[MySQL] Any list type?

[NeoDreamer]

I have a table for members. In one of the columns, I want to be able to store the member's friends list. Which variable type is best suited for this purpose?

[skOOb]

it's called a 'table'. Create a new table and link it to your current members table.
create table member_friends (
member_friend_id int(11) NOT NULL auto_increment,
member_id int(11) NOT NULL,
friend_id int(11) NOT NULL,
PRIMARY KEY (member_friend_id)
)where member_id is your primary key in your members table. now you can insert members and their friends. you could add different fields to this table like 'date_added' or 'friend_status' or something.

If you dont want to do this, I would suggest adding a field to your members table..."friend_list mediumtext default ''" with the member_id #'s of friends seperated by a pipe e.g. So it will show "13|74|289|36" and then you can get the ID's of the members friends.

Obviously the first one is much more efficient and better. hth :beer:

[NeoDreamer]

So you need 1 entry for each friend-to-friend link with your method?

[skOOb]

right. I believe it is the best way. so to get all of member 5's friends, you would do
select friend_id from member_friends where member_id = 5

[bwh2]

sk00b is correct in his design. you may also want to add fields like date_created, is_active, date_destroyed to the table. that way, you can track things like how fast people are adding friends, which relationships are not active and when they were deleted. just a tracking history.

but sk00b forgot something in his SQL. remember, you want to remove redundant data. so for a friendship between member_id 5 and member_id 1, you do not want 2 rows of data. you just want one. 1:5. not 1:5 and 5:1.

so a member's friend list would be
SELECT friend_id,member_id FROM member_friends WHERE member_id=5 OR friend_ID=5i'll think about it a little more to see if i can come up with a SQL-side solution to not selecting the member in question's id each time. it would be nice to remove that on the SQL-side rather than the php-side.