This post explains you how to design the Facebook Style message conversation system using PHP and MySQL. I have been working with messaging system at labs.9lessons.info, take a quick look at this post, how I have implemented database design tables and SQL queries. Login at labs.9lessons.info and try this live demo.
Live DemoPrevious Tutorials:Database Design Create Tables and Relationships with SQL
Database Design
To build the message conversation system, you have to create three tables such as Users, Conversation and Conversation_Reply. This following image generated by using Mysql Workbench tool.
Users Table
User table contains all the users registration details. CREATE TABLE `users` (
`user_id` int NOT NULL PRIMARY KEY AUTO_INCREMENT ,
`username` varchar(25) NOT NULL UNIQUE,
`password` varchar(50) NOT NULL ,
`email` varchar(100) NOT NULL
);
`user_id` int NOT NULL PRIMARY KEY AUTO_INCREMENT ,
`username` varchar(25) NOT NULL UNIQUE,
`password` varchar(50) NOT NULL ,
`email` varchar(100) NOT NULL
);
Data will store in following way, here the password data encrypted with MD5 format.
Conversation Table
This table contains conversation relation data between registered users. Here user_one and user_two are FOREIGN KEY to REFERENCES users.user_idCREATE TABLE `conversation` (
`c_id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`user_one` int(11) NOT NULL,
`user_two` int(11) NOT NULL,
`ip` varchar(30) DEFAULT NULL,
`time` int(11) DEFAULT NULL,
FOREIGN KEY (user_one) REFERENCES users(user_id),
FOREIGN KEY (user_two) REFERENCES users(user_id)
);
`c_id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`user_one` int(11) NOT NULL,
`user_two` int(11) NOT NULL,
`ip` varchar(30) DEFAULT NULL,
`time` int(11) DEFAULT NULL,
FOREIGN KEY (user_one) REFERENCES users(user_id),
FOREIGN KEY (user_two) REFERENCES users(user_id)
);
Conversation Reply Table
Contains all user conversation replys data. Here user_id_fk is FOREIGN KEY to REFERENCES users.user_id and c_id_fk is FOREIGN KEY to REFERENCES conversation.c_idCREATE TABLE `conversation_reply` (
`cr_id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`reply` text,
`user_id_fk` int(11) NOT NULL,
`ip` varchar(30) NOT NULL,
`time` int(11) NOT NULL,
`c_id_fk` int(11) NOT NULL,
FOREIGN KEY (user_id_fk) REFERENCES users(user_id),
FOREIGN KEY (c_id_fk) REFERENCES conversation(c_id)
);
`cr_id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`reply` text,
`user_id_fk` int(11) NOT NULL,
`ip` varchar(30) NOT NULL,
`time` int(11) NOT NULL,
`c_id_fk` int(11) NOT NULL,
FOREIGN KEY (user_id_fk) REFERENCES users(user_id),
FOREIGN KEY (c_id_fk) REFERENCES conversation(c_id)
);
Conversation List
Data relations between users and conversation tables. Take a look at the following SQL statement users table object as U and conversation table object as C . Here user_one = '13' and user_two='13' refers to users table user_id value.
SELECT U.user_id,C.c_id,U.username,U.email
FROM users U,conversation C, conversation_reply R
WHERE
CASE
WHEN C.user_one = '13'
THEN C.user_two = U.user_id
WHEN C.user_two = '13'
THEN C.user_one= U.user_id
END
AND
C.c_id=R.c_id_fk
AND
(C.user_one ='13' OR C.user_two ='13') ORDER BY C.c_id DESC
FROM users U,conversation C, conversation_reply R
WHERE
CASE
WHEN C.user_one = '13'
THEN C.user_two = U.user_id
WHEN C.user_two = '13'
THEN C.user_one= U.user_id
END
AND
C.c_id=R.c_id_fk
AND
(C.user_one ='13' OR C.user_two ='13') ORDER BY C.c_id DESC
Conversation Last Message
Getting the reply results for conversation c_id='2' from conversation_reply table.
SELECT cr_id,time,reply
FROM conversation_reply
WHERE c_id_fk='2'
ORDER BY cr_id DESC LIMIT 1
FROM conversation_reply
WHERE c_id_fk='2'
ORDER BY cr_id DESC LIMIT 1
PHP Code
Contains PHP code. Displaying username arun conversation results
<?php
$query= mysqli_query("SELECT u.user_id,c.c_id,u.username,u.email
FROM conversation c, users u
WHERE CASE
WHEN c.user_one = '$user_one'
THEN c.user_two = u.user_id
WHEN u.user_two = '$user_one'
THEN c.user_one= u.user_id
END
AND (
c.user_one ='$user_one'
OR c.user_two ='$user_one'
)
Order by c.c_id DESC Limit 20") or die(mysql_error());
while($row=mysqli_fetch_array($query,MYSQLI_ASSOC))
{
$c_id=$row['cid'];
$user_id=$row['user_id'];
$username=$row['username'];
$email=$row['email'];
$cquery= mysqli_query("SELECT R.cr_id,R.time,R.reply FROM conversation_reply R WHERE R.c_id_fk='$c_id' ORDER BY R.cr_id DESC LIMIT 1") or die(mysql_error());
$crow=mysqli_fetch_array($cquery,MYSQLI_ASSOC);
$cr_id=$crow['cr_id'];
$reply=$crow['reply'];
$time=$crow['time'];
//HTML Output.
}
?>
$query= mysqli_query("SELECT u.user_id,c.c_id,u.username,u.email
FROM conversation c, users u
WHERE CASE
WHEN c.user_one = '$user_one'
THEN c.user_two = u.user_id
WHEN u.user_two = '$user_one'
THEN c.user_one= u.user_id
END
AND (
c.user_one ='$user_one'
OR c.user_two ='$user_one'
)
Order by c.c_id DESC Limit 20") or die(mysql_error());
while($row=mysqli_fetch_array($query,MYSQLI_ASSOC))
{
$c_id=$row['cid'];
$user_id=$row['user_id'];
$username=$row['username'];
$email=$row['email'];
$cquery= mysqli_query("SELECT R.cr_id,R.time,R.reply FROM conversation_reply R WHERE R.c_id_fk='$c_id' ORDER BY R.cr_id DESC LIMIT 1") or die(mysql_error());
$crow=mysqli_fetch_array($cquery,MYSQLI_ASSOC);
$cr_id=$crow['cr_id'];
$reply=$crow['reply'];
$time=$crow['time'];
//HTML Output.
}
?>
Conversation Updates
Data relations between users and conversation_reply tables. The following SQL statement users table object as U and conversation_reply table object as R . Here c_id_fk = '2' refers to convesation table c_id value.
SELECT R.cr_id,R.time,R.reply,U.user_id,U.username,U.email
FROM users U, conversation_reply R
WHERE R.user_id_fk=U.user_id AND R.c_id_fk='2'
ORDER BY R.cr_id DESC
FROM users U, conversation_reply R
WHERE R.user_id_fk=U.user_id AND R.c_id_fk='2'
ORDER BY R.cr_id DESC
PHP Code
Contains PHP code, displaying conversation c_id=2 reply results.
<?php
$query= mysqli_query("SELECT R.cr_id,R.time,R.reply,U.user_id,U.username,U.email FROM users U, conversation_reply R WHERE R.user_id_fk=U.user_id and R.c_id_fk='$c_id' ORDER BY R.cr_id ASC LIMIT 20") or die(mysqli_error());
while($row=mysqli_fetch_array($query,MYSQLI_ASSOC))
{
$cr_id=$row['cr_id'];
$time=$row['time'];
$reply=$row['reply'];
$user_id=$row['user_id'];
$username=$row['username'];
$email=$row['email'];
//HTML Output
}
?>
$query= mysqli_query("SELECT R.cr_id,R.time,R.reply,U.user_id,U.username,U.email FROM users U, conversation_reply R WHERE R.user_id_fk=U.user_id and R.c_id_fk='$c_id' ORDER BY R.cr_id ASC LIMIT 20") or die(mysqli_error());
while($row=mysqli_fetch_array($query,MYSQLI_ASSOC))
{
$cr_id=$row['cr_id'];
$time=$row['time'];
$reply=$row['reply'];
$user_id=$row['user_id'];
$username=$row['username'];
$email=$row['email'];
//HTML Output
}
?>
Conversation Check
Following query will verify conversation already exists or not.
SELECT c_id
FROM conversation
WHERE
(user_one='13' AND user_two='16')
OR
(user_one='16' AND user_two='13')
FROM conversation
WHERE
(user_one='13' AND user_two='16')
OR
(user_one='16' AND user_two='13')
Creating Conversation
//Creating Conversation
INSERT INTO conversation
(user_one,user_two,ip,time)
VALUES
('13','16','122.3.3.7','122.3.3.7');
//Conversation Reply Insert
INSERT INTO conversation_reply
(user_id_fk,reply,ip,time,c_id_fk)
VALUES
('13,'How are you','122.3.3.7','122.3.3.7','2');
INSERT INTO conversation
(user_one,user_two,ip,time)
VALUES
('13','16','122.3.3.7','122.3.3.7');
//Conversation Reply Insert
INSERT INTO conversation_reply
(user_id_fk,reply,ip,time,c_id_fk)
VALUES
('13,'How are you','122.3.3.7','122.3.3.7','2');
PHP Code Creating Conversation.
<?php
$user_one=mysqli_real_escape_string($db,$_GET['user_session']);
$user_two=mysqli_real_escape_string($db,$_GET['user_two']);
if($user_one!=$user_two)
{
$q= mysqli_query("SELECT c_id FROM conversation WHERE (user_one='$user_one' and user_two='$user_two') or (user_one='$user_two' and user_two='$user_one') ") or die(mysql_error());
$time=time();
$ip=$_SERVER['REMOTE_ADDR'];
if(mysql_num_rows($q)==0)
{
$query = mysqli_query("INSERT INTO conversation (user_one,user_two,ip,time) VALUES ('$user_one','$user_two','$ip','$time')") or die(mysql_error());
$q=mysqli_query("SELECT c_id FROM conversation WHERE user_one='$user_one' ORDER BY c_id DESC limit 1");
$v=mysql_fetch_array($q,MYSQLI_ASSOC);
return $v['c_id'];
}
else
{
$v=mysqli_fetch_array($q,MYSQLI_ASSOC);
return $v['c_id'];
}
}
?>
$user_one=mysqli_real_escape_string($db,$_GET['user_session']);
$user_two=mysqli_real_escape_string($db,$_GET['user_two']);
if($user_one!=$user_two)
{
$q= mysqli_query("SELECT c_id FROM conversation WHERE (user_one='$user_one' and user_two='$user_two') or (user_one='$user_two' and user_two='$user_one') ") or die(mysql_error());
$time=time();
$ip=$_SERVER['REMOTE_ADDR'];
if(mysql_num_rows($q)==0)
{
$query = mysqli_query("INSERT INTO conversation (user_one,user_two,ip,time) VALUES ('$user_one','$user_two','$ip','$time')") or die(mysql_error());
$q=mysqli_query("SELECT c_id FROM conversation WHERE user_one='$user_one' ORDER BY c_id DESC limit 1");
$v=mysql_fetch_array($q,MYSQLI_ASSOC);
return $v['c_id'];
}
else
{
$v=mysqli_fetch_array($q,MYSQLI_ASSOC);
return $v['c_id'];
}
}
?>
PHP Code - Inserting Reply
<?php
$reply=mysqli_real_escape_string($db,$_POST['reply']);
$cid=mysqli_real_escape_string($db,$_POST['cid']);
$uid=mysqli_real_escape_string($db,$uid_session);
$time=time();
$ip=$_SERVER['REMOTE_ADDR'];
$q= mysqli_query("INSERT INTO conversation_reply (user_id_fk,reply,ip,time,c_id_fk) VALUES ('$uid','$reply','$ip','$time','$cid')") or die(mysqli_error());
?>
$reply=mysqli_real_escape_string($db,$_POST['reply']);
$cid=mysqli_real_escape_string($db,$_POST['cid']);
$uid=mysqli_real_escape_string($db,$uid_session);
$time=time();
$ip=$_SERVER['REMOTE_ADDR'];
$q= mysqli_query("INSERT INTO conversation_reply (user_id_fk,reply,ip,time,c_id_fk) VALUES ('$uid','$reply','$ip','$time','$cid')") or die(mysqli_error());
?>
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');
$db = mysqli_connect(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE);
?>
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'username');
define('DB_PASSWORD', 'password');
define('DB_DATABASE', 'database');
$db = mysqli_connect(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE);
?>
Hi sir nice post...but I have one question is it ok to loop a mysql query?
ReplyDeleteNice post, but how to manage nested level reply on a single post.
Deletenice
ReplyDeleteI have been meaning to do something like this, and go even further by using APE(ajax push engine) to make have a full chat system implementation. Thanks for the post.
ReplyDeleteAwesome!!
ReplyDeletethx!
ReplyDeleteAwesome....
ReplyDeletevery nice ,,
ReplyDeletevery helpful thx!
ReplyDeleteNice dear ! !
ReplyDeleteCool post..informative
ReplyDeleteInteresting, but getting the basics wrong like using mysql_query and md5() as "encryption" for passwords, makes me question everything you've written...
ReplyDeleteThis is awesome!
ReplyDeleteawesomely done.. Thnkz bro..
ReplyDeletegud tutorial
ReplyDeleteGreat post!!!
ReplyDeletethis is coool...thenx !!!
ReplyDeletevery well
ReplyDeleteHey. Why are you not using mysqli ? It's safer
ReplyDeleteThis is pretty cool...Thanks a lot.
ReplyDeletehow can we add multiple users in a conversation?
You don't need three Table
ReplyDeletejust tow table work fine
Congratulations, but if I wanted to have a conversation with multiple users as facebook. ;)
ReplyDeleteI truly love your design and style of blog. I added this to my favorites blog checklist and will probably be coming again soon.
ReplyDeletewhy do we have 2 users id in the conversation table?
ReplyDeleteis it better to have just 1 row with the conversation id and user id
+
DeleteNice article...
ReplyDeleteConversation List Query Update.
ReplyDeleteu always share good article, i like it (y) :)
ReplyDeletethanks
superb design. i like it too much. it's look like a new revolution in message system.
ReplyDeleteTussi Great Ho!!!! :) ..Thanks :)
ReplyDeletenice design and a superb idea..wll done.
ReplyDeleteNice article...
ReplyDeleteGreat post!!!
ReplyDeleteExcellente! :D a practicar!!!! thanks a lot!!
ReplyDeletegood!, interesting, but how it be to add more users to convertation?
ReplyDeletenice one
ReplyDeleteExcellent Post. helped a lot
ReplyDeleteNyc post...
ReplyDeletenice post.
ReplyDeleteBut this chat script not a real time like a facebook chat system.
What tool you are using for designing a MYSQL database schema?
ReplyDeletehow i can delete my conversation withou deleting the recipient's messages?
ReplyDeleteIf i delete mine then the messages will be also deleted from the receiver inbox.
The post is fine but i have a question. when i Displaying username arun conversation results the sql query is not showing the correct username. how can i solve this?
ReplyDeletenice work, but it isn't like facebook chat , you should have friend list and refresh it from time to time to check which one is offline or online now , and the message body should be refreshed every time to make it real chat
ReplyDeleteBest.
ReplyDeletevery good
ReplyDeleteDont really like the fact that a conversation is only between to people. In my oppinion your conversations table should only have contained one column with relation to a users, so that you could add as many users to a conversation as youd like.
ReplyDeleteGreat sir, thanx for providing such a great Knowledge...Thanx....U have bookmark your site..So impressed to you..
ReplyDeleteyou rock dude !!
ReplyDeleteWhat if i delete one of the messages? If i delete my message, it will be non-visible for other user too. Isn't this a problem?
ReplyDeleteWhat if user_two deletes the conversation? then it will be deleted for user_one to?
ReplyDeletei hava a doubt on Conversation Reply Table
ReplyDeletewhere user_id_fk refer to which attribute ?(user-one and user two)
no it is refer to current user who send reply.
DeleteIt's dont know which content belong to user_on , user_two
DeleteExcellent Post. easy to understand and easy to implement..
ReplyDeleteGK
hey can u pls give a link to download files for this post?it would be a grt help...
ReplyDeletegreat post, what happen with big data? query will slow? are you check it?
ReplyDeleteAwesome tutorial... I really don't understand what makes some people here question about the security ?? are they not aware that mysql 5 + has an integrated system which quells all the possibilities of hacking, including injections.. it's totally safe and secure! unless you use sessions instead of passing variables directly as a link
ReplyDeleteFacebook Style Messaging System Database Design with jsp... plzzzzz help
ReplyDeleteSo many things are wrong with this. Mysql extensions, no deletions, no seen/unseen tracking, complex query required to find conversations with recent replies.
ReplyDeleteThis is certainly not Facebook method. It won't offer enough features or scale properly if u decide to implement them with the current schema.
You can fix many of your headaches by enforcing that user_one is always smaller than user_two.
Thank your for this post.
ReplyDeleteI do however would like to know, how would you manage reply (or conversation) being viewed (or not yet viewed) by the person receiving the message.
This database model will not work with large number of messages.
ReplyDeleteLook at this code:
SELECT cr_id,time,reply
FROM conversation_reply
WHERE c_id_fk='2'
ORDER BY cr_id DESC LIMIT 1
c_id_fk is not indexed. so It will look for the whole table. A table can contain millions of messages. so millions of rows. This complete design is bad.
This conversation not like facebook, facebook conversation table structure is different check once in facebook , user1 deletes all conversation b/w user1 and user2 , if user2 can see conversation but user1 cannot see conversation but in your program anyonce can has the authority to delete msg , we do not know who is deleting message because anyone can delete so information may lost
ReplyDeleteThe legendary Srinivas delivers again, you're a genius!!
ReplyDeleteTo all the people complaining, Srinivas has taken the time out to deliver this tutorial as guide line on how a messaging system works. It's up to you to optimise and secure it. Goodness sakes at least do some work. Great tutorial!
ReplyDeletehow can you post these conversation,so that the other user don't have to refresh the page to see it!!!!
ReplyDeleteHi Srinivas. Thanks a zillion. To all others who are questioning/complaining, why don't you put an effort into resolving your own queries yourself ?
ReplyDeleteNice tutorial, It goes inline with what I've developed so far. One thing you are missing is a status for the conversation (read/unread/deleted), a status that needs to be set for each user.
ReplyDeleteHi there, I have read your confirmation. The big problem is one of them delete their conversation then the conversation will be non-visible for other user. We can add 2 columns are :
ReplyDelete- user_on_status TINYINT(1)
- user_two_status TINYINT(1)
With this status equal 0 you can control the conversation. If user_one delete , We will set user_on_status = 0 instead of delete permanent.
gr8
ReplyDeleteConsider this example, if the person "A" writes a message to the "B" person, and the person "A" deletes the message, the "B" person will also erase the message, as would be done in these cases
ReplyDeletevery nice.
ReplyDeleteplease help me to add online chat in final year project on local host em confused which ip will use and how
ReplyDeleteThanks!
ReplyDeleteI've developed it. This script is getting of the users message area only. Incoming messages can not see on your profile page ( Like Facebook ). You need to go to the messages.php page to see the messages. But i like srinivas database design.
ReplyDeleteIf i am going to make this project from where to start and how to start...please help me with some tutorial videos.
ReplyDeleteits very helpful.. thanks a lot
ReplyDeleteSir if you want to count unread message can you help us plz!!!!
ReplyDeleteCurious about how u handle cancellations in this scenario...If A remove a message, B won't be able to see it anymore. Everybody should be able to delete messages in their "inbox" without permit the interlocultor to know about this action ;) Start from real situations instead of build db schemes drawing tables
ReplyDeleteHow check Unread Messages ?
ReplyDeleteif i test the php for showing messages evreything stays blank?
ReplyDeletecan somebody help with the html is need to use?
any body help to find all messages sent by other user of status 0 when user 2 want to show his replies.
ReplyDeleteOne of the best tutorials about messaging, saved my ass and time :D
ReplyDeletehi
ReplyDeletehow to check if the message is new. like new message notification
Nice tutorial. I only wanted was database structure.
ReplyDeleteNice
ReplyDelete;..;.;.;;.;.;
ReplyDeletei think its not right. what happened if user1 delete his conversation ?
ReplyDeleteI need to download the code
ReplyDeletevery Great work keep it up and plz sir tell me how to store link of videos in data base and retrieve and play video in webpage when required!
ReplyDeleteCreate complete CSS and HTML pages.
ReplyDeleteBy this anybody with basic knowledge can understand this script. GREAT JOB DUDE KEEP GOOD WORK.
How will you then figure out the read/unread status in above implementation
ReplyDeleteHow would you take one long list of messages and insert it into a database like this?
ReplyDeleterarely a blog helps out, but this one did..thanks!
ReplyDeletehow we can download source code..
ReplyDeletevery god post, keep it up
ReplyDeletebut how to manage individual delete of conversation and conversation replies for individual user ??
ReplyDeleteexcellent post , easily understand schema
ReplyDeletegood job,,, i like it,,
ReplyDeleteHello,
ReplyDeleteany examples on 'group chat'?
hi its pretty cool but I need a little twisted thing I need one to many chat as well is there any link or help?
ReplyDeletethanks
Hi Sir
ReplyDeleteI was astonishing looking into your blog. Its really good..
But according to this ERD conservation possible between TWO users. So please help me ERD how to design DB for chat in group ?
I hv developed RTC system having same ERD approach but I am looking for ERD that support :
Chat b/w 2 users
Chat among groups
Waiting for your kind answer
Cheers :)
Hi
ReplyDeleteThe ERD work perfect for 1 to 1 chat.. It will be great to have an example on Group Chat.. Or Any Idea ??
Waiting for kind reply..
Thanks :)
Can You please attach the source code to download
ReplyDeleteHow can we maintain deleted the conversation ?
ReplyDeletevery very thanks
ReplyDeletehow to create conversation group?
Can I get the full source code of the project please
ReplyDeleteCan we have full source code of the project please?
ReplyDeleteThere is possibility to change avatar image ?
ReplyDeleteHave a user level ?
Thnaks
1. Why is time an int(11), should not that be a timestamp?
ReplyDelete2. What value should we insert in the ip field of the table conversation?
IP of user_one or IP of user_two?
SELECT U.user_id,C.c_id,U.username,U.email
ReplyDeleteFROM users U,conversation C, conversation_reply R
WHERE
CASE
WHEN C.user_one = '13'
THEN C.user_two = U.user_id
WHEN C.user_two = '13'
THEN C.user_one= U.user_id
END
AND
C.c_id=R.c_id_fk
AND
(C.user_one ='13' OR C.user_two ='13') ORDER BY C.c_id DESC
This query shows duplication conversation. why??
nice sir
ReplyDeleteWOW ! Awesome
ReplyDeleteThis post is very very fresh....
ReplyDeleteSir you just ultimate, awesome work.
ReplyDeleteThis is awesome buddy...
ReplyDeletethank you my friend :) this is awesome :)
ReplyDeleteThanks, great tutorial! Ignore those who are complaining they don't know anything, this is basic tutorial so it wont be exactly look like facebook but gives info on how it goes, like the concept. Thanks again sir
ReplyDeleteThank you
DeleteHie I'm trying to built a system which use the same concept and I'm having trouble designing the database.can I ask when you put user_1 and user_2 is that not against first normal form
ReplyDeleteThis comment has been removed by a blog administrator.
ReplyDelete