Linking two tables

Databases
1

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 :slight_smile:

2

How’s this? :slight_smile:


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;
3

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 ";
4

Anyone?

5

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

:slight_smile:

6

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

7

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

8

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.

9

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

10

Anyone?? Please…

11

did you test the query outside of php?

12

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;

13

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?

14

Yeah.

15

I finally figured out the problem.

The userid field was called uid in platoon_members not UserID

16

:slight_smile: