Storing 'friends' for user in SQL

Placebo

need to store friends for a user in an SQL, database.
How would this be possible, without me having to store them in one field with some sort of character divider. Also limit would be varchar, 255.

This would then need to be populated somewhere else.
Need to implement something like this,
http://lesterchan.net/wordpress/2006/07/05/donations/email/

But where they can select their friends !
Thanks.


edit: i actually just a realised i could store friends details, in a different table associated with the user id - DOH

seamus

A normal many:many relationship.

What you do is have one table with two columns - the user's id and the friend's id.

Call it "userfriend" or something.

When you want to get a list of friend ids for a particular person, you just call

SELECT friendid FROM userfriend WHERE userid = 10

Or you can go further and do a join, and get a list of all their friends' details:

SELECT u.* from user u, userfriend uf
WHERE u.userid = uf.friendid
AND uf.userid = 10

The table looks something like this:
userid | friendid
10| 1
10| 3
10| 4
10| 6
10| 8
4| 9
4| 1
4| 6
9| 1

Placebo

thanks seamus, i just realised myself. Il just make a new table and go that easy route

Overflow

seamus wrote: »

A normal many:many relationship.

What you do is have one table with two columns - the user's id and the friend's id.

Call it "userfriend" or something.

When you want to get a list of friend ids for a particular person, you just call

SELECT friendid FROM userfriend WHERE userid = 10

Or you can go further and do a join, and get a list of all their friends' details:

SELECT u.* from user u, userfriend uf
WHERE u.userid = uf.friendid
AND uf.userid = 10

The table looks something like this:
userid | friendid
10| 1
10| 3
10| 4
10| 6
10| 8
4| 9
4| 1
4| 6
9| 1

I concur, that would be the way to do it!