PHP and MYSQL with Ajax Like and Unlike Facebook Style

Facebook like system is one of the best implementation in social network systems, may be in future based on like data Facebook going to launch semantic search engine. In this post I have explained how to implement like/unlike system database design and web implementation with PHP and jquery.

Database Design
To build the message like system, you have to create three tables such as Users,Messages and message_like. This following image generated by using Mysql Workbench tool.

Users Table

User table contains all the users registration details. 

CREATE TABLE `users` (
`uid` 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. 

Messages Table

This table contains user status messages data. Here uid_fk  is the FOREIGN KEY to REFERENCES users.uid

CREATE TABLE   `messages` (
`msg_id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`message` varchar(200) NOT NULL,
`uid_fk` int(11) NOT NULL,
`like_count` int(11) DEFAULT NULL,
`created` int(11) DEFAULT NULL,
FOREIGN KEY (uid_fk) REFERENCES users(uid)
);

Message Like Table

Contains all user message likes relation data. Here uid_fk is FOREIGN KEY to REFERENCES users.uid and msg_id_fk is FOREIGN KEY to REFERENCESmessages.msg_id

CREATE TABLE `message_like` (
`like_id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`msg_id_fk` int(11),
`uid_fk` int(11) NOT NULL,
`created` int(11) NOT NULL,
FOREIGN KEY (uid_fk) REFERENCES users(uid),
FOREIGN KEY (msg_id_fk) REFERENCES messages(msg_id)
);

HTML Code
Simple HTML code.

<div class="stbody">
<div class="stimg"><img src="userprofile.jpg"/></div>
<div class="sttext">
<b>Hemant Vishwakarma</b>: Status Message
<div class="sttime">48 seconds ago</div>

<div><a href="#" class="like" id="like103" title="Like" rel="Like">Like</a>
</div>

<div class='likeUsers' id="likes103"><span id="you103"><a href="#">You</a>,</span> <a href="#">Hemant</a>, <a href="#">Sharma</a> and 20 other friends like this.
</div>

</div>
</div>

PHP Code
To show Like or Unlike from message_like table based on message ID.

<div>

<?php

$msg_id='103'; //Message id
$uid='1'; //Message user id. 
$q=mysqli_query($db,"SELECT like_id FROM message_like WHERE uid_fk='$uid' and msg_id_fk='$msg_id' ");
if(mysqli_num_rows($q)==0)
{
echo '<a href="#" class="like'" id="like'.$msg_id.'" title="Unlike" rel="Unlike">Unlike</a>';
 }
else
{ 
echo '<a href="#" class="like" id="like'.$msg_id.'" title="Like" rel="Like">Like</a>';
} ?>

</div>

PHP Code
This code will display likes users details from users and message_like tables based on message ID. 

<?php
if($like_count>0)
{ 
$query=mysqli_query($db,"SELECT U.username,U.uid FROM message_like M, users U WHERE U.uid=M.uid_fk AND M.msg_id_fk='$msg_id' LIMIT 3");
?>
<div class='likeUsers' id="likes<?php echo $msg_id ?>">

<?php

$new_like_count=$like_count-3; 
while($row=mysqli_fetch_array($query,MYSQLI_ASSOC))
{
$like_uid=$row['uid']; 
$likeusername=$row['username']; 
if($like_uid==$uid)
{
echo '<span id="you'.$msg_id.'"><a href="'.$likeusername.'">You</a>,</span>';
}
else
{ 
echo '<a href="'.$likeusername.'">'.$likeusername.'</a>';
}  
}
echo 'and '.$new_like_count.' other friends like this'; 
?> 
</div>
<?php }
else { 
echo '<div class="likeUsers" id="elikes'.$msg_id.'"></div>';
} ?>

JavaScript
Contains javascipt code. $(".like").on('click',function(){}- like is the class name of the like/unlike anchor tag. Using $(this).attr("id") calling anchor tag ID value.

<script src="http://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>

<script type="text/javascript">

$('.like').on("click",function() 
{
var ID = $(this).attr("id");
var sid=ID.split("like"); 
var New_ID=sid[1];
var REL = $(this).attr("rel");
var URL='message_like_ajax.php';
var dataString = 'msg_id=' + New_ID +'&rel='+ REL;
$.ajax({
type: "POST",
url: URL,
data: dataString,
cache: false,
success: function(html){

if(REL=='Like')
{
$("#youlike"+New_ID).slideDown('slow').prepend("<span id='you"+New_ID+"'><a href='#'>You</a> like this.</span>.");
$("#likes"+New_ID).prepend("<span id='you"+New_ID+"'><a href='#'>You</a>, </span>");
$('#'+ID).html('Unlike').attr('rel', 'Unlike').attr('title', 'Unlike');
}
else
{
$("#youlike"+New_ID).slideUp('slow');
$("#you"+New_ID).remove();
$('#'+ID).attr('rel', 'Like').attr('title', 'Like').html('Like');
}

}
});

</script>

message_like_ajax.php
Contains PHP code to update Like or Unlike data. 

<?php
include 'db.php';
if(isSet($_POST['msg_id']) && isSet($_POST['rel']))
{
$msg_id=mysqli_real_escape_string($db,$_POST['msg_id']);
$rel=mysqli_real_escape_string($db,$_POST['rel']);
$uid=$session_uid; // User login session id
if($rel=='Like')
{
//---Like----
$q=mysqli_query($db,"SELECT like_id FROM message_like WHERE uid_fk='$uid' and msg_id_fk='$msg_id' ");
if(mysqli_num_rows($q)==0)
{
$query=mysqli_query($db,"INSERT INTO message_like (msg_id_fk,uid_fk) VALUES('$msg_id','$uid')");
$q=mysqli_query($db,"UPDATE messages SET like_count=like_count+1 WHERE msg_id='$msg_id'") ;
$g=mysqli_query($db,"SELECT like_count FROM messages WHERE msg_id='$msg_id'");
$d=mysqli_fetch_array($g,MYSQLI_ASSOC);
echo $d['like_count'];
}
}
else
{
//---Unlike----
$q=mysqli_query($db,"SELECT like_id FROM message_like WHERE uid_fk='$uid' and msg_id_fk='$msg_id' ");
if(mysqli_num_rows($q)>0)
{
$query=mysqli_query($db,"DELETE FROM message_like WHERE msg_id_fk='$msg_id' and uid_fk='$uid'");
$q=mysqli_query($db,"UPDATE messages SET like_count=like_count-1 WHERE msg_id='$msg_id'");
$g=mysqli_query($db,"SELECT like_count FROM messages WHERE msg_id='$msg_id'");
$d=mysqli_fetch_array($g,MYSQLI_ASSOC);
echo $d['like_count'];
}
}
}
?>

Final PHP Code
Complete Like/Unlike system. 

<?php 
$query=mysqli_query($db,"SELECT U.username, U.uid, M.msg_id, M.message FROM users U, messages M WHERE U.uid=M.uid_fk and U.uid='$sessions_uid'");
while($row=mysqli_fetch_array($query,MYSQLI_ASSOC))
{
$msg_id=$row['msg_id'];
$message=$row['message'];
$username=$row['username'];
$uid=$row['uid'];
?>

<div class="stbody">
<div class="stimg"><img src="userprofile.jpg"/></div>
<div class="sttext">
<b>Hemant Vishwakarma</b>: Status Message
<div class="sttime">48 seconds ago</div>

<div>
<?php
$q=mysqli_query($db,"SELECT like_id FROM message_like WHERE uid_fk='$uid' and msg_id_fk='$msg_id' ");
if(mysqli_num_rows($q)==0)
{
echo '<a href="#" class="like'" id="like.$msg_id.'" title="Unlike" rel="Unlike">Unlike</a>';
 } else { 
echo '<a href="#" class="like" id="like.$msg_id.'" title="Like" rel="Like">Like</a>';
} ?>
</div>

<?php if($like_count>0) { 
$query=mysqli_query($db,"SELECT U.username,U.uid FROM message_like M, users U WHERE U.uid=M.uid_fk AND M.msg_id_fk='$msg_id' LIMIT 3");
?>
<div class='likeUsers' id="likes<?php echo $msg_id ?>">
<?php
$new_like_count=$like_count-3; 
while($row=mysqli_fetch_array($query))
{
$like_uid=$row['uid']; 
$likeusername=$row['username']; 
if($like_uid==$uid)
{
echo '<span id="you'.$msg_id.'"><a href="'.$likeusername.'">You</a></span>';
}
else
{ 
echo '<a href="'.$likeusername.'">'.$likeusername.'</a>';
}  
}
echo 'and '.$new_like_count.' other friends like this';
?> 
</div>
<?php }
else { 
echo '<div class="likeUsers" id="elikes'.$msg_id.'"></div>';
} ?>
</div>
</div>
<php } ?>

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);
?>

PHP and MYSQL with Ajax  Like and Unlike Facebook Style