PHP/MySQL db confusion

  • (2 Pages)
  • +
  • 1
  • 2

19 Replies - 1070 Views - Last Post: 15 April 2010 - 09:32 AM Rate Topic: -----

#1 NubileDIYer  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 52
  • Joined: 11-August 09

PHP/MySQL db confusion

Posted 08 April 2010 - 01:51 PM

Hi all,
I am working on my first real PHP project, and have solved a remarkable amount of problems thanks to previous posts and tutorials. Thanks!

However, there is one question I am having trouble finding a solution to, which is keeping me from continuing. I have one db with two tables: users (title, fname, lname, email, username, password) and vehicles (username, year, make, model, status) and the general idea of the site is to display a member's vehicle data upon log-in. The index.php pages has a Log-in form which POSTS to login.php. From here, upon verification, there is a link to the user's member.php page which will display the data in a table.. at least, that's what I need it to do.

So far, I've got the log-in system down. With Sessions, I have even figured out how to access $username's row (in users table) and build echo 'Welcome, $title $lname'; upon log-in. But for the life of me, I just can't find an article or tutorial on how to access the vehicles table and build a query to find all of $username's (from vehicles table this time) vehicles. And, once I figure that out, I don't know how to build a table that will have JUST the right number of rows to host that data. I'm guessing I need to count the rows and build a table with n+1(for the header), but, again, I don't know where to begin.

Here's my stab at it:
<?php
		require_once("connect.php");
		$dataquery = mysql_query ("SELECT * FROM vehicles WHERE username = '$username'");
			while ($datarow = mysql_fetch_array($dataquery))
			{
			print_r($datarow);
?>
			


Now my $_SESSION['username'] refers to the users table username field, so am I correct in guessing that is what's throwing it off?

I am sure these are relatively basic questions, so I thank you for your patience with me.
~n00b

Is This A Good Question/Topic? 0
  • +

Replies To: PHP/MySQL db confusion

#2 jrm402  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 51
  • View blog
  • Posts: 333
  • Joined: 18-March 10

Re: PHP/MySQL db confusion

Posted 08 April 2010 - 02:40 PM

Well your are very close and on the right track. Did you assign $username = $_SESSION['username'];? (I'm assuming you did).

Once you get the queries correct the output is simple, something like this.
<table>
<?php

$dataquery = ....;
while($datarow = mysql_fetch_array($dataquery)) {
echo "<tr><td>" . $datarow['year'] . "</td><td>" . $datarow['make'] . "</td> etc.... </tr>";
}

?>
</table>



You don't need any kind of count to populate the table because the while will do it all for you.

Let us know how it goes from here! :)
Was This Post Helpful? 1
  • +
  • -

#3 NubileDIYer  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 52
  • Joined: 11-August 09

Re: PHP/MySQL db confusion

Posted 08 April 2010 - 04:13 PM

jrm402, thanks for the simple (and quick!) answer! I was trying to over-complicate it, as I tend to do when I'm new at something.

However, I believe either my query is wrong, or it's looking in the wrong table still, because only the header was displayed:
<?php
session_start();
//The POST comes from the log-in box, and I think is referring to the users table.
$postusername = $_POST['username'];
?>


Some HTML stuff, then:
            
<table width="800" border="1" cellspacing="0" cellpadding="0">
  <tr>
    <th scope="col">VIN No.</th>
    <th scope="col">Year</th>
    <th scope="col">Make</th>
    <th scope="col">Model</th>
    <th scope="col">Color</th>
    <th scope="col">Title</th>
    <th scope="col">Item No.</th>
    <th scope="col">Status</th>
    <th scope="col">Photo</th>
    <th scope="col">Dock Receipt</th>
  </tr>
<?php
	require_once("connect.php");
	$dataquery = mysql_query ("SELECT * FROM vehicles WHERE username = '$postusername'");
		while($datarow = mysql_fetch_array($dataquery)) 
		{
		echo "<tr><td>" . $datarow['VIN'] . "</td><td>" . $datarow['year'] . "</td><td>" . $datarow['make'] . "</td><td>" . $datarow['model'] . "</td><td>" . $datarow['color'] . "</td><td>" . $datarow['title'] . "</td><td>" . $datarow['item'] . "</td><td>" . $datarow['status'] . "</td><td>" . $datarow['photo'] . "</td><td>" . $datarow['dockrec'] . "</td></tr>";
		}

?>
</table>



And yes, my Session was opened on the login.php page (once confirmed usernames and pws match db):
//Verify Username matches Password
if ($postusername==$dbusername&&$postpassword==$dbpassword)
	{
//Open Session. This DOES get carried over as long as I have session_start(); at the top, right?
	$_SESSION['username'] = $dbusername ;
}


I only just changed posted usernames from $username to $postusername, to help keep track vs $dbusername, but I made sure all were updated and everything is back to function (besides, of course, the table).
Was This Post Helpful? 0
  • +
  • -

#4 JackOfAllTrades  Icon User is online

  • Saucy!
  • member icon

Reputation: 5951
  • View blog
  • Posts: 23,210
  • Joined: 23-August 08

Re: PHP/MySQL db confusion

Posted 08 April 2010 - 04:19 PM

1. Create query as a variable so you can print it if it doesn't work
2. Use variable in query
3. Check return value of query...if it's FALSE...THE QUERY FAILED! So print the variable you passed as the query to make sure it contains what you expect and call mysql_error() to find out why MySQL is complaining.

People just can't seem to grasp PHP and MySQL error handling and troubleshooting. It's not rocket surgery.
Was This Post Helpful? 1
  • +
  • -

#5 NubileDIYer  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 52
  • Joined: 11-August 09

Re: PHP/MySQL db confusion

Posted 09 April 2010 - 07:07 AM

While I do appreciate your willingness to help, let me reiterate that I am NEW at PHP, and programming in general. New as in, 4 months ago, I had never opened a <?php tag. Does this condemn me to a lifetime of ignorance? I'd say I am coming along pretty well, being almost completely self-taught. So my troubleshooting skills need some work, I get it, that's why you are the mayor of simpleton and I am simply one of your subjects. Do not mistake my lack of experience for laziness.

I have deduced (by replacing $postusername with an actual username) that my query is correct and the variable is incorrect. If I may ask another simple/stupid question, how do I tell my variable $postusername (which the user entered into the login box, verified in the users table) to link to the username in the vehicles table?
<?php
require_once("connect.php");
//This query will work if I exchange an actual username for $postusername
	$dataquery = mysql_query ("SELECT * FROM vehicles WHERE username = '$postusername'");

while($datarow = mysql_fetch_array($dataquery)) 
	{
	echo "<tr><td>" . $datarow['VIN'] . "</td><td>" . $datarow['year'] . "</td><td>" . $datarow['make'] . "</td><td>" . $datarow['model'] . "</td><td>" . $datarow['color'] . "</td><td>" . $datarow['title'] . "</td><td>" . $datarow['item'] . "</td><td>" . $datarow['status'] . "</td><td>" . $datarow['photo'] . "</td><td>" . $datarow['dockrec'] . "</td></tr>";
	}

?>



Again, thank you both for your help and advice.
Was This Post Helpful? 0
  • +
  • -

#6 Jstall  Icon User is offline

  • Lurker
  • member icon

Reputation: 434
  • View blog
  • Posts: 1,042
  • Joined: 08-March 09

Re: PHP/MySQL db confusion

Posted 09 April 2010 - 07:52 AM

View PostNubileDIYer, on 09 April 2010 - 06:07 AM, said:

While I do appreciate your willingness to help, let me reiterate that I am NEW at PHP, and programming in general. New as in, 4 months ago, I had never opened a <?php tag. Does this condemn me to a lifetime of ignorance? I'd say I am coming along pretty well, being almost completely self-taught. So my troubleshooting skills need some work, I get it, that's why you are the mayor of simpleton and I am simply one of your subjects. Do not mistake my lack of experience for laziness.

I have deduced (by replacing $postusername with an actual username) that my query is correct and the variable is incorrect. If I may ask another simple/stupid question, how do I tell my variable $postusername (which the user entered into the login box, verified in the users table) to link to the username in the vehicles table?
<?php
require_once("connect.php");
//This query will work if I exchange an actual username for $postusername
	$dataquery = mysql_query ("SELECT * FROM vehicles WHERE username = '$postusername'");

while($datarow = mysql_fetch_array($dataquery)) 
	{
	echo "<tr><td>" . $datarow['VIN'] . "</td><td>" . $datarow['year'] . "</td><td>" . $datarow['make'] . "</td><td>" . $datarow['model'] . "</td><td>" . $datarow['color'] . "</td><td>" . $datarow['title'] . "</td><td>" . $datarow['item'] . "</td><td>" . $datarow['status'] . "</td><td>" . $datarow['photo'] . "</td><td>" . $datarow['dockrec'] . "</td></tr>";
	}

?>



Again, thank you both for your help and advice.



Hey there. From what I can see it should be working, did you try just echoing out $postusername to see what the value of it is?
Was This Post Helpful? 1
  • +
  • -

#7 JackOfAllTrades  Icon User is online

  • Saucy!
  • member icon

Reputation: 5951
  • View blog
  • Posts: 23,210
  • Joined: 23-August 08

Re: PHP/MySQL db confusion

Posted 09 April 2010 - 08:09 AM

I gave you instructions on how to troubleshoot, that was my goal. And hopefully you learned something, although your most recent code does not seem to indicate you made the changes I indicated :(

Without seeing your html form, it's hard to help you as to the value of your data coming from the user.
Was This Post Helpful? 1
  • +
  • -

#8 rmccarter721  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 8
  • View blog
  • Posts: 139
  • Joined: 10-September 07

Re: PHP/MySQL db confusion

Posted 09 April 2010 - 08:39 AM

Jack, I think the problem is:

Someone who has been coding a while would understand what your post meant and could do that. Though this poster has only been coding a matter of months (4), he doesn't understand what you mean, I know that at that stage i wouldn't of had a clue what you meant.
Was This Post Helpful? 1
  • +
  • -

#9 JackOfAllTrades  Icon User is online

  • Saucy!
  • member icon

Reputation: 5951
  • View blog
  • Posts: 23,210
  • Joined: 23-August 08

Re: PHP/MySQL db confusion

Posted 09 April 2010 - 08:59 AM

// Assuming username comes from a form field called "username" via a POST
// Sanitize data
$username = mysql_real_escape_string($_POST['username']);
$myquery = "SELECT * FROM vehicles WHERE username='{$username}'";
$result = mysql_query($myquery);
if (!$result)
{
    // This should be done through proper error logging so you do not expose
    // any important SQL information
    echo ("Query ({$myquery}) failed; error: " . mysql_error());
    exit();
}

while ($row = mysql_fetch_array($result))
{
    // Handle each row as you see fit
}

// Clean up
mysql_free_result($result);


Was This Post Helpful? 1
  • +
  • -

#10 jrm402  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 51
  • View blog
  • Posts: 333
  • Joined: 18-March 10

Re: PHP/MySQL db confusion

Posted 09 April 2010 - 10:23 AM

That's what I was trying to ask too if you had stored the $username variable. I didn't see it in your code anywhere except for when you use it. So if you didn't store it, like JackOfAllTrades did above $username = mysql_real_escape_string($_POST['username']);, then your query would look like this:
"SELECT * FROM vehicles WHERE username = ''";


because it is an empty string.
Was This Post Helpful? 1
  • +
  • -

#11 NubileDIYer  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 52
  • Joined: 11-August 09

Re: PHP/MySQL db confusion

Posted 14 April 2010 - 01:12 PM

I spent the weekend reading the best intro to PHP/MySQL I could find : PHP 101 for the Absolute Beginner and it really helped fill in some of the many gaps in my knowledge. I am (hopefully) ONE bug away from a functional app here. BIG thanks to jrm402 on how to build my user data table. That leaves the issue of displaying member-specific data. I have a working query to display data if I put in an ID, but not a variable to represent that ID. I have the following queries on member.php, one which will store all users table data in $user_result and a second to store all vehicles table data in the $car_result variable: (I subbed out the * for every fieldname in my query to make sure my queries were working, so no hating :rolleyes2: )
//note it works when I enter '1' in, but I can't figure out how to enter variable $userid into the query to display the value of 1, which is the id for my username
$user_query = "SELECT id, title, fname, lname, user FROM users WHERE id = '1' ";
$user_result = mysql_query($user_query) or die('Query Error:'. mysql_error());

//A while loop to assign results to array
while($row = mysql_fetch_assoc($user_result))
{
echo "<h3>Welcome to your Member Page, {$row['title']} {$row['lname']}. Here is your Vehicle Data:</h3>" ;
?>
<table>
<?php

//And the vehicles query: ({$row['id']} works here because it was assigned in the above loop):
$car_query = "SELECT year, make, model, status, VIN, color, title, item, photo, dockreceipt FROM vehicles WHERE id = {$row['id']}";
$car_result = mysql_query($car_query) or die('Query Error:'. mysql_error());
//As messy as this is, it works like a charm when I hard-code a value for id into the users query:
while($datarow = mysql_fetch_array($car_result)) 
{
echo "<tr><td>" . $datarow['VIN'] . "</td><td>" . $datarow['year'] . "</td><td>" . $datarow['make'] . "</td><td>" . $datarow['model'] . "</td><td>" . $datarow['color'] . "</td><td>" . $datarow['title'] . "</td><td>" . $datarow['item'] . "</td><td>" . $datarow['status'] . "</td><td>" . $datarow['photo'] . "</td><td>" . $datarow['dockrec'] . "</td></tr>";
// Close Vehicle While Loop
}	
// Close User While Loop
}
?>
</table>


I tried {$row['id']}, $userid (which I previously defined as $_SESSION['id'] and confirmed it echoes), swapping quotes in query, everything. No dice. But then, I'm still not absolutely solid on sessions and queries. I think I have attached all relevant code, let me know if you need to see more! Any suggestions and advice are greatly appreciated!
Was This Post Helpful? 0
  • +
  • -

#12 JackOfAllTrades  Icon User is online

  • Saucy!
  • member icon

Reputation: 5951
  • View blog
  • Posts: 23,210
  • Joined: 23-August 08

Re: PHP/MySQL db confusion

Posted 14 April 2010 - 01:51 PM

What is the value of $car_query before you pass it into the SQL query? What is the schema for your vehicle table?
Was This Post Helpful? 1
  • +
  • -

#13 NubileDIYer  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 52
  • Joined: 11-August 09

Re: PHP/MySQL db confusion

Posted 14 April 2010 - 04:08 PM

I'm not sure if I understand correctly. Aren't I defining the $car_query variable on this line:
$car_query = "SELECT year, make, model, status, VIN, color, title, item, photo, dockreceipt FROM vehicles WHERE id = {$row['id']}";

And then running the query and setting the result to $car_result here?:
$car_result = mysql_query($car_query) or die('Query Error:'. mysql_error());


See the attached pic to see how the vehicles table is set up. Perhaps id being set to int is affecting it?

I think my more fundamental question is 'What is the best way to take a unique id from the users table, set it to a variable so it can be used globally (if I used that term correctly.. on another page), and then build a query to access and select all of one id's (ie all vehicles where id = [user's id]) vehicles from the vehicles table?' You can titter at me all you'd like as long as it leads to :bananaman: or :punk:

Attached image(s)

  • Attached Image

Was This Post Helpful? 0
  • +
  • -

#14 JackOfAllTrades  Icon User is online

  • Saucy!
  • member icon

Reputation: 5951
  • View blog
  • Posts: 23,210
  • Joined: 23-August 08

Re: PHP/MySQL db confusion

Posted 14 April 2010 - 05:27 PM

See, now you're missing something...the relation to the user.

There should be a foreign key (user_id, as a suggestion) in the vehicles table that maps to the user's id from the user table.
Was This Post Helpful? 1
  • +
  • -

#15 JackOfAllTrades  Icon User is online

  • Saucy!
  • member icon

Reputation: 5951
  • View blog
  • Posts: 23,210
  • Joined: 23-August 08

Re: PHP/MySQL db confusion

Posted 15 April 2010 - 05:15 AM

I had to run when I posted that, but this is an example which assumes the use of InnoDB (for foreign key constraints)
users
-----
id int not null auto_increment
first_name varchar(40)
last_name varchar(80)

vehicles
--------
id int not null auto_increment
user_id int not null 
year unsigned smallint
make varchar(25)
model varchar(75)
foreign key (user_id) references users(id) on delete cascade


Adding a user:
insert into users (first_name, last_name) values ('Joe', 'Blow');


Adding a car for a user:
insert into vehicles(user_id, year, make, model) values (userID, 1966, 'Dodge', 'Charger');


Getting a user and its information:
select * from users u inner join vehicles v on u.id = v.user_id where u.id = userID;


Deleting a user and all its information:
delete from user where id=userID

Was This Post Helpful? 1
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2