PHP mySQL retrieval issue

I am having a problem retrieving some data from tables in my database.

I have user profiles on my site and you can add other users to your friends list, The friends table has 2 collumns, the user_id of the user who added the person and the 'friend_id' the user_id of the person added.

There is also a user table with the detail开发者_开发技巧s of each other. 'user_id' is contained in both of these tables, this is how the tables are linked together.

I want to be able to retrieve all of the friends one user has.

The problem is that my query just returns the main users name instead of the friends of that user

The current query is

SELECT * FROM users,  user_followers 
  WHERE users.user_id = '$userset' AND user_followers.friend_id = users.user_id`

$userset contains the user_id of the main user logged in

Any help would be greatly appreciated.

You just have a small error in your WHERE clause. Change users.user_id = '$userset' to user_followers.user_id = '$userset'. Here is the query in full (also rewritten to use the newer ANSI-92 join syntax):

SELECT *
FROM user_followers
JOIN users
ON user_followers.friend_id = users.user_id
WHERE user_followers.user_id = '$userset'

This fixes your initial question:

SELECT user.*, friend.* FROM users user 
INNER JOIN user_followers uf on user.user_id = uf.user_id
INNER JOIN users friend on uf.friend_id = friend.user_id
WHERE user.user_id = '$userset';

However, that's going to return a whole lot of unnecessary data (user.* for each row). It would be better to grab the user info first with a simple:

SELECT * FROM users WHERE user_id = '$userset';

Then grab their friends:

SELECT friend.* FROM user_followers uf INNER JOIN users friend on uf.friend_id = friend.user_id
WHERE uf.user_id = '$userset';