Storing friends in mysql

[Crew-Portal]

I have a database with all users.. Within that database all users have an id. How would I make it so users can "friend" other users? Would I need to make a new table for that?

 

Im sorry for the dumb question.. I havent dealt with php or mysql in about two years because I was busy getting engaged and all.. But now that I have time again I think im gonna take up one of my old projects. Granted I probably could have answered this question myself back then but now im finding some trouble taking up programming again. :\

[ManiacDan]

You need a friends table, yes.  It will link two userIDs and probably have a "friends since" field, maybe even a "relationshipType" field for relatives and partners.

[The Little Guy]

I would make second table like posted above, containing: id, member_id, friend_id, is_friends, friend_date:

 

create table friends (id int unsigned auto_increment primary key, member_id int unsigned, friend_id int unsigned, is_firends boolean default 0, friend_date timestamp);

 

Assuming you don't want them to be friends right away, we set is_friends equal to 0.

 

When someone requests someone else's friendship, we do this:

 

<?php
$member_id = 123;
$friend_id = 321;
mysql_query("insert into friends(member_id, friend_id) values ($member_id, $friend_id)");
?>

 

Next you need to notify the friend that they have a pending friend request, something like this:

 

<?php
$member_id = 321;
$sql = mysql_query("select count(*) from friends where friend_id = $member_id and is_friends = 0");
$row = mysql_fetch_array($sql);
if((int)$row[0] > 0){
echo "<p><a href='/requests.php'>New Requests</a></p>";
}
?>

 

then when the member accepts the request, you would do something like this:

 

<?php
$member_id = 321;
$friend_id = 123;
mysql_query("update friends set is_friends = 1 where member_id = $friend_id and friend_id = $member_id");
mysql_query("insert into friends (member_id, friend_id, is_friends) values ($member_id, $friend_id, 1)");
?>

 

Now if you want to see who you someones friends are, you can just do this:

 

select * from friends where member_id = 123321 and is_friends = 1;

 

Note: $member_id referrers to the logged in member and $friend_id referrers to the friend, you can see some are backwards, that is on purpose.

 

Hope this helps!

[Crew-Portal]

wow, Thanks alot. This was alot more info than I was wanting. Much appreciated

[merylvingien]

Even easier is to make a table, friends, with userid, then friend id as a text field. Deliminate them however you like, space or ; etc

Explode that field out when you need to find who is thier friends.

[The Little Guy]

Even easier is to make a table, friends, with userid, then friend id as a text field. Deliminate them however you like, space or ; etc

Explode that field out when you need to find who is thier friends.

 

super inefficient.

[KevinM1]

Even easier is to make a table, friends, with userid, then friend id as a text field. Deliminate them however you like, space or ; etc

Explode that field out when you need to find who is thier friends.

 

super inefficient.

 

Exactly.  The whole point of the separate ids is that they represent different entities.  Keeping them in separate columns facilitates joins, and allows the db to do the work it was designed for.