php mysql query select mutual friends?

[shortysbest]

my friends database is set up with two columns, friend_1 and friend_2. The code I use to find friends of a single user is:

 

$find_friends = mysql_query("SELECT * FROM friends WHERE (friend_1='".$id."' OR friend_2='".$id."')");

 

however now I need to select the rows that two users have in common.

for instance:

 

friend_1 -------friend_2

1 ------------------2   

6 ------------------2

2 ------------------7

6 ------------------1

1 ------------------7

 

so if 1 is me and 2 is the users page I'm on, it should return 6,7. (not returning my or the users id)

what makes it complicated is that the ids of users can be in either row, so u can't just select 1 row and compare to the other.

 

[Muddy_Funster]

$find_friends = mysql_query("SELECT friend_1, friend_2 FROM friends WHERE (friend_1='".$id."' OR friend_2='".$id."') AND (friend_1<>'".$my_id."' OR friend_2 <>'".$my_id."')");

 

Not sure why your ID fields arn't numerical but I think that should get you going. $my_id will need to be assigned to the current user ID.

[kickstart]

Hi

 

Another idea, but I think I might be over complicating things.

 

This is assuming $id is one persons id and $id2 is the other persons id.

 

SELECT z.aFriend
FROM (SELECT if(a.friend_1 = $id,a.friend_2,a.friend_1) AS aFriend
FROM friends a
WHERE (a.friend_1 = $id
AND a.friend_2 != $id2)
OR (a.friend_2 = $id
AND a.friend_1 != $id2)) z
INNER JOIN (SELECT if(a.friend_1 = $id,a.friend_2,a.friend_1) AS aFriend
FROM friends a
WHERE (a.friend_1 = $id
AND a.friend_2 != $id2)
OR (a.friend_2 = $id
AND a.friend_1 != $id2)) x
ON z.aFriend = x.aFriend

 

All the best

 

Keith

[shortysbest]

Thank you both very much, unfortunately Kickstart I haven't been able to get yours to work.

 

Muddy_Funster, yours works great, however I was wondering if you could include in the mysql_query, because I also need to count the number of mutual friends and without this in the query it may count some rows it shouldn't.

 

$mutual_friends = mysql_query("SELECT friend_1, friend_2 FROM friends WHERE (friend_1='".$id."' OR friend_2='".$id."') AND (friend_1<>'".$session."' OR friend_2 <>'".$session."')");
while($mutual_check = mysql_fetch_array($mutual_friends))
{
$mutual_find = $mutual_check['friend_2'];

//this just checks the rows to determine which column it needs the id from, since user ids could be reversed depending on how they were put into the database
//(friend_1 is the user that accepted the friend request, friend_2 sent the request)

if($mutual_find==$session||$mutual_find==$id)
{
$mutual_id = $mutual_check['friend_1'];	
}
else 
{
$mutual_id = $mutual_check['friend_2'];	
}

//this removes the id of the session so it doesn't show their own friendship within mutual friends
if($mutual_id!=$session){
////the rest of the code is irrelevant

 

Thanks a ton.

[kickstart]

Hi

 

Worked when I had a play so maybe something to do with field types if you are using character id fields.

 

It only brings back one column which should contain all the mutual friends irrespective of which column in the table they are in.

 

$mutual_friends = mysql_query("SELECT z.aFriend FROM (SELECT if(a.friend_1 = '$id',a.friend_2,a.friend_1) AS aFriend FROM friends a WHERE (a.friend_1 = '$id' AND a.friend_2 != '$session') OR (a.friend_2 = '$id' AND a.friend_1 != '$session')) z INNER JOIN (SELECT if(a.friend_1 = '$id',a.friend_2,a.friend_1) AS aFriend FROM friends a WHERE (a.friend_1 = '$id' AND a.friend_2 != '$session') OR (a.friend_2 = '$id' AND a.friend_1 != '$session')) x ON z.aFriend = x.aFriend ");

 

All the best

 

Keith

[shortysbest]

Thanks, still having trouble, but how are you suppose to retrieve the id? If I just replace your query with the other one it doesn't work, well it partially works, it does loop the correct number of times, however I just need to retrieve the id, not sure how I would get it without selecting a specific column.

 

 

this is the entire block of code:

 

<?php  
//$mutual_friends = mysql_query("SELECT friend_1, friend_2 FROM friends WHERE (friend_1='".$id."' OR friend_2='".$id."') AND (friend_1<>'".$session."' OR friend_2 <>'".$session."')");
$mutual_friends = mysql_query("SELECT z.aFriend FROM (SELECT if(a.friend_1 = '$id',a.friend_2,a.friend_1) AS aFriend FROM friends a WHERE (a.friend_1 = '$id' AND a.friend_2 != '$session') OR (a.friend_2 = '$id' AND a.friend_1 != '$session')) z INNER JOIN (SELECT if(a.friend_1 = '$id',a.friend_2,a.friend_1) AS aFriend FROM friends a WHERE (a.friend_1 = '$id' AND a.friend_2 != '$session') OR (a.friend_2 = '$id' AND a.friend_1 != '$session')) x ON z.aFriend = x.aFriend ");
while($mutual_check = mysql_fetch_array($mutual_friends))
{
$mutual_find = $mutual_check['friend_2'];

if($mutual_find==$session||$mutual_find==$id)
{
$mutual_id = $mutual_check['friend_1'];	
}
else 
{
$mutual_id = $mutual_check['friend_2'];	
}
if($mutual_id!=$session){
$mutual_photo = photo($mutual_id, 55);
$mutual_name = name($mutual_id, 'fml');
?>
<li id="<?php print $mutual_id?>">
<img onClick="location.hash='#/profile&id=<?php print $mutual_id?>'" class="friends-thumb" src="<?php print $mutual_photo?>" /><div class="friends-name" id="hover<?php print $mutual_id?>"><div class="friend-arrow-border"><div class="friend-arrow"></div></div><a href="#/profile&id=<?php print $mutual_id?>"><?php print $mutual_name?></a></div>
</li>
<?php
}
}
?>

[kickstart]

Hi

 

Something like this

 

<?php  
$mutual_friends = mysql_query("SELECT z.aFriend AS FriendId FROM (SELECT if(a.friend_1 = '$id',a.friend_2,a.friend_1) AS aFriend FROM friends a WHERE (a.friend_1 = '$id' AND a.friend_2 != '$session') OR (a.friend_2 = '$id' AND a.friend_1 != '$session')) z INNER JOIN (SELECT if(a.friend_1 = '$id',a.friend_2,a.friend_1) AS aFriend FROM friends a WHERE (a.friend_1 = '$id' AND a.friend_2 != '$session') OR (a.friend_2 = '$id' AND a.friend_1 != '$session')) x ON z.aFriend = x.aFriend ");
while($mutual_check = mysql_fetch_array($mutual_friends))
{
$mutual_photo = photo($mutual_check['FriendId'], 55);
$mutual_name = name($mutual_check['FriendId'], 'fml');
?>
<li id="<?php print $mutual_id?>">
<img onClick="location.hash='#/profile&id=<?php print $mutual_check['FriendId'];?>'" class="friends-thumb" src="<?php print $mutual_photo?>" /><div class="friends-name" id="hover<?php print $mutual_check['FriendId'];?>"><div class="friend-arrow-border"><div class="friend-arrow"></div></div><a href="#/profile&id=<?php print $mutual_check['FriendId'];?>"><?php print $mutual_name?></a></div>
</li>
<?php
}
?> 

 

No need to figure out which column it comes from as the SQL sorts that out.

 

All the best

 

Keith

All the best

 

Keith

[shortysbest]

Ahhh, I see, looked right over the FriendId at the beginning! Thanks works perfectly.

 

[kickstart]

Hi

 

Being fair I hadn't given it an alias id in the earlier select I pasted. Put an alias of FriendId in there to make it more obvious.

 

All the best

 

Keith

[shortysbest]

Ah.

[shortysbest]

Do you know how to get this to work in the query?

 

$mutual_friends = mysql_query("SELECT z.aFriend AS FriendId FROM (SELECT if(a.friend_1 = '$id',a.friend_2,a.friend_1) AS aFriend FROM friends a WHERE (a.friend_1 = '$id' AND a.friend_2 != '$session') OR (a.friend_2 = '$id' AND a.friend_1 != '$session')) z INNER JOIN (SELECT if(a.friend_1 = '$id',a.friend_2,a.friend_1) AS aFriend FROM friends a WHERE (a.friend_1 = '$id' AND a.friend_2 != '$session') OR (a.friend_2 = '$id' AND a.friend_1 != '$session')) x ON z.aFriend = x.aFriend ");

while($mutual_check = mysql_fetch_array($mutual_friends));
{
$mutual_id = $mutual_check['FriendId'];
$query = mysql_query("SELECT * FROM posts WHERE from_id='$mutual_id' AND to_id='$id' AND state='0' ORDER BY id DESC");
}

 

Basically I need to select the mutual friends (which is no problem), then I need to select from my posts table where from_id is equal to any and all of our mutual friends, and the post to id is the users profile we're on,

This example doesn't work, the $query goes through a function: main_post_content($query);

which then goes on to:

 

function main_post_content($query, $class, $realtime)
{
if(mysql_num_rows($query)==0&&!$realtime)
{
?>
<div class='stream-no-posts'>There haven't been any posts</div>
<?php 
}
else 
{
while($get_post = mysql_fetch_array($query))
{

 

if that makes any sort of sense to you.

[kickstart]

Hi

 

You should be able to use a JOIN rather than doing a SELECT within a loop (which is normally pretty inefficient).

 

$mutual_friends_posts = mysql_query("SELECT *
FROM posts
INNER JOIN (SELECT if(a.friend_1 = '$id',a.friend_2,a.friend_1) AS aFriend 
FROM friends a WHERE (a.friend_1 = '$id' AND a.friend_2 != '$session') OR (a.friend_2 = '$id' AND a.friend_1 != '$session')) z ON posts.from_id = z.aFriend AND posts.to_id='$id' AND posts.state='0' 
INNER JOIN (SELECT if(a.friend_1 = '$id',a.friend_2,a.friend_1) AS aFriend 
FROM friends a WHERE (a.friend_1 = '$id' AND a.friend_2 != '$session') OR (a.friend_2 = '$id' AND a.friend_1 != '$session')) x ON z.aFriend = x.aFriend  ");

while($row = mysql_fetch_array($mutual_friends_posts));
{
// Some coding here to output it
}

 

All the best

 

Keith

[shortysbest]

Thank you very much, you're great! hah. I'm horrible at the more 'advanced' mysql, didn't know you could use if statements in them.

[kickstart]

Hi

 

No problem.

 

Please note that while I put SELECT * in the example, in real life it is a bad idea to use it. Instead always specify the fields you want back.

 

SELECT * is a useful shortcut when you need data in a hurry, but best avoided for anything running in the long term.

 

All the best

 

Keith

[shortysbest]

oh, what does it make the query take longer to complete? Would make sense, if so do you by any chance know how significant the speed difference would be? Also do you know how to show the amount of time it takes for a script to run and complete?

[Muddy_Funster]

using SELECT * opens holes in security, reduces scalability, wastes resources and generaly annoys the hell outa me .

 

there is never any benifit to using SELECT * over explicitly named fields (even if you want all the fields) other than "it's less to type".  Personaly I think that, if you don't want to type, you have the wrong hobby going on.

 

Think about it - If you are going swimming, and your swimwear is in your bedroom, do you take the ENTIRE contents of your room (bed & PC included) with you to the pool and then find your swimwear after you get there, leaving all your other stuff just lying about?  'cource you don't, that would just be silly.  That's basicly using a SELECT * on your room.  you wouldn't do it in real life, you shouldn't do it in a database.

 

Not sure about getting the time taken to show, I know it can be done, just don't know how.

[kickstart]

Hi

 

Not sure it will make a major difference to the time to complete the query (although once complete you could be pulling back a substantial amount of extra data which could be a big issue with a remote database - especially if one column on the table is a large field containing images for example).

 

However the problem comes if the table designs change. If someone adds a column to a table and you are reading into an array rather than an associative array then column 5 (for example) will no longer be what you expect. Also possible that with a JOIN a column added to one table could duplicate a column name in another table leading to errors.

 

It does have its uses when playing around trying to get something to work and on here I do use it when just showing an idea but would recommend never to use it in live code.

 

All the best

 

Keith

[shortysbest]

Thank you both, I guess I have about 40 files to edit o,O. I found a way to time the response, and I ran through I bunch of tests, overall I got it was about 5-9% faster to define the fields, than not to. My website probably has easily 150 or more queries in it, so that could potentially be substantial along with the added security.

While we're talking about security, I'm not very well educated with how to make a secure website, I have very little security on my site, which is an issue I need to address if I have any plans for my website.

[Muddy_Funster]

Yeah, it's always best to get into the practice of securing your site early on.  What I recomend is reading up on some examples (both here and other sources) of data sanitisation and the use of SALT with encription.  And if you hit any other questions post up a new thread in the php section, there are some smokin' programs on this site, and if you give your post a relevent title you should get a quick response.

[shortysbest]

Thanks, I've been looking around and I would just like to ask you personally how you think the best way is for a secure login. Like setting a Cookie. Right now all I do is set the cookie as the users unique id from the database. (just for simplicity as I use that id throughout my page for mysql queries).  I've seen some people use uniq id's for each session,however I'm not entirely sure what the best way would be.

Without very much knowledge I was thinking possibly storing a uniq id in the databse alongside the users id and each time they log in it uses a different uniq id. (also my cookies default expiration is 1 month, again just as a simple variable for the time being)

[kickstart]

Hi

 

Store the session id. For a bit more you can also have a table of current logins with the session id in there, along with time it was last used, IP address and anything else. You can check that the ip address matches the one the session was last attached to (might screw up with some ISPs), that the user agent is the same and that the time since they last did anything is within reason.

 

Also means that you can disconnect people easily by deleting their row from the current logins table forcing them to login again.

 

All the best

 

Keith

[Muddy_Funster]

Cookies arn't ideal.  Like javascript, they are required to be enabled at the client side, something you have no control over.  Using a PHP Session as Kickstart has suggested would be better.  Cookies are, ultimately, for the EU benefit, not the web servers (although it has become common for people to use them for evil).

...

Store the session id. For a bit more you can also have a table of current logins with the session id in there, along with time it was last used, IP address and anything else.

...

A lot of the additional information will be dependant on your hosting provider supporting it with their PHP install configuration.  And be carefull about restricting by IP address aswell, I access sites from my home and my office, using (obviously) two totaly different IP's.  Just stuff to think about.  Follow what Kickstart said about Sessions and having a logins table and you will be starting in the right direction.

 

Good luck

[kickstart]

And be carefull about restricting by IP address aswell, I access sites from my home and my office, using (obviously) two totaly different IP's.

 

In such a situation I would expect you to log in again (possibly with some auto login functionality if required).

 

That said IP address is a pain as some ISPs reallocate IP addresses regularly. AOL is one that plays around like this I think.

 

All the best

 

Keith

[Muddy_Funster]

AOL is a poain for a lot of things - although I was just reffering to the need to be carefull about the way restrictions are placed, if a user is bound to an IP at signup, and that information stored and recalled for validation at login, then there will likely be problems.

[kickstart]

Hi

 

Sorry, maybe should have made it clearer. No intention to care long term about the IP address when they sign up to the site. Just when they log in you store it. If it changes then it is not the same user so you log them out and present them with the login screen again. Same for the user agent.

 

All the best

 

Keith