Friends

[waynewex]

I've a query that I can't seem to get my head around.

 

Basically, I have two tables:

 

• user
  
• friendship
  

The table user holds all user related data. Its primary key is called user_id.

My friendship table looks like this:

 

  `friendship_id` int(11) unsigned NOT NULL AUTO_INCREMENT,

  `user_a` int(11) unsigned NOT NULL,

  `user_b` int(11) unsigned NOT NULL,

  `friendship_started` datetime NOT NULL,

 

Say for example, I visit somebody's profile. I'll get to see his or her friends. The problem is, his or her user_id could exist in either user_a OR user_b, hence the reason I'm having a problem in joining the friendship table with the user table.

[Mchl]

You can either insert friendships two ways

(i.e. when inserting friendship among users 1 & 2 insert 1,2 and 2,1)

or you can join a UNION of friendship table like this

SELECT user_a,user_b, friendship_started FROM friendships
UNION
SELECT user_b,user_a, friendship_started FROM friendships

 

Other thing to consider is whether you actually need friendship_id column. (user_a,user_b) makes perfect primary key.

[onlyican]

$strSQL = "SELECT * FROM friendship LEFT JOIN users ON friendship.users_a = users.users_id LEFT JOIN users ON friendship.users_b = users.usersID WHERE friendship.users_a = ".$intUsersID." OR friendship.users_b = ".$intUsersID;

 

//RUN QUERY HERE

//Loop through results Showing User Info who is NOT intUsersID

[waynewex]

You can either insert friendships two ways

(i.e. when inserting friendship among users 1 & 2 insert 1,2 and 2,1)

or you can join a UNION of friendship table like this

SELECT user_a,user_b, friendship_started FROM friendships
UNION
SELECT user_b,user_a, friendship_started FROM friendships

 

Other thing to consider is whether you actually need friendship_id column. (user_a,user_b) makes perfect primary key.

 

Thanks for the reply. My main goal is to show the user details of those who are friends with the person's profile I'm viewing. Also, is it nec. bad that I have friendship_id as the primary key? I have an index on both the user_a and user_b cols.

[waynewex]

$strSQL = "SELECT * FROM friendship LEFT JOIN users ON friendship.users_a = users.users_id LEFT JOIN users ON friendship.users_b = users.usersID WHERE friendship.users_a = ".$intUsersID." OR friendship.users_b = ".$intUsersID;

 

//RUN QUERY HERE

//Loop through results Showing User Info who is NOT intUsersID

 

Thanks for the reply. I had to modify your SQL. The query I'm using is:

 

$select = "
		SELECT * FROM friendship
		LEFT JOIN user AS u_1 ON friendship.user_a = u_1.user_id 
		LEFT JOIN user AS u_2 ON friendship.user_b = u_2.user_id 
		WHERE friendship.user_a = '".$user_id."' OR friendship.user_b = '".$user_id."'";

 

Your original one was scolding me about aliases and "user" not being unique. The above query is doing the reverse of what I want to do, which means I must be kind of close. Basically, it's looping out only the owner's name and not the name of his friend (I know for a fact that this owner only has one friend).

[waynewex]

Anyone?

[ignace]

I believe you are looking for: http://www.phpfreaks.com/forums/index.php/topic,281823.msg1335964.html#msg1335964

 

A working example can be viewed at: http://webdevkid.wordpress.com/files/2009/03/twitter-clone.png (please don't mind the text