Facebook Style Friend Request System Database Design.
Wall Script
Wall Script
Monday, March 17, 2014

Facebook Style Friend Request System Database Design.

I received few tutorial requests from my readers that asked to me, how to design Facebook style friend request system using MySQL. I had posted few tutorials about twitter style friend follow and follower concept designs, Facebook friend system is involved with few pending steps such as sending a request and accepting a request. Here I have discussed how to design a database and implementation of SQL queries, hope this post will help you to understand the friend system.

Friend Request System Database Design.


Previous Tutorials:Facebook Style Messaging System Database Design.

Database Design
To build the friend request system, you have to create three tables such as Users, Updates and Friends.

Friend Request System Database Design.

Users Table
User table contains all the users registration details.
CREATE TABLE `users` (
`user_id` INT(11) NOT NULL AUTO_INCREMENT ,
`username` VARCHAR(45) ,
`password` VARCHAR(100) ,
`email` VARCHAR(45) ,
`friend_count` INT(11) ,
`profile_pic` VARCHAR(150),
PRIMARY KEY (`user_id`));

Friend Request System Database Design.

Data will store in following way, here the password data encrypted with MD5 format.

Updates Table
This table contains user status updates data. Here user_id_fk  is the FOREIGN KEY to REFERENCES users.user_id
CREATE TABLE `updates` (
`update_id` INT(11) AUTO_INCREMENT ,
`update` VARCHAR(45),
`user_id_fk` VARCHAR(45),
`created` INT(11) ,
`ip` VARCHAR(45),
PRIMARY KEY (`update_id`),
FOREIGN KEY (user_id_fk) REFERENCES users(user_id));

Friend Request System Database Design.

Friends Table
This table contains user friends relation data. Here friend_one and friend_two are the FOREIGN KEYs to REFERENCES users.user_id
CREATE TABLE `friends` (
`friend_one` INT(11) ,
`friend_two` INT(11) ,
`status` ENUM('0','1','2') DEFAULT '0',
PRIMARY KEY (`friend_one`,`friend_two`),
FOREIGN KEY (friend_one) REFERENCES users(user_id),
FOREIGN KEY (friend_two) REFERENCES users(user_id));

Here status 0, 1 and 2 values references to Pending Friend Request, Confirm Friend Request and You.

Friend Request System Database Design.

User Registration
This statement is required for user registration, here status 2 represents to you. This helps while retrieving friend updates along with your updates.
INSERT INTO friends
(friend_one,friend_two,status)
VALUES
('$user_id','$user_id','2');

Add Friend
Use the following insert statement for adding a friend.
INSERT INTO friends
(friend_one,friend_two)
VALUES
('$user_id','$friend_id');

Confirm Friend Request
Here confirming the friend request, updating the status 0 to 1
UPDATE friends
SET status="1"
WHERE
(friend_one="$user_id" OR friend_two="$user_id")
AND
(friend_one="$friend_id" OR friend_two="$friend_id");

Checking Friend
Here friend_one represents the friend request owner.
SELECT 'friend_one','friend_two','status' FROM friends
WHERE
(friend_one="$user_id" OR friend_two="$user_id")
AND
(friend_one="$friend_id" OR friend_two="$friend_id")

PHP Code
This contains simple PHP code, this helps you to display the proper friend request related buttons.
<?php
include 'db.php';
$user_id=user session value;
$friend_id=frined id value;
$result=mysqli_query($connection,"SELECT 'friend_one','friend_two','status' FROM friends WHERE (friend_one="$user_id" OR friend_two="$user_id") AND (friend_one="$friend_id" OR friend_two="$friend_id")");
$row=mysqli_fetch_array($result,MYSQLI_ASSOC);

if($row['friend_one']=='$user_id' && $row['status']=='0')
{
Friend Request System Database Design.
}
else
{
Friend Request System Database Design.
}
?>

Friends Updates List
Data relations between users, friends and updates tables for friend feed results. The following SQL statement users table object as U, updates table object as D and friends table object as F . Here $user_id reference to user session value and $friend_id is reference to friend user row id value.
SELECT U.username, U.email, D.update_id, D.update, D.created
FROM users U, updates D, friends F
WHERE
D.user_id_fk = U.user_id
AND

CASE
WHEN F.friend_one = '$user_id'
THEN F.friend_two = D.user_id_fk
WHEN F.friend_two= '$user_id'
THEN F.friend_one= D.user_id_fk
END

AND
F.status > '0'
ORDER BY D.update_id DESC;

Friend Request System Database Design.

Friends List
Data relations between users and friends tables for displaying user friends. Take a look at the following SQL statement users table object as U and friends table object as F . Here user_id is the login user session value.
SELECT F.status, U.username, U.email
FROM users U, friends F
WHERE
CASE

WHEN F.friend_one = '$user_id'
THEN F.friend_two = U.user_id
WHEN F.friend_two= '$user_id'
THEN F.friend_one= U.user_id
END

AND
F.status='1';

Friend Request System Database Design.

db.php
Database configuration file, modify username, password and database values.
<?php
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'username');
define('DB_PASSWORD', 'password');
define('DB_DATABASE', 'database');
$connection = mysqli_connect(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE);
?>
web notification

84 comments:

  1. awsome tutorial sir!!
    i could understand this in easy manner!

    ReplyDelete
  2. Awsome tutorial Srinivas :)
    What is your opinion of object oriented syntax versus procedural style in mysqli ?

    ReplyDelete
  3. i thing here should be a demo! please

    ReplyDelete
  4. im always looking forwad to ur new Tuts, greate work mane!!!...just wondering if facebook wont hv a problem if they found out u giving tutorials to imitate their site..

    ReplyDelete
  5. sir plz help me mujhe ek box me user ki upload ki gai photos show karna hai magr kese
    for ex jese facebook me profile page me photos ka box hai wese

    ReplyDelete
  6. best tut but i thing video tut add u r awsome

    ReplyDelete
  7. I really needed for one of my application, I'll try to modify it lol anyways thanks buddy

    ReplyDelete
  8. thanks for the post...

    ReplyDelete
  9. Awesome tutorial (y)
    I think in 'Confirm Friend Request' function we need to update the `friend_count` in users table too

    ReplyDelete
  10. Very nice information. Thank you so much for sharing.

    ReplyDelete
  11. That awsome tutorial Bro. This is first post i read you blog.

    ReplyDelete
  12. Really interesting, I always found you informative. Thanks for sharing such a great post. Keep blogging!

    ReplyDelete
  13. best if come with demo.

    ReplyDelete
  14. Thanks for sharing such an intellectual post!!! really nice coding!!!

    ReplyDelete
  15. how to get mutual friends of a specific user plz plz tell the exect sql command/query

    ReplyDelete
  16. Thats awesome.. I was always curious how to design such system in php... never got any success ... Thanks Keep it up

    ReplyDelete
  17. Awesome tut... thanks for sharing...

    ReplyDelete
  18. Thanks for the tut!

    When "checking a friend", what if the current user has already confirmed the friend's request (i.e. the status has been set to 1), as it's written, wouldn't your logic errantly display the "confirm request" box?

    Regarding the sql for confirming a friend request, how could the user ever be in a position to confirm a friend request as friend_one? Your SQL suggest he could.

    Was that your intension to include your OWN updates in the "Friends Updates list", because isn't that what "F.status > '0'" would allow for?

    I'm thinking the app shouldn't allow for the same pair of friends to have multiple rows in the friends table, right? So, the following shouldn't be possible, But I didn't see where your script was checking for that when adding a new record.

    friend_one = 123; friend_two = 456
    friend_one = 456; friend_two = 123

    ReplyDelete
  19. Thank You sir....!! awesome work

    ReplyDelete
  20. This is Amazing Tutorial... Thanks...

    ReplyDelete
  21. script is awesome
    but we can do directly
    through make a relationship table with friends..
    hemanjosko

    ReplyDelete
  22. awesome man... nice and easy tutorial

    ReplyDelete
  23. Thanks for the tutorial! Good job man.

    ReplyDelete
  24. Great article, it is nice to read your article..

    ReplyDelete

  25. Awesome post, thanks for sharing this post..

    ReplyDelete
  26. why can't I get this to work ..... I cannot create an updates table it comes up with an error !!! HELP

    ReplyDelete
  27. Thank you. but I got a doubt. As db grows bigger, this is really going to hit the performance right?

    ReplyDelete
  28. Plz sir my id facebook withe twitter connetid on u site

    ReplyDelete
  29. Great Job Srinivas. Very much appreciated for this article, As this made my task more easy
    Thank you for posting

    ReplyDelete
  30. i am not understand why you using friend_one and friend_two if i got 10 friend request than what gonna do any idea?

    ReplyDelete
  31. I'm stucked with update table.. I need help!

    ReplyDelete
  32. why can't I get this to work ..... I cannot create an updates table it comes up with an error !!! HELP

    ReplyDelete
  33. This is an awesome tutorial! How would you go about getting a list of users for a person that are friends yet.?

    ReplyDelete
  34. Please, how can i create the request button. I don't see where you include it in your tutorial. Please help!

    ReplyDelete
  35. It's Great Awesome Thing!@

    ReplyDelete
  36. i got this error: #1215 - "Cannot add foreign key constraint" for `updates` able

    ReplyDelete
  37. very nice tutorial . I just want one thing to display recommended fiends like facebook . Friends from friends of friends like this .

    ReplyDelete
  38. Hello Sir,
    Please help me...
    how to get mutual friends of a specific user with status.

    ReplyDelete
  39. sir, i have a doubt in friends table if some one added user 1
    for example :
    ===========================================
    friend_one | friend_two | status | created
    ===========================================
    4 | 1 | 2 | Date

    how to retrieve all confirm friends from friend table including my record 4 1 2 Date

    ReplyDelete
  40. Hello Users need some change in update table then it work fine

    put the `user_id_fk` datatype to int then it work

    CREATE TABLE `updates`
    (
    `update_id` INT(11) AUTO_INCREMENT,
    `updates` VARCHAR(45),
    `user_id_fk` INT(11),
    `created` INT(11),
    `ip` VARCHAR(45),
    PRIMARY KEY (update_id),
    FOREIGN KEY (user_id_fk) REFERENCES users(user_id)
    )

    ReplyDelete
  41. Need to Change in `updates` table

    CREATE TABLE `updates`
    (
    `update_id` INT(11) AUTO_INCREMENT,
    `updates` VARCHAR(45),
    `user_id_fk` INT(11),
    `created` INT(11),
    `ip` VARCHAR(45),
    PRIMARY KEY (update_id),
    FOREIGN KEY (user_id_fk) REFERENCES users(user_id)
    )

    ReplyDelete
  42. tu codigo junto con otro me ayudo demaciado muchas gracias amigo

    ReplyDelete
  43. in my users profile i have a extra column named flag whose value=1 when user online and 0 otherwise except this all other part is same as the database tables in this article .can somebody plz tell me how can i get a list of online friends .thanks in advance

    ReplyDelete
  44. Sir, plz give me this frnd request script!

    ReplyDelete
  45. Should friend_one and friend_two both be indexed for searches to be faster?

    ReplyDelete
  46. any one please can describe the status 0, 1, 2

    ReplyDelete
    Replies
    1. Here status 0, 1 and 2 values references to Pending Friend Request, Confirm Friend Request and You.

      Delete
  47. SELECT U.firstName, U.lastName, U.currentJobTitle, U.picpath, D.update_id, D.update, D.dateTime
    FROM user U, updates D, friends F
    WHERE
    D.user_id_fk = U.userId
    AND

    CASE
    WHEN F.friend_one = '$user_id'
    THEN F.friend_two = D.user_id_fk
    WHEN F.friend_two= '$user_id'
    THEN F.friend_one= D.user_id_fk
    END

    AND
    F.status > '0'
    ORDER BY D.update_id DESC"

    all friends status is showing but my status is not showing

    please check anyone can help me

    ReplyDelete
  48. How does facebook listen for newly made requests so they can update the client view without having to wait for the user to refresh the page?

    ReplyDelete
  49. thanks!!! I've taken a couple of ideas from here :)

    ReplyDelete
  50. nice explanation. but need tutorial with example with values so that can understand easily.

    ReplyDelete
  51. can you provide code for android using Mysql database??

    ReplyDelete

mailxengine Youtueb channel
Make in India
X