MySQL Relational friends scheme?

I was wondering how you'd work a PHP and MySQL Friends system? 开发者_StackOverflow社区I was thinking like, in the users table there would be a colum titled friends which would hold data with other user IDs who they are friends with seperated by commas,

for example, 1,3,56,3 - then explode this and foreach the array?

Would that work?

That is one way to do it.
You may want to consider creating a new row for each friend.

For example:

Friend ID: 1 Friend Name: Bob

Friend ID: 2 Friend Name: Ron

Friend ID: 3 Friend Name: Joe

If Bob was friends with both Ron and Joe there would be 2 records in the friends table

id  user  friend  
1   1     2  
2   1     3

Then if joe became friends with bob but not ron the table would end up being

id  user  friend  
1   1     2  
2   1     3  
3   3     1

This gives you flexibility down the road to add in more complex queries.

Don't do it as comma-separated strings, have a user_friends table with each friend relationship as a record. A comma-separated string isn't going to be usefully indexable or queryable.

Important example:

Friend 1 is connected with Friend 2 You have to lookup the user_id AND the friend_id to find all the friends of Friend 1