friend system? - Printable Version

friend system? - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: friend system? (/showthread.php?tid=3729)

friend system? - El Forum - 10-18-2007

[eluser]MMCCQQ[/eluser]
hi!

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

friend system? - El Forum - 10-18-2007

[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.

friend system? - El Forum - 10-19-2007

[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.

friend system? - El Forum - 10-19-2007

[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);

friend system? - El Forum - 10-19-2007

[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?

friend system? - El Forum - 10-19-2007

[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.

friend system? - El Forum - 10-19-2007

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

friend system? - El Forum - 10-19-2007

[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.

friend system? - El Forum - 10-19-2007

[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.

Quote: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:

Quote:SELECT * FROM FriendRequests WHERE userB = $userID

outgoing user requests

Quote:SELECT * FROM FriendRequests WHERE userA = $userID

and friendships

Quote: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.

friend system? - El Forum - 10-19-2007

[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

Quote: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?