[SOLVED] Multiple MySQL Select

[hoopplaya4]

Hi All,

 

I've got a MySQL table:

 

tblpm: 
id 	
message_id 	
receiver_id 
title 	
content 	
date_sent 
sender_id

 

Now, what I am currently doing is retrieving all the data from this table based on the following criteria:

 

<?php
require("../connection.php");
$rs = mysql_db_query($DBname,$sql,$link); 
$data = mysql_query("SELECT * FROM tblpm WHERE receiver_id ='$usrID' ORDER BY id DESC") or die(mysql_error());

while($row= mysql_fetch_array( $data ))
{
echo "The id is:";
echo $row['id'];

echo "The receiverID is:";
echo $row['receiver_id'];

echo "The Sender ID is:";
echo $row['sender_id'];   // <--- This is what I would like to use to grab the sender's First and Last name from another table called "tblusers."

}

 

How could I use the "sender_id" field to match it with the "usrID" field in another table (called tblusers), so that I can retrieve the First and Last name of the sender and place it in the "while loop"?

 

Hope this makes sense.

[akitchin]

this is called a JOIN, and is a very valuable construct to learn. here you'll want a LEFT OUTER JOIN to append the first name and last name to the right side of the table you're querying:

 

SELECT tblpm.*, tblusers.First AS first_name, tblusers.Last AS last_name FROM tblpm LEFT OUTER JOIN tblusers ON tblpm.sender_id=tblusers.usrID WHERE receiver_id ='$usrID' ORDER BY id DESC

 

i'll be honest and say i'm not sure that tblpm.* will work, but if it doesn't, you'll just have to list the fields explicitly.

 

PS: google a few tutorials on JOINs and you'll most likely be able to come up with a more suitable query than this. wikipedia has a pretty decent entry on them.

[hoopplaya4]

Thanks akitchin, that certainly worked. 

 

I knew I had to use a JOIN, but I need someone to explain it to me for me to understand how to use them.

 

I'll check out the Wikipedia entries.

 

Thanks again.