mysql buddy list

[nahsorhseda]

hi iam trying to build a community site
iam trying to make friends list for a user my database looks somewhat as below

id username pssw age friends etc etc
1 rrtyrthrt trrtry yy
2 trtrert tert yy

but the problem is ill be abe to add only one friend per user
many told me to make another table but u show me how a another table should look like
or is there a another way

 

[easykey]

You do need a 2nd table with three numerical fields:

id
friend_id
buddy_id

so data could be for example:

id=1, friend_id=13, buddy_id=26
id=1, friend_id=13, buddy_id=78

then friend 13 can have buddy's 26 and 78...

to read do:

SELECT buddy_id FROM buddy_table WHERE friend_id=(whoever)

 

[nahsorhseda]

why do i need 2 fields i need only a buddy list
and also ,is the id is same as the one used in the users table

 

[easykey]

friend_id and buddy_id both contain users ids. This table is just to hold relationships between users. You get the buddy list by doing a SELECT on the buddy table as I said before. This select statement gives you a list of user ids that are buddies of that friend.