[RESOLVED] friends system in database

pizzaengineer

I'm trying to set up a basic friends framework for a small forum and I'm a bit confused on how to go about doing this (total PHP noob here). I'm not sure the best way to approach this. As it is now I have a table for users, a table for friends and everything set up with user ID's. I have the following tables:

table users

id_usr
username
password

table friends

id_friends
userid1
userid2
authorized

I have an insert record set up to insert into friends with the user id of the person adding the friend as well as the friend of the user and everything seems to work one way. I want to have it so when a friend accepts the request they are both mutually friends. Can this be done with a single record in the database or do I need two entries to do this? Thanks

leatherback

I personally always use 2 records, but that is also because I want to be able to differentiate between the two: Person A might see person B as a friend, but not vise versa. Having two records would solve that problem.

It also makes it easier later on when you would like to get all friends from friend A:
"Select userid2, username from friends left join users where user1id = $thisperson2"

Instead of having to go through double search/linking with surrogate tables.

pizzaengineer

Thanks for getting back to me, but I think I need a little more clarification on one thing.

For my purpose, I want Person A and Person B to BOTH be friends once one of them accept the friend request. This is pretty much the key problem I am having. Can it be done with a complex query string?

leatherback

It COULD be done but you need to start working with synonyms for colums and stuff. I personally would just add the relationship twice, as it is much easier to handle

But something like ehm..

(select userid1 as userid from friends where userid2=$SearchedUser)
union
(select userid2 as userid from friends where userid1=$SearchedUser)

could work, I presume

pizzaengineer

I'm trying to set up a friends system using one record per friendship in this forum I'm building. I have two tables, one called friends which holds the relationships between users and one called users, which holds the users name, details, etc... Here's what the friends table looks like populated with data:

mysql> select * from friends;
+----+---------+---------+------------+
| id | Friend1 | Friend2| authorized |
+----+---------+---------+------------+
|  1 | 1       | 2       | y          |
|  2 | 2       | 3       | y          |
|  3 | 3       | 1       | y          |
|  4 | 4       | 1       | y          |
+----+---------+---------+------------+
4 rows in set (0.00 sec)

I want to be able to display a list of friends filtered with a select statement. Here is what I have so far:

SELECT *
FROM friends LEFT JOIN user_usr ON (some statement here)
WHERE (Friend1 = colname AND AreFriends = 'y') OR (Friend2 = colname AND AreFriends = 'y')

Can anyone help me out here on what to put into the (some statement here)? Thanks

leatherback

You have me at a loss here. Didn't we talk about this just yesterday?

Why did you start a new thread?

leatherback

Basically, this is exactly why I adviced you to work with double links. However, if you use the example I gave you, you should be able to get the users' details through the synonyms used for friendid1 and friendid2

bpat1434

You can just as easily use two joins of the same table with different references. You'd want to capture the username, so you'd have a case or if statement in the SELECT clause to account for it. Something like:

SELECT f.*, 
    CASE f.`Friend1` WHEN '<userID>' 
        THEN u2.`username` 
        ELSE u1.`username` 
    END AS `username`
FROM `friends`
INNER JOIN `user_usr` AS u1
    ON f.`Friend1`= u1.`id`
INNER JOIN `user_usr` AS u2
    ON f.`Friend2` = u2.`id`
WHERE f.`AreFriends` = 'y'
  AND (f.`Friend1` = '<userID>' OR f.`Friend2` = '<userID>')

Now, <userID> would be the userID associated with the current user (so for me that would be 163486 for this forum).

Now, the SQL is easy to understand. You're just bringing the user information in twice so that every user ID gets a name associated with it. That's the two "INNER JOIN .... " clauses. We create references to them using "AS u#" to which we use in the SELECT and WHERE clauses.

The SELECT clause gets everything from the friends table, and a nifty CASE statement which says the following
WHEN Friend1 has the associated userID of <userID> THEN we use u2.username ELSE use u1.username and store that as the column "username".

So now, if you ran that, you should be able to do a quick dump of usernames and have only those friends that have accepted for that user.

Hope that helps.