[Resolved] SELECT query from multiple tables

[Mr_Mako]

Hi.

I've searched the forums extensively for an answer... and I've tried the examples listed in every one of them... and had no luck at all.

I have two tables I need to extract data from. One is a "plane" table, where individual aircraft are stored, and one is a "user" table... where the users of the site are stored. Each user "owns" an aircraft, and they are assigned by adding the user_id field from the "user" table to the "plane" table. They look like this:

USER

user_id (auto_increment)
name
location
email

PLANE

plane_id (auto_increment)
user_id
name
type
status

So... in essence, what I have is something like this:

USER
====
1		2		3		4
Bob		Mike		Sally		John
USA		UK		USA		USA
[email][e-mail][/email]	[email][e-mail][/email]	[email][e-mail][/email]	[email][e-mail][/email]

PLANE
=====
1		2		3		4
2		4		1		3
Mike's Plane	John's Plane	Bob's Plane	Sally's Plane
B-17G		B-17F		B-17G		B-17F
ok		ok		ok		shot down

Now... I have a page, where I want to display the aircraft (as the basis), with the type and user details... like below:

$display_block .= "<font size-2 color=#00000><b>$plane_link</b> :<font size=1>  ($plane_type) - $user_name ($user_email)</font></font><br>";

I am having a HELL of a time extracing the information out of the two tables to populate the above PHP block!!

I can't seem to get the data from one or the other table depending on my code. Here's my latest attempt:

// Create Aircraft Query //
$plane_query = "SELECT * FROM $user, $plane WHERE plane.user_id = 'user.user_id' AND plane.squadron = '358th' AND plane.status = '0' ORDER BY plane.plane_id ASC";
$plane_result = @mysql_query($plane_query, $connect) or die(mysql_error());

while ($row = mysql_fetch_array($plane_result)) {
	$plane_name_long = $row['plane.name'];
	$plane_name_fix = str_replace(" ", "%20", $plane_name_long);
	$plane_link = "<a href=aircraft.php?name=$plane_name_fix><font color=#30341B><b>$plane_name_long</b></font></a>";
	$plane_type = $row['plane.type'];
	$plane_user_id = $row['plane.user_id'];
	$user_name = $row['user.name'];
	$user_email = $row['user.email'];
}

Anyone have any ideas what I'm doing wrong? I can't figure it out... and I don't want to have a whole bunch of queries to dig up the information... that's just sloppy, and I don't want to get into the habit of writing sloppy code.

Thanks...

[mrhappiness]

WHERE plane.user_id = 'user.user_id'

should be WHERE plane.user_id = user.user_id

[Mr_Mako]

If I do that, then I get an output that just shows the parenthesis and colon in the $display_block and nothing else.

Error checking reveals that not one of my $row variables has anything in it.

[Mr_Mako]

Anyone have any other possible suggestions? 🙁

[LordShryku]

You're referencing the tables as variables...

$plane_query = "SELECT * FROM $user, $plane WHERE plane.user_id = 'user.user_id' AND plane.squadron = '358th' AND plane.status = '0' ORDER BY plane.plane_id ASC";

Doesn't seem like they need to be variables. Are they set? Considering you want to keep variables, you should alias them. Beyond that, your query looks for plane.squadron, which according to your description of the tables, doesn't exist. Assuming it's there, and there's data to match this query, I'd try this:

$user  = "user";
$plane = "plane";

$plane_query  = "SELECT * 
                   FROM ".$user." u, ".$plane." p
                  WHERE p.user_id = u.user_id
                    AND p.squadron = '358th' 
                    AND p.status = '0' 
               ORDER BY p.plane_id ASC";

$plane_result = mysql_query($plane_query) or die(mysql_error());
if(mysql_num_rows($plane_result) > 0) {
   while($row = mysql_fetch_array($plane_result)) {
      print_r($row);
   }
}
else {
   echo "No rows returned";
}

[Cognizant]

$plane_query = "SELECT * FROM $user, $plane WHERE plane.user_id = 'user.user_id' AND plane.squadron = '358th' AND plane.status = '0' ORDER BY plane.plane_id ASC";

Are $user and $plane supposed to be variables, or are those the actual table names? If they are variables, maybe try:

$plane_query = "SELECT * FROM $user as u, $plane as p WHERE p.user_id = u.user_id AND p.squadron = '358th' AND p.status='0' ORDER BY p.plane_id ASC";

If the AREN'T variable, then take out the $:

$plane_query = "SELECT * FROM user, plane WHERE plane.user_id = user.user_id AND plane.squadron = '358th' AND plane.status='0' ORDER BY plane.plane_id ASC";

[Mr_Mako]

Hey Guys...

I finaly got it to work. One of my problems was that I had "$row" misspelled as "$rpw" somehow on one of my lines... hehe.

It worked when I used the following syntax:

// Create Aircraft Query //
$plane_query = "SELECT * FROM user as u, plane as p WHERE p.user_id = u.user_id AND p.squadron = '358th' AND p.status = '0' GROUP BY p.plane_id ASC"; 
$plane_result = @mysql_query($plane_query, $connect) or die(mysql_error()); 

while ($row = mysql_fetch_array($plane_result)) {
	$plane_name_long = $row['name'];
	$plane_name_fix = str_replace(" ", "%20", $plane_name_long);
	$plane_link = "<a href=aircraft.php?name=$plane_name_fix><font color=#30341B><b>$plane_name_long</b></font></a>";
	$plane_type = $row['type'];
	$plane_user_id = $row['user_id'];
	$user_name = $row['username'];
	$user_location = $row['location'];

$display_358th_block .= "
	<font face=\"Century Gothic\" color=#000000 size=2>$plane_link<b> :</b><font size=1 color=#000000><font color=red><b> ($plane_type)</b></font> - $user_name ($user_location)</font></font><br>";

}

I really appreciate the help... this helped me a lot! 🙂