Is there a way to extract..

[Azaz]

So I make a colum in my table called "friends"

 

Is there a way to update using mysql that colum for each user, so let's say friend 1 adds friends 2 with the id of 25

 

so the query puts the id "25" into the friends column, then if friend 1 adds friend 5 with the id of 26 it puts "26" into the friends column and so on...

 

So it would have like commas in the colum, 25,26,30,31,31 and all those represent the id's of the person who is wanting to add people to his friends list!

 

 

If so how do I accomplish that, and then what If I want to use mysql to list all those id's and

 SELECT name,avatar,etc from userstable WHERE id = "25,26,3,31,31"

 

Is this even possible or am i thinking to harD?

[Masterprise]

My suggestion would be to make a friends table rather than a column. In the friends table have two columns: id, and friend_id (or similar). So when friend 1 adds friend 2 (say friend 1 has an id of 12), insert a record into the table with the id value of 12 and friends_id value of 25. When friend 1 adds friend 5, add a record with the id value of 12 and friend_id value of 26, and so forth.

 

When you want to retrieve all of the friends of friend 1, do something like

 

SELECT name,avatar,etc 
FROM userstable 
INNER JOIN friends ON userstable.id = friends.id
WHERE friends.id = 12;

 

 

[Azaz]

Ok sweet Masterprice, I was thinking of that too! but now I have 1 other problem,

 

What if I want to make it so they have to accept them to be a friend? Would I need a table called friend_reqs

 

or is it simplilar?

 

 

Thanks for your response tho if you could chime in here 1 more time i would love it.

[Masterprise]

No, you shouldn't need another table, just another column in the friends table. The table would have something like id, friend_id, and accepted now. The accepted column should have a default value of 'no', 'false', 0, or something like that.

 

When friend 5 accepts the request of friend 1, Have the script update the corresponding record in the friends table and change the accepted column for that record to 'yes', 'true', or 1 (whatever you feel like using. The ENUM type works great for these types of columns).

 

Now, when you want to get all of the friends for friend 1 do something like

 

SELECT name,avatar,etc
FROM userstable
INNER JOIN friends ON userstable.id = friends.id
WHERE friends.id = 12 AND accepted = 'yes';