Friends list SQL help

[gunnarflax]

Hi!
I'm trying to create my own little community for learning purposes and I'm having some trouble with getting the data I want from the table. I'm not that high on SQL but I know the basics but when you get into the more complex mysql-lines I get lost.

The problem I'm facing is that I have a table with 3 columns: id1, id2 and confirmed. I read about some techniques for sorting a user's friends in this thread. I decided to use the second method described, meaning that for every friend added you create 2 rows in the table:

+--------+--------+--------+
|  id1   |   id2  | conf...|
+--------+--------+--------+
| user   | friend |    1   |
+--------+--------+--------+
| friend |  user  |    0   |
+--------+--------+--------+

When a user adds a friend, one row is created showing that the user is a friend of the other user and another row is created showing that friend is now a friend of the user who added the friend. But the latter row shows that the other user hasn't accepted the friend request yet. That is shown in the confirmed column: 1 equals an accepted request and 0 a not yet accepted friend request.

With this information I want to be able to retrieve 3 different arrays of data with PHP. First, I want to be able to find out who the user has confirmed friend relationships with:

+--------+--------+--------+
|  id1   |   id2  | conf...|
+--------+--------+--------+
| user   | friend |    1   |
+--------+--------+--------+
| friend |  user  |    1   |
+--------+--------+--------+

Secondly, I want to be able to find out who the user has pending requests with:

+--------+--------+--------+
|  id1   |   id2  | conf...|
+--------+--------+--------+
| user   | friend |    1   |
+--------+--------+--------+
| friend |  user  |    0   |
+--------+--------+--------+

Thirdly, I want to be able to list the requests made to the user by other users which the user himself needs to accept:

+--------+--------+--------+
|  id1   |   id2  | conf...|
+--------+--------+--------+
| user   | friend |    0   |
+--------+--------+--------+
| friend |  user  |    1   |
+--------+--------+--------+

I've tried something like this to retrieve the first described data:

(SELECT id2 FROM friends WHERE id1='$user' AND confirmed=1)
UNION
(SELECT id1 FROM friends WHERE id2='$user' AND confirmed=1)

But for some reason I still receive the ones that still hasn't accepted the requests and I thought that the following code would only bring the pending friend requests but I still receive the same result as in the previous code:

(SELECT id2 FROM friends WHERE id1='$user' AND confirmed=1)
UNION
(SELECT id1 FROM friends WHERE id2='$user' AND confirmed=0)

So I was hoping someone could help me here to get the correct SQL-syntax since I'm obviously doing something terribly wrong :) Thanks for your help!

P.S. Later I want to add the option to block a user's friend requests by having confirmed set as 2 or something like that. If you would also help me with that it would be greatly appreciated (then of course I would name the "confirmed"-column to something more appropriate, like "status" or something).

[nileshgr]

You need a GROUP BY clause.
For example, this query below will return pending friend requests for user1:

select * from foo group by conf,id1,id2 having conf = false and id1='user1';

Alter the above query's conf, id1 and id2 in the having part to get the desired results.

Hope it helps !

Note: If you have the conf column as INT then specify 0 or 1 accordingly.

[gunnarflax]

This works almost as I want it too. The example code you gave me works as it should:

SELECT *
FROM friends
GROUP BY confirmed,id1,id2
HAVING confirmed = 0
AND id1='$user'

It selects the users that have requested to be friends with the user. And when changing the to "AND id2='$user'" I get the users who I have requested friendship with. But how should I do to select all friends who I have an accepted friendship with? Anyway I do it I always receive all friends: accepted, pending and requested. Or accepted and the ones I've requested.

This code gives me all kinds of confirmed options:

(SELECT id2 FROM friends WHERE id1='$user' AND confirmed = 1)
UNION
(SELECT id1 FROM friends WHERE id2 = '$user' AND confirmed = 1)

And this gives me all except the ones that has asked me:

SELECT *
FROM friends
GROUP BY confirmed,id1,id2
HAVING confirmed = 1
AND id1='$user'

Any help appreciated!

[gunnarflax]

Or would it be easier to use several tables, one for requests, one for accepted friends and one for blocked friends? I went with this design I have now as it seems easier on the server.

[nileshgr]

Avoid UNIONS and JOINS as far as possible. Keep the thing simple because UNIONS and JOINS can be a performance hit. It is used only when there's no other way out.

I didn't understand your problem correctly, but I feel I got the solution.

This is the row structure:

id1   |  id2   | confirmed 
--------+--------+-----------
 user   | friend | t
 friend | user   | f
 user1  | friend | t
 friend | user2  | f

(confirmed is bool)

This is the query:

select * from foo group by id2,id1,confirmed having id2 = 'friend' and confirmed = true;

This is the output:

id1  |  id2   | confirmed 
-------+--------+-----------
 user1 | friend | t
 user  | friend | t

Is it the desired result ?

[gunnarflax]

The problem with the code above is that it doesn't only get the confirmed relationships, but also the requests made by other users.

When a user (user) wants to add a friend the friend (friend) needs to accept the request. So when (user) sends the request these entries are made into the database:

id1   | id2    | confirmed
------+--------+-----------
user  | friend | true
friend| user   | false

showing that User wants to be friend with Friend but Friend still needs to accept this request. So when you see it the other way around, when the Friend wants to add User as a friend:

id1   | id2    | confirmed
------+--------+-----------
friend| user   | true
user  | friend | false

It shows that Friend is open for a "friendship" with User but User still needs to accept this. This is how I thought would be the best way to manage a friend list because I don't need more than one table for this.

The problem appears when I want to retrieve the accepted friendships of User:

id1   | id2    | confirmed
------+--------+-----------
User  | Friend | true
Friend| User   | true

Then when I use the code from the previous post:

SELECT * FROM foo GROUP BY id2,id1,confirmed HAVING id2 = 'friend' AND confirmed = TRUE;

I also receive the requests made from User to Friend. Since confirmed for User is true.

I hope I've manage to explain the problem. The SQL code needs to retrieve both the rows where id1 = User, id2 = Friend confirmed = true and where id1 = Friend, id2 = User, confirmed = true and not where only one of the rows is confirmed = true.

[nileshgr]

Try this:

select * from foo where ( ( id1 = 'friend' and id2 = 'user' ) or ( id1 = 'user' and id2 = 'friend' ) ) and confirmed = true;

This is the table structure:

id1   |  id2   | confirmed 
--------+--------+-----------
 friend | user   | t
 user   | friend | t

Then I run this:

update foo set confirmed = false where id1 = 'user' and id2 = 'friend';

Now the output is:

id1   | id2  | confirmed 
--------+------+-----------
 friend | user | t

[gunnarflax]

I don't think that will work since I don't have the friend's id. I'm trying to view all the friends of User so Friend will change every row. Do you understand what I mean?

[gunnarflax]

Maybe I should change the way I'm storing data in the database? I don't see why I shouldn't be able to do this:

Instead of creating two rows for every relationship between users I can have one which shows who befriended who by setting id1 as the user who requests a friendship with another user (friend, id2). Then I should be able to sort out requested friendships and the ones who has asked the user himself by checking both the confirmed cell and id1 or id2, right? The thing is that when I should display all the friends of one user I will have to search both the id1 and id2 column for the user's name and that confirmed = true. Is this a great performance loss?

[nileshgr]

Yeah exactly, use id1 and id2 to indicate the direction of friendship. Also, for getting accepted friendships, use two columns confirmed1 and confirmed2. If both of them are confirmed then the friendship is confirmed two way, else it is one way.

[gunnarflax]

I will try this and rewrite the code. I'll let you know if it works!

[gunnarflax]

Yes! It works perfectly! The database now looks like this:

id1    | id2    | status1 | status2 |
-------+--------+---------+---------+
user1  | user2  | 1       + 1       |

status1 and status2 aren't boolean's since I want to be able to block users by putting status = 2. So at the moment status1 and status2 are int's.

The system is as following:
* When a user (user1) adds a friend (user2) a new row is created in the database where user1 is id1 and user2 is id2.
* status1 is set as 1, meaning that user1 (id1) is open for a friendship with user2 (id2)

This way I can find out who is asking to be friends with you:
id2 = USER, status1=1 and status2=0.

I can also find out who YOU have asked to be friends with you:
id1 = USER, status1=1 and status2=0.

This works perfectly since I won't select any relationship which is blocked by any part. But the problem appears when I want to print out the result for selecting the friendships who both parts have accepted. I have a script that looks like this:

$result = friends($profile['username']);

while ($row = mysql_fetch_array($result)){
       echo $row['id2']."<br />";
}

But I can't know which of the id's is the user's id since it depends on who befriended who.

So far I've solved it like this:

$result = friends($profile['username']);

while ($row = mysql_fetch_array($result)){
       if ($row['id1'] == $profile['username']){
               echo $row['id2']."<br />";
       }
       else {
               echo $row['id1']."<br />";
       }
}

But it doesn't feel professional to me. Should I instead return an array from the function containing only the information I need? But then I can't take use of the excellent...

while ($row = mysql_fetch_array($result)){

...method.

Any suggestions?

[nileshgr]

You can put a OR predicate in the SQL query, something like this:

SELECT * from friends WHERE ( id1 = 'user1' or id2 = 'user1' ) and ( status1 = 1 and status2 = 1 );

This will return all the results which have both parts confirmed.

[gunnarflax]

Thanks but that wasn't my question :) maybe I explained it poorly? Anyhow, I think the problem is solved by now. Thank you so much for your help itech7! I've appreciated it greatly!

[nileshgr]

I love helping people :)