我想根据另一个表中的内容从表中返回数据？

Question

I have a table passenger_table and a table users!

passenger_table

user_id  j_id
1          1
34         1
54         1

users

user_id  Firstname  Lastname
1         Patrick    Connell    
34         John       Murphy
54         Connell    Jones

I know this might be straight forward enough but I just want to loop out all the users firstnames and lastnames where journey id is = 1.

So the output would be the 3 users mentioned above first and lastnames.

This is what I got so far.....

//Get passenger id
$id = $_GET['id'];
$resultp = mysql_query("SELECT * FROM passenger_journey WHERE j_id=$id")
or die(mysql_error()); 
$rowp = mysql_fetch_array($resultp);
$passenger_id = $rowp['user_id'];


//Get Passenger Details
$resultpd = mysql_query("SELECT * FROM users WHERE user_id=$passenger_id")
or die(mysql_error()); 


while($rowpd = mysql_fetch_array($resultpd)) {
    echo '<a href="profile.php?id=' . $rowpd['user_id'] . '">' . $rowpd['firstname'] . ' ' . $rowpd['lastname'] . '</a><br/>';
}

At the moment I can only retrieve one record.

Answer 1

You should join the table, on the associative ID. Read more about table joins here: http://dev.mysql.com/doc/refman/5.0/en/join.html

SELECT u.user_id, u.firstname, u.lastname, p.j_id 
FROM users u 
LEFT JOIN passenger_table p ON p.user_id=u.user_id 
WHERE p.j_id=1;