This post explains you how to design the Facebook Style message conversation system using PHP and MySQL. I have been working with messaging system, take a quick look at this post, how I have implemented database design tables and SQL queries.
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
);
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_id
CREATE 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)
);
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_id
CREATE 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)
);
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
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.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
PHP Code
Contains PHP code. Displaying username arun conversation results
<?php
$query= mysql_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=mysql_fetch_array($query))
{
$c_id=$row['cid'];
$user_id=$row['user_id'];
$username=$row['username'];
$email=$row['email'];
$cquery= mysql_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=mysql_fetch_array($cquery);
$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
PHP Code
Contains PHP code, displaying conversation c_id=2 reply results.
<?php
$query= mysql_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(mysql_error());
while($row=mysql_fetch_array($query))
{
$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')
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');
PHP Code Creating Conversation.
<?php
$user_one=mysql_real_escape_string($_GET['user_session']);
$user_two=mysql_real_escape_string($_GET['user_two']);
if($user_one!=$user_two)
{
$q= mysql_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 = mysql_query("INSERT INTO conversation (user_one,user_two,ip,time) VALUES ('$user_one','$user_two','$ip','$time')") or die(mysql_error());
$q=mysql_query("SELECT c_id FROM conversation WHERE user_one='$user_one' ORDER BY c_id DESC limit 1");
$v=mysql_fetch_array($q);
return $v['c_id'];
}
else
{
$v=mysql_fetch_array($q);
return $v['c_id'];
}
}
?>
PHP Code - Inserting Reply
<?php
$reply=mysql_real_escape_string($_POST['reply']);
$cid=mysql_real_escape_string($_POST['cid']);
$uid=mysql_real_escape_string($uid_session);
$time=time();
$ip=$_SERVER['REMOTE_ADDR'];
$q= mysql_query("INSERT INTO conversation_reply (user_id_fk,reply,ip,time,c_id_fk) VALUES ('$uid','$reply','$ip','$time','$cid')") or die(mysql_error());
?>
-By Parthiv Patel
Parthiv Patel
Bhaishri Info Solution
Sr. PHP Developer
Limdi Chowk, AT PO. Nar, Di. Anand
Nar, Gujarat
388150
India
pparthiv2412@gmail.com
7383343029
DOB: 12/24/1986
