View Full Version : SELECT data from multiple tables

05-20-2011, 07:59 AM
In a php page script (friends.php) I need to query two tables. One is "friends" table and the other is "members" table.

In a first query, I use:

$sql = mysql_query("SELECT * FROM friends WHERE (requesting_id=$id OR requested_id=$id) AND connect_type='1'");

while($row = mysql_fetch_array($sql)) {
$friend_id1 = $row[requesting_id];
$friend_id2 = $row[requested_id];
if($friend_id1 != $id){
$true_friend = $row[requesting_id];
} else {
$true_friend = $row[requested_id];

To select all rows where two members are friends, which stores them into a variable $true_friend.

Now, what I want to do, is grab the $true_friend's (id, firstname, and lastname) from my "members" table, so that I can display a list of the member's friends, using:

$outputList .= '
<table width="100%">
<td width="13%" rowspan="2"><div style=" height:50px; overflow:hidden;"><a href="profile.php?id=' . $id . '" target="_self">' . $user_pic . '</a></div></td>
<td width="14%" class="style7"><div align="right">Name:</div></td>
<td width="73%"><a href="profile.php?id=' . $id . '" target="_self">' . $firstname . ' ' . $lastname . '</a> </td>
<hr />';

So How would I set up the query for the member table, for each value of $true_friend...?

05-20-2011, 09:14 PM
Check: http://www.w3schools.com/sql/sql_join.asp

Will explain it all, click the 'Next' bit at the bottom to read the other bits because there are different types of joins. I think this is what you're trying to achieve.

05-21-2011, 06:28 PM
Forget what I said here

05-22-2011, 05:06 PM
I have two tables:

'members' with columns id, firstname, lastname
'friends' with columns requesting_id, requested_id, connect_type

The main problem I am having is that my $id (of the current logged in member) can either be in the requesting_id column or requested_id column of the 'friends' table.

Now, once I located all instances of this $id from the 'friends' table, I want to select the "other" id (either requested_id or requesting_id, which would be the friend of the logged-in member) and get their id, firstname, lastname from the 'members' table. So I can echo them out as a list of members who are friends with the current logged in member.

I am just having trouble coming up with a JOIN query that would. I really appreciate your help.