Facebook-Like Friend Suggestion

Are you developing a social network and just wondering how facebook made friend suggestion. It's easy! In just one MySQL query you can fetch mutual friends for a particular user logged on to your social network site. Keep reading and in a little moment, you will see its no trick at all.

Please Leave a comment behind. If you will like some help, just post me some feedback...

VIEW SCREENSHOT:

Download
This tutorial requires that you create two mysql database tables:

friends - php friend requests
profiles - stores user information

We assume that your images are saved in a folder called thumb. We will also create a file called:

index.php

INDEX.PHP

<html>
<head>
<title>Facebook-like Friend Suggestion - MySQL,PHP by Ohwofosirai Desmond</title>
<style>
#mutualdiv{
        border:1px #ffccff solid;width:200px;font-size:11px;
        margin-top:4px; height:60px;background-color:#f0e0f0;
        }
#mfrendbut{
        background-color:#690069; width:auto; float:right;
        padding:3px;color:#FFFFFF; margin-right:5.6em; font-family:Harrington;
        cursor:pointer; font-weight:100; margin-top:4px;
        }
#mfrendbut:hover{background:#600090}

</style>

</head>
<body>
<h2>Facebook-like Friend Suggestion - MySQL,PHP</h2>
<?php
mysql_connect("localhost","root","") or die(mysql_error());
mysql_select_db("tutorial") or die(mysql_error());
/*
We are actually finding mutual friends from the table where friend requests are stored and joining with table containing profiles. So
database should have table called profiles with columns like "id"-(autoincrement when users register),"email","fname","lname","profilpic"-(for images).
Also have another column called friends with columns like "id", "sender"-(profiles.id of user who sent out friend request), "recepient"-(id of target), status-("accepted or not").
In our script, when a user accepts friend request, the friends table is filled in reverse form. So every friend request accepted produces a second database entry in the sender-to-recepient order: 2 to 1, 1 to 2. Check our mysql dump(exported file) and study the pattern carefully to avoid mistakes.
*/
//The userid below matches profiles.id of the currently logged on user. So fetch it for logged on user.
$userid=1; //use $_SESSION['userid'] if saved in session variable

$den=mysql_query("SELECT distinct count(recepient) as n, profilpic, fname, lname FROM friends r INNER JOIN profiles u ON r.recepient = u.id WHERE sender IN (select sender from friends where recepient='$userid' and status='accepted') AND recepient!='$userid' GROUP BY recepient HAVING COUNT(recepient) >= 1 LIMIT 0, 3") or die(mysql_error());

$i=1;
while($k=mysql_fetch_array($den)){
echo '<div id="mutualdiv" class="mut'.$i.'">
<div style="float:left;width:auto; height:56px"><img style="float:left;margin:3px" src="thumb/'.$k['profilpic'].'" width="50px" height="50px"></div><div><div><div style="float:left"> '.$k['lname'].' '.$k['fname'].'</div><div style="float:right;margin-right:0px;cursor:pointer;" class="close" onclick=$(".mut'.$i.'").fadeOut(600); title="more">»»</div></div><br/><div style="color:#000000; font-weight:100; margin:2px;"> '.$k['n'].' mutual friend</div><div id="mfrendbut">Add as friend</div></div></div>';
++$i;
}
echo "</div>";
?>
</body>
</html>

FRIENDS.SQL

CREATE TABLE `friends` (
   `id` int(11) not null auto_increment,
   `sender` int(11),
   `recepient` int(11),
   `status` enum('accepted','waiting'),
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=18;

INSERT INTO `friends` (`id`, `sender`, `recepient`, `status`) VALUES
('1', '1', '2', 'accepted'),
('5', '2', '3', 'accepted'),
('6', '4', '3', 'accepted'),
('7', '5', '1', 'accepted'),
('8', '5', '4', 'accepted'),
('9', '2', '1', 'accepted'),
('10', '3', '2', 'accepted'),
('11', '3', '4', 'accepted'),
('12', '1', '5', 'accepted'),
('13', '4', '5', 'accepted'),
('14', '6', '5', 'accepted'),
('15', '5', '6', 'accepted'),
('16', '6', '2', 'accepted'),
('17', '2', '6', 'accepted');

PROFILES.SQL

CREATE TABLE `profiles` (
   `id` int(11) not null auto_increment,
   `email` varchar(40) not null,
   `password` varchar(15) not null,
   `fname` varchar(30) not null,
   `lname` varchar(30) not null,
   `sex` enum('male','female') not null,
   `profilpic` varchar(80),
   PRIMARY KEY (`id`),
   UNIQUE KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7;

INSERT INTO `profiles` (`id`, `email`, `password`, `fname`, `lname`, `sex`, `profilpic`) VALUES
('1', 'dexmundo@gmail.com', 'ruke1989', 'Desmond', 'Ohwofosirai', 'male', ''),
('2', 'dexmundo@facebook.com', 'facebook', 'Facebook', 'MCDonald', 'male', '1334782647r.jpg'),
('3', 'dexmundo@yuurok.com', 'yuurok', 'yuurok', 'dexmundo', 'female', '1334783309hills.jpg'),
('4', 'duru@network.ng', 'network', 'Duru', 'Chide', 'female', '1334785219hills.jpg'),
('5', 'alegbo@yuurok.com', 'alegbo', 'Alegbis', 'Yukoos', 'female', '1334785383r.jpg'),
('6', 'philoprayed@yahoo.com', 'yahoo', 'Ohwofosirai', 'yahoo', 'female', '1334787635ort_U844573.jpg');

find mutual friends, friend suggestion php, facebook script