You know you may get much gay love from me username1, but this time I have to correct you I'm affraid. A comma seperated list is definately not the way to go here.
I would suggest making a cross-link table, with 2 columns: userID1 and userID2. Each time someone adds someone else as a friend, you insert a new row with the first users ID in column 1 and the second users ID in column 2 or vice versa. This will look something like this:
Code:
INSERT INTO crosstable_friends (userID1, userID2)
VALUES ('this_users_ID', 'friends_users_ID')
. You might want to check wether this connection already exists first by doing this:
Code:
SELECT COUNT(*)
FROM crosstable_friends
WHERE userID1 = 'this_users_ID'
AND userID2 = 'friends_users_ID'
OR WHERE userID1 = 'friends_users_ID'
AND userID2 = 'this_users_ID'
- if the result is bigger then 0 you don't perform the insert query. This can also be fitted into 1 query, but if I'm going to just write that out chances are good I make a mistake since conditional insert queries tend to be a bit tricky. Best to fiddle around with it yourself.
Now when you want to know who you added as a friend, you perform
Code:
SELECT *
FROM crosstable_friends
LEFT JOIN usertable
ON userID2 = usertable.userID
WHERE userID1 = 'this_users_ID'
and to know who added you as a friend you perform
Code:
SELECT *
FROM crosstable_friends
LEFT JOIN usertable
ON userID1 = usertable.userID
WHERE userID2 = 'this_users_ID'
(note that both queries can be made into one single query that selects all your friends, but I'd like to have the extra data available).
This is much faster then username1's solution because you don't have to do a full-text search over an entire table. It is also much smaller because all you store are integers, which are a lot smaller then character strings
Optionally you can store addition information, like when the friends connection was made, or wether it was accepted, in additional columns. This is quite easy once you get the main idea. Again, I suggest you fiddle around with it a bit :smile:
And yes, this does require one to know a bit of PHP coding. If you do not, I suggest you read my guide
here