Linking two tables

[Ben Wilkins]

Ok, hey I have:

Users table:

UserID, Rank, and other user details

Platoon_members:

UserID, and other details.




I have this statement:

$query = "SELECT * FROM `platoon_members` WHERE `platoon_id` = '{$row[id]}' ORDER BY `rank` ASC ;";

But I need to ORDER BY the rank feild in the users table.
There is no Rank Field in the platoon_members table.

if you dont understand me, ill explain in more detail

[SJH]

How's this?

SELECT *
  FROM platoon_members m
INNER
  JOIN users u
    ON u.UserID = m.UserID
 WHERE m.platoon_id = {$row['id']}
ORDER
    BY u.rank ASC;

[Ben Wilkins]

Just to check, is this right

 $query = "SELECT * FROM `platoon_members` m INNER JOIN 'users' u ON u.UserID = m.UserID WHERE `m.platoon_id` = '{$row[id]}' ORDER BY `u.rank` ASC ";

[Ben Wilkins]

Anyone?

[r937]

no, that's not right, ben, you messed it up

try SJH's query without adding any punctuation marks to it or flattening it out into one long unreadable line

[Ben Wilkins]

OK tried that:

			$query = 
"
SELECT * FROM platoon_members m
INNER
JOIN users u
ON u.UserID = m.UserID
WHERE m.platoon_id = {$row['id']}
ORDER
BY u.rank ASC";

And it produces:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/benanddale/domains/nsgclan.com/public_html/includes/Regiment.php on line 62

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/benanddale/domains/nsgclan.com/public_html/includes/Regiment.php on line 66

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/benanddale/domains/nsgclan.com/public_html/includes/Regiment.php on line 62

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/benanddale/domains/nsgclan.com/public_html/includes/Regiment.php on line 66

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/benanddale/domains/nsgclan.com/public_html/includes/Regiment.php on line 62

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/benanddale/domains/nsgclan.com/public_html/includes/Regiment.php on line 66

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/benanddale/domains/nsgclan.com/public_html/includes/Regiment.php on line 62

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/benanddale/domains/nsgclan.com/public_html/includes/Regiment.php on line 66

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/benanddale/domains/nsgclan.com/public_html/includes/Regiment.php on line 62

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/benanddale/domains/nsgclan.com/public_html/includes/Regiment.php on line 66

[Ben Wilkins]

Oh, I also tried it without the " " and it gives me

Parse error: syntax error, unexpected T_STRING in /home/benanddale/domains/nsgclan.com/public_html/includes/Regiment.php on line 49

[crmalibu]

step 1 - develop a working query using something like phpmyadmin, mysql workbench, heidisql, or another similar tool. Don't stop until you have a query that produces the desired result set.

step 2 - using the working sql query you created, create a string in php, substituting any variables needed. echo the text to the screen.

step 3 - make sure it matches the working sql query you created

step 4 - now you can actually query the database from within php.

[Ben Wilkins]

Im a nub at mysql my mate done the coding, but hes away for a while. Can I not just edit the code somehow to make this work?

[Ben Wilkins]

Anyone?? Please..

[r937]

did you test the query outside of php?

[Ben Wilkins]

Erm.. I dunno.


Is this code correct though?:

$query =
SELECT * FROM platoon_members m
INNER
JOIN users u
ON u.UserID = m.UserID
WHERE m.platoon_id = {$row['id']}
ORDER
BY u.rank ASC;

[r937]

except for the php parts, it looks okay

the sql statement that you want to test starts with SELECT and has an actual id number instead of {$row['id']}

what are you going to test it in? phpmyadmin?

[Ben Wilkins]

Yeah.

[Ben Wilkins]

I finally figured out the problem.

The userid field was called uid in platoon_members not UserID

[r937]