friend system?

[eluser]MMCCQQ[/eluser]
hi!


somebody know how to design mysql db for a friend system like myspace.com ?

[eluser]xwero[/eluser]
two tables is all you need to start

persons : id, name, ...
friends : person_id, friend_of

You could put the friend_of field in the persons table, as long text field filled with a comma separate list, but then you would have to use sql statements like

Code:

select name from persons where friend_of like '%?%'

which aren't that fast as

Code:

select person_id from friends where friend_of=?

I hope this gets you started.

[eluser]woopsicle[/eluser]
So, if bob is a friend of jane.. does that mean jane is a friend of bob's?

Friends table
---------------
Person_id = bob | Friend_of = jane
Person_id = jane | Friend_of = bob

So with the first way you mention xwero, two records for each friendship are required.

I have to say I have never really looked into a 'friends' system but this seems a bit strange.

[eluser]danoph[/eluser]
you should never use like when trying to retrieve a specific person or record...

i have coded websites with buddy/friend systems before and I found the best way was creating a new table. That way, you don't have to have comma separated values in your table if bob has more than jane as a friend. what if bob is friends with mike, dan, jill, jane, jack, joe, and sally?

basically:

Code:

describe friends;
+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| id        | int(11) | NO   | PRI | NULL    | auto_increment |
| user_id   | int(11) | YES  |     | NULL    |                |
| friend_id | int(11) | YES  |     | NULL    |                |
+-----------+---------+------+-----+---------+----------------+

If bob's user ID is 1, you can get bob's friends by saying

Code:

select * from friends where user_id = '1';

I believe there are a lot more options available when using a separate table to link users together.

I think you can solve the problem of having two records by using something like:

Code:

select * from friends where (user_id = 1 and friend_id = 5) or (user_id = 5 and friend_id = 1);

[eluser]danoph[/eluser]
actually, i keep thinking about it, and i can't figure out how to prevent having two records, hah.

tough question.

anyone else have an opinion?

[eluser]xwero[/eluser]
[quote author="woopsicle" date="1192792593"]So, if bob is a friend of jane.. does that mean jane is a friend of bob's?

Friends table
---------------
Person_id = bob | Friend_of = jane
Person_id = jane | Friend_of = bob

So with the first way you mention xwero, two records for each friendship are required.

I have to say I have never really looked into a 'friends' system but this seems a bit strange.[/quote]

It's not strange bob is a friend of jane but jane doesn't have to be a friend of bob. This gives people the freedom to choose their friends.

[eluser]danoph[/eluser]
yes, but how do you perform a SQL query getting all friends with both records matching is the burning question!

[eluser]xwero[/eluser]
I had some problems connecting to the site so i will make it a telegram post

Code:

// myfriends
select friend_of from friends where person_id=?
// friendswithme
select person_id from friends where friend_of=?
// twowayfriends (mysql5)
select person_id from friends where friend_of=? and person_id in (select friend_of from friends where person_id=?)

if you haven't mysql5 on your server you run the first 2 queries and in php you compare the common ids

Code:

$myfriends = $query1->result_array();
$friendswithme = $query2->result_array();
$twowayfriends = array();
foreach($myfriends as $row)
{
    if(is_numeric(array_search($row['friend_of'],$friendswithme))){ $twowayfriends[] = $row['friend_of']; }
}

The code is untested.

[eluser]Phil Sturgeon[/eluser]
I have a simple system for this. Have a FriendRequests table and a Friends table. Have userA and userB in both, userA does the requesting, userB is requested.

CREATE TABLE `FriendRequests` (
`requestID` bigint(20) NOT NULL auto_increment,
`userA` int(11) NOT NULL default '0',
`userB` int(11) NOT NULL default '0',
`viewed` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`requestID`)
);

CREATE TABLE `Friends` (
`friendshipID` bigint(20) NOT NULL auto_increment,
`userA` int(11) NOT NULL default '0',
`userB` int(11) NOT NULL default '0',
`friend_type` int(3) NOT NULL default '0',
`friends_since` int(11) NOT NULL default '0',
PRIMARY KEY (`friendshipID`)
);

This way you can easily show the incoming user requests:

SELECT * FROM FriendRequests WHERE userB = $userID

outgoing user requests

SELECT * FROM FriendRequests WHERE userA = $userID

and friendships

SELECT * FROM Friends WHERE (userA = $userID AND userB = $friendID) or (userB = $userID AND userA = $friendID)

.

The benefit of this is that you have functioniality like MySpace and other sites. If one user deletes the friendship, it is deleted. This echos reality a little more than the two-record system where someone can be friends with somebody who no longer likes them!

I have a friends module which I was going to release, but it has a little too much custom code in it for now, so will finish my project then package it.

[eluser]xwero[/eluser]
[quote author="thepyromaniac" date="1192808169"]I have a simple system for this. Have a FriendRequests table and a Friends table. Have userA and userB in both, userA does the requesting, userB is requested.

and friendships

SELECT * FROM Friends WHERE (userA = $userID AND userB = $friendID) or (userB = $userID AND userA = $friendID)

.

[/quote]

To get friendship data you need two ids but how do you get that second id if you only know the id of the person that is logged in?