Help with a friends script

[Jewbilee]

Im having a little trouble with this.  I need a way to keep track of friends of users similar to myspace.  One user can add several others to a friends list and be viewed and removed and what not.  The only problem Im having is how to do I store this in a database?  Should I make a seperate table or store the id's of friends in a users table?

[jsladek]

make a seperate table 2 feilds user & friend.  use the user_id feild of your user table. 
To find friends of a particular user
[b]SELECT * FROM tbl_friend WHERE user = '$user_id';[/b].
That will give you a list of the users friends. 
You could find the Name or whatever from your user table by doing something like this:
[b]SELECT first_name, last_name FROM users WHERE user_id IN (SELECT * FROM tbl_friend WHERE user = '$user_id')[/b]

My syntax might be a little off but it's in the ballpark.

-John

[corbin]

You could try a table like

user_id, friend_id

And then lets say theyre 5 users, corbin, bob, john, frank, and joe.  They have the ids 1 2 3 4 5.

Lets say corbin has bob and joe as friends and bob has joe and frank

the db would look like

1,2
1,5
2,5
2,4

Then assuming you are pulling usernames from the table accounts and this from friends you could do something like the following to pull corbin's friends.

SELECT *.friends,id.accounts,user_name.accounts FROM friends,accounts WHERE user_name.accounts = 'corbin' friends.user_id = id.accounts

[Jewbilee]

heres the problem with the above parts.. since one field is named user_id and the other is friend.. what if i wanted to display friend's friends and not user_id's...

like..

say:

bob & fred
bod & burt
burt & fred..

you see?  How would i display all of burts friends?

[corbin]

SELECT * FROM accounts,friends WHERE accounts.user_name = 'burt' AND (friends.user_id = account.id OR friends.friend_id = accounts.id)

[Jewbilee]

im a little confused how that query works..

[corbin]

Hmmm what it does is it pulls everything from the accounts and friends tables and treats it like one giant result set.

Then it would pull the data from accounts where the column user_name is burt  Then it would use the id pulled from the accounts table and use it for checking the id against the user_id and friends_id fields in the friends table.

[Jewbilee]

hmm.. ill give it a try a little later..

[ki]

You might try a database consisting of 2 columns
my_id, friend_id

Add
----
$aFriend = "INSERT INTO `user_friends` (`my_id`, `friend_id`) VALUES ('".$myID."', '".$myFriendsID."')";
$addFriend = mysql_query($aFriend);
  if(!$addFriend) die(mysql_error());

List Friends
-----------
echo "<table>";
$listFriends = mysql_query("SELECT * FROM `user_friends` WHERE `my_id` = '".$myID."'");
while ($myFriends = mysql_fetch_object($listFriends)) {

$getDetails = mysql_query("SELECT * FROM `user_details` WHERE `id` = '".$myFriends->friend_id."');
$getFD = mysql_fetch_object($getDetails);

echo "<tr><td><a href=\"?page=profile&id=".$getFD->id."\">".$getFD->username."</td></tr>";

}
echo "</table>";

--------------

This code should work.