Help creating a query

[madk]

Hi guys,

I need help with this query that is once again boggling my mind.

The following query works but I need to combine the two queries so that I can sort the data by username.

$query = mysql_query("SELECT * FROM ds_friends WHERE user_id = '$user_id' AND active = '1'") or die(mysql_error());
while($list = mysql_fetch_array($query))
{
$friend_query = mysql_query("SELECT username, user_avatar, user_id FROM phpbb_users WHERE user_id = '" . $list['friend_id'] . "'") or die(mysql_error());
$friend = mysql_fetch_array($friend_query);
}

Please help me modify this to work. Thanks in advance.

[phranque]

SELECT ds_friends.user_id, active, username, user_avatar, ... FROM ds_friends, phpbb_users WHERE ds_friends.user_id=phpbb_users.user_id AND active = '1' ORDER BY user_id

[madk]

This doesn't seem to be what I'm looking for.

A. I need to pull the 'friend_id' from the first query to ds_friends and then use the 'friend_id to query the phpbb_users table to get more info on this user.

B. I need to be able to order by the 'username' in phpbb_users.

C. I'm thinking about something like this but haven't tested yet:

SELECT username
FROM phpbb_users
WHERE user_id
IN (SELECT friend_id FROM ds_friends WHERE user_id = '$user_id' AND active = '1')

EDIT: Just tested and returns a syntax error.

[madk]

Fixed using the sub-query.

Now I hear that using joins is more efficient. Is this true? If so how would I rewrite this query to use them?

[ZydoSEO]

Try something like:

SELECT pu.username, pu.user_avatar, pu.user_id
FROM ds_friends df
JOIN phpbb_users pu
ON pu.user_id = df.user_id
WHERE df.active = '1'
ORDER BY pu.username

This should pull back all active users from ds_friends and their corresponding data from phpbb_users.

DISCLAIMER: Not a PhP/MySQL guy... more an ASP/SQL*SERVER guy... but it should give you an idea of where to start.

You may also have to alias the columns being returned using AS aliasname, not sure about MySQL quirks when using ORDER BY or GROUP BY.

NOTE: If there is a row in ds_friends for a user_id that does NOT have a corresponding row in phpbb_users then no row will be returned. This type of join (INNER) only returns rows where the user_id exists in both tables.

If you'd like to get back rows that also include user_ids that exist in ds_friends but are missing a row in phpbb_users then you'd want to use a LEFT OUTER join something similar to (but not necessarily the exact syntax :))

SELECT df.user_id AS dfuser, pu.username, pu.user_avatar, pu.user_id AS puuser
FROM ds_friends df
LEFT JOIN phpbb_users pu
ON pu.user_id = df.user_id
WHERE df.active = '1'
ORDER BY pu.username