article

Saturday, April 17, 2021

Like Unlike using PHP Mysqli and jQuery AJAX

Like Unlike using PHP Mysqli and jQuery AJAX

CREATE TABLE `posts` (
  `id` int(11) NOT NULL,
  `title` varchar(100) NOT NULL,
  `content` text NOT NULL,
  `link` varchar(255) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `posts` (`id`, `title`, `content`, `link`, `timestamp`) VALUES
(4, 'What is AngularJS', 'AngularJS is a JavaScript MVC framework  developed by Google that lets you build well structured, easily testable,  declarative and maintainable front-end applications which provides solutions to  standard infrastructure concerns.', 'link-5', '2021-03-20 16:00:00'),
(5, 'What is MongoDB', 'It is a quick tutorial on MongoDB and how you can install it on your Windows OS. We will also learn some basic commands in MongoDB for example, creating and dropping a Database, Creation of a collection and some more operations related to the collection.', 'link-6', '2021-03-21 16:00:00'),
(6, 'Python Flask Load content Dynamically in Bootstrap Modal with Jquery AJAX and Mysqldb', 'Python Flask Load content Dynamically in Bootstrap Modal with Jquery AJAX and Mysqldb', 'link-6', '2021-03-20 16:00:00'),
(7, 'AutoComplete Textbox with Image using jQuery Ajax PHP Mysql and JqueryUI', 'AutoComplete Textbox with Image using jQuery Ajax PHP Mysql and JqueryUI', 'link-7', '2021-03-14 16:00:00'),
(8, 'PHP Mysql Registration Form Validation using jqBootstrapValidation with Jquery Ajax', 'PHP Mysql Registration Form Validation using jqBootstrapValidation with Jquery Ajax', 'link-8', '2021-03-20 16:00:00'),
(9, 'Python Flask Registration Form Validation using jqBootstrapValidation with Jquery Ajax and Mysql', 'Python Flask Registration Form Validation using jqBootstrapValidation with Jquery Ajax and Mysql', 'link-9', '2021-03-19 16:00:00'),
(10, 'Displaying Popups data on mouse hover using Jquery Ajax and PHP Mysql database', 'Displaying Popups data on mouse hover using Jquery Ajax and PHP Mysql database', 'link-10', '2021-03-15 16:00:00'),
(11, 'Displaying Popups data on mouse hover using Jquery Ajax and Python Flask Mysql database', 'Displaying Popups data on mouse hover using Jquery Ajax and Python Flask Mysql database', 'link-11', '2021-03-14 16:00:00');


ALTER TABLE `posts`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `posts`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;

CREATE TABLE `like_unlike` (
  `id` int(11) NOT NULL,
  `userid` int(11) NOT NULL,
  `postid` int(11) NOT NULL,
  `type` int(2) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `like_unlike`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `like_unlike`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
index.php
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
//index.php
<!doctype html>
<html>
<head>
<title>Like Unlike using PHP Mysqli and jQuery AJAX</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />   
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
<script type='text/javascript'>
$(document).ready(function(){
    $(".like, .unlike").click(function(){
        var id = this.id;  
        var split_id = id.split("_");
        var text = split_id[0];
        var postid = split_id[1];
        var type = 0;
        if(text == "like"){
            type = 1;
        }else{
            type = 0;
        }
        $.ajax({
            url: 'likeunlike.php',
            type: 'post',
            data: {postid:postid,type:type},
            dataType: 'json',
            success: function(data){
                var likes = data['likes'];
                var unlikes = data['unlikes'];
                $("#likes_"+postid).text(likes);      
                $("#unlikes_"+postid).text(unlikes);  
                if(type == 1){
                    $("#like_"+postid).css("color","#ffa449");
                    $("#unlike_"+postid).css("color","lightseagreen");
                }
                if(type == 0){
                    $("#unlike_"+postid).css("color","#ffa449");
                    $("#like_"+postid).css("color","lightseagreen");
                }
            }
        });
    });
});
</script>
</head>
<body >
<div class="container" >
    <div class="row" style="padding:50px;">
        <p><h1>Like Unlike using PHP Mysqli and jQuery AJAX</h1></p>
        <div class="content">
            <?php
            include "dbcon.php";
                $userid = 5;
                $query = "SELECT * FROM posts";
                $result = mysqli_query($conn,$query);
                while($row = mysqli_fetch_array($result)){
                    $postid = $row['id'];
                    $title = $row['title'];
                    $conntent = $row['content'];
                    $type = -1;
 
                    $status_query = "SELECT count(*) as cntStatus,type FROM like_unlike WHERE userid=".$userid." and postid=".$postid;
                    $status_result = mysqli_query($conn,$status_query);
                    $status_row = mysqli_fetch_array($status_result);
                    $count_status = $status_row['cntStatus'];
                    if($count_status > 0){
                        $type = $status_row['type'];
                    }
 
                    $like_query = "SELECT COUNT(*) AS cntLikes FROM like_unlike WHERE type=1 and postid=".$postid;
                    $like_result = mysqli_query($conn,$like_query);
                    $like_row = mysqli_fetch_array($like_result);
                    $total_likes = $like_row['cntLikes'];
 
                    $unlike_query = "SELECT COUNT(*) AS cntUnlikes FROM like_unlike WHERE type=0 and postid=".$postid;
                    $unlike_result = mysqli_query($conn,$unlike_query);
                    $unlike_row = mysqli_fetch_array($unlike_result);
                    $total_unlikes = $unlike_row['cntUnlikes'];
 
            ?>
                    <div class="post">
                        <h1><?php echo $title; ?></h1>
                        <div class="post-text">
                            <?php echo $conntent; ?>
                        </div>
                        <div class="post-action">
                            <input type="button" value="Like" id="like_<?php echo $postid; ?>" class="like" style="<?php if($type == 1){ echo "color: #ffa449;"; } ?>" /> (<span id="likes_<?php echo $postid; ?>"><?php echo $total_likes; ?></span>) 
                            <input type="button" value="Unlike" id="unlike_<?php echo $postid; ?>" class="unlike" style="<?php if($type == 0){ echo "color: #ffa449;"; } ?>" /> (<span id="unlikes_<?php echo $postid; ?>"><?php echo $total_unlikes; ?></span>)
                        </div>
                    </div>
            <?php
                }
            ?>
        </div>
   </div>
</div>
<style>
.content{
    border: 0px solid black;
    border-radius: 3px;
    padding: 5px;
    margin: 0 auto;
    width: 70%;
}
.post{
    border-bottom: 1px solid black;
    padding: 10px;
    margin-top: 10px;
    margin-bottom: 10px;
}
.post:last-child{
    border: 0;
}
.post h1{
    font-weight: normal;
    font-size: 30px;
}
.post-text{
    letter-spacing: 1px;
    font-size: 15px;
    font-family: serif;
    color: gray;
    text-align: justify;
}
.post-action{
    margin-top: 15px;
    margin-bottom: 15px;
}
.like,.unlike{
    border: 0;
    background: none;
    letter-spacing: 1px;
    color: lightseagreen;
}
.like,.unlike:hover{
    cursor: pointer;
}
</style>
</body>
</html>
likeunlike.php
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
//likeunlike.php
<?php
include "dbcon.php";
$userid = 7;
$postid = $_POST['postid'];
$type = $_POST['type'];
 
$query = "SELECT COUNT(*) AS cntpost FROM like_unlike WHERE postid=".$postid." and userid=".$userid;
 
$result = mysqli_query($conn,$query);
$fetchdata = mysqli_fetch_array($result);
$count = $fetchdata['cntpost'];
 
if($count == 0){
    $insertquery = "INSERT INTO like_unlike(userid,postid,type) values(".$userid.",".$postid.",".$type.")";
    mysqli_query($conn,$insertquery);
}else {
    $updatequery = "UPDATE like_unlike SET type=" . $type . " where userid=" . $userid . " and postid=" . $postid;
    mysqli_query($conn,$updatequery);
}
 
$query = "SELECT COUNT(*) AS cntLike FROM like_unlike WHERE type=1 and postid=".$postid;
$result = mysqli_query($conn,$query);
$fetchlikes = mysqli_fetch_array($result);
$totalLikes = $fetchlikes['cntLike'];
 
$query = "SELECT COUNT(*) AS cntUnlike FROM like_unlike WHERE type=0 and postid=".$postid;
$result = mysqli_query($conn,$query);
$fetchunlikes = mysqli_fetch_array($result);
$totalUnlikes = $fetchunlikes['cntUnlike'];
 
$return_arr = array("likes"=>$totalLikes,"unlikes"=>$totalUnlikes);
 
echo json_encode($return_arr);
?>
dbcon.php
?
1
2
3
4
5
6
7
//dbcon.php
<?php
$conn = new mysqli('localhost','root','','testingdb');
if ($conn->connect_error) {
    die('Error : ('. $conn->connect_errno .') '. $conn->connect_error);
}
?>

Posted by ednalan at 1:46 AM . Under , .

Related Post