[RESOLVED] SELECT data from multiple tables

juniper747

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%">
                <tr>
                <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>
                </tr>
		</table>
	        <hr />';

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

NogDog

Sounds like something you can do in a single query using a JOIN on the two tables.

juniper747

Thanks! But could you explain a little more... So I am trying to use JOIN and I only have two tables (below) with the columns in ():

members: (id, firstname, lastname)
friends: (requesting_id, requested_id)

Then I have the session variable: $id

I tried:

$sql2 = mysql_query("SELECT members.id, members.firstname, members.lastname FROM members JOIN friends
    ON members.id = friends.requesting_id JOIN friends ON members.id = friends.requested_id
 WHERE $id IN(requesting_id,requested_id) AND connect_type=1 ORDER BY id ASC $limit");

But I'm getting and error when I try to build my outputlist here:

// Build the Output Section Here
$outputList = '';
while($row2 = mysql_fetch_array($sql2)) { 

$id = $row2["id"];
$firstname = $row2["firstname"];
$lastname = $row2["lastname"];
}

Any ideas?

Here's the error:
Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in...

NogDog

That error indicates the query failed to be processed by MySQL, so mysql_query() returned false. I think some (all?) of the problem may have been confusion by the MySQL parser with two joins on the same table, which may be fixed by assigning aliases via AS, along with a couple other tweaks:

<?php
$query = "
SELECT
   members.id,
   members.firstname,
   members.lastname
FROM members
   JOIN friends AS requesting ON members.id = friends.requesting_id
   JOIN friends AS requested ON members.id = friends.requested_id
WHERE
   $id IN(requesting.requesting_id, requested.requested_id) AND
   members.connect_type=1
ORDER BY id ASC $limit
";
$sql2 = mysql_query($query);
if($sql2 == false) {
   // error will be displayed or logged depending on your display_errors setting
   user_error(mysql_error() . " -- $query");
   echo "<p class='error'>Sorry, there was a DB error.</p>";
}
else {
   // process the query results
}

juniper747

Forget what I said before.... to clarify things,

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.
Juniper747 is online now Add to Juniper747's Reputation Report Post Edit/Delete Message

NogDog

Maybe use a UNION?

$query = "
(SELECT members.id, members.firstname, members.lastname
FROM members
JOIN friends ON members.id = friends.requesting_id
WHERE friends.requested_id = $id)
UNION
(SELECT members.id, members.firstname, members.lastname
FROM members
JOIN frines on members.id = friends.requested_id
WHERE friends.requesting_id = $id)
ORDER BY id ASC LIMIT $limit
";

juniper747

Thank you very much NogDog! Worked brilliantly!