The upvote and downvote system is used to take decisions on which content is better or which content is bad for readers. We represent upvote with an arrow-up sign and downvote with the arrow-down sign.
In this tutorial, we will learn how to create an upvote and downvote system using PHP, AJAX, and MYSQL databases. We will use PHP PDO prepared statements to make a secure system and JQuery, bootstrap. As you know, we use bootstrap to make a responsive website using readymade CSS classes. Ajax is used to perform CRUD operations without page refreshing and JQUERY is a JavaScript open source framework that helps to reduce JavaScript code lines and is easy to write and understand. The Upvote and downvote system is mostly used for QA forum websites or event websites like Techno Smarter QA, Stackoverflow, and Question2answer. This is known as the voting system or like and unlike system. You can use it as a voting system for kinds of websites in PHP. Upvote means that you like this post, question, or answer and downvote means that you don’t like this post, question, or answer. We will use PHP PDO prepared statements to avoid SQL injection and direct attacks. This is the most important thing that you avoid SQL injection with prepared statements and also direct attacks. This will complete the secured upvote and downvote system using PHP, AJAX, Jquery, Bootstrap, PDO prepared statements and the MYSQL database.
First of all, we will create three tables.
User table – Users can upvote or downvote after logging in to their accounts.
Post Table – You can use it as a question-answer table. Users can upvote and downvote any post.
User votes – All votes will be saved in this table.
Let’s create an users table using the query below –
CREATE TABLE `users` (
`id` int(11) NOT NULL,
`username` varchar(255) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`password` varchar(355) DEFAULT NULL
) ;
Insert two users into the user's table using the query below –
INSERT INTO `users` (`id`, `username`, `email`, `password`) VALUES
(1, 'demo', 'demo@gmail.com', '$2y$04$0FsMXPoAPf4zDd0JyfYFFejx2DKtqO9c4gEwjGOvbQzQ1UOJsx4p6'),
(2, 'demo1', 'demo1@gmail.com', '$2y$04$jqiBn2yz6DYdAUBnSdleM.hAabjBZ1Yrr/1rO0piAKNUIzBegsavO');
Username – demo, password – 123demo
Username – demo1 , password - 123demo1
You will need these credentials while login or you can create a registration form in PHP but we already inserted two users’ data into the database.
Now, create a posts MYSQL table using the query below –
CREATE TABLE `posts` (
`post_id` int(11) NOT NULL,
`title` varchar(355) DEFAULT NULL,
`content` longtext DEFAULT NULL,
`netvotes` int(11) NOT NULL
);
Let’s insert some posts data into the posts table using the query below –
INSERT INTO `posts` (`post_id`, `title`, `content`, `netvotes`) VALUES
(1, 'How to use AJAX, and Jquery with PHP programming?', 'As you know that Ajax is used to create CRUD operations without page refreshing and Jquery is one of the best framework of Javascript. We will create an upvote and downvote system with ajax, PHP, jquery, and the MYSQL database.', 0),
(2, 'Upvote and downvote system like QA forum in PHP ', 'A QA forum can't be completed without a voting system. Using this upvote and downvote system, users can vote on questions or posts. Users can give upvotes or downvotes.', 0),
(3, 'How to create a voting system using PHP, AJAX, and MYSQL database?', 'The voting system is one of the most useful systems for blogs, QA forums, and other content based websites or events.', 0),
(4, 'How to create an upvote and downvote system in PHP, AJAX, Jquery, and MYSQL?', 'In this tutorial post, we learn the voting system in PHP, AJAX, and JQUERY with the MYSQL database. ', 0);
Now, create an user votes MYSQL table using the query below –
CREATE TABLE `uservotes` (
`voteid` int(11) NOT NULL,
`postid` int(10) UNSIGNED NOT NULL,
`userid` int(10) UNSIGNED NOT NULL,
`vote` tinyint(4) NOT NULL
);
These MYSQL tables will be connected to each other. In the user votes table, we will store post id userid, and vote (1 or -1). We will update net votes in the posts table using PHP PDO prepared statements.
First of all, create a connection file which is known as a config file. This file will help you connect to the database.
config.php
<?php session_start();
define('DBNAME','tutedb');
define('DBUSER','root');
define('DBPASS','');
define('DBHOST','localhost');
try {
$db = new PDO("mysql:host=".DBHOST.";dbname=".DBNAME, DBUSER, DBPASS);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//echo "Your page is connected with database successfully..";
} catch(PDOException $e) {
echo "Issue -> Connection failed: " . $e->getMessage();
}
?>
Provide all the details in the correct form.
Now, create a login form.
login.php
<?php require_once("config.php");?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Login</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0/dist/css/bootstrap.min.css" rel="stylesheet">
<link rel="stylesheet" type="text/css" href="style.css">
</head>
<body>
<div class="container">
<div div class="row">
<div style="background:grey; color: #fff;" class="card">
<h1>Login to up vote or down vote </h1>
</div>
</div>
<br>
<div class="row">
<div class="col-sm-3">
</div>
<div class="col-sm-6">
<?php
if(isset($_POST['login_submit']))
{
$login_var=$_POST['login_var'];
$password=$_POST['password'];
$sql= "SELECT count(*) FROM users WHERE username=:login_var OR email=:login_var"; $stmt = $db->prepare($sql);
$stmt->bindParam(':login_var', $login_var ,PDO::PARAM_STR);
$stmt->execute();
$count= $stmt->fetchColumn();
if($count>0)
{
$sql="SELECT id,password FROM users WHERE username=:login_var OR email=:login_var"; $stmt=$db->prepare($sql);
$stmt->bindParam(':login_var', $login_var ,PDO::PARAM_STR);
$stmt->execute();
$row=$stmt->fetch();
if(password_verify($password,$row['password'])){
$_SESSION["login_session"]="1";
$_SESSION["userid"]=$row['id'];
header("location:index.php");
}
else{
echo 'Invalid Username or Password';
}
}
else
{
echo 'Invalid Username or Password';
}
}
?>
<form action="" method="post">
<div class="form-group">
<label>Username or Email</label>
<input type="text" class="form-control" placeholder="Username or email" name="login_var" required>
</div>
<div class="form-group">
<label>Password</label>
<input type="password" class="form-control" placeholder="Password" name="password" required>
</div>
<div class="form-group form-check">
</div>
<button type="submit" class="btn btn-primary" name="login_submit">Login </button>
</form>
</div>
<div class="col-sm-3">
</div>
</div>
</div>
</body>
</html>
In the code above, we linked bootstrap min CSS to make a responsive login system. We already discussed the login and registration system in the previous tutorial. First of all, log in then upvote and downvote on any post. You can log in with different users to upvote and downvote any posts.
We will display all posts in the list with upvote and downvote icon images in the index file.
Let’s create an index.php file.
index.php
<?php require_once("config.php");
if(!isset($_SESSION['login_session'])){
header("location:login.php");
}
else
{
$userid=$_SESSION['userid'];
}
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Upvote and Downvote system using AJAX,Jquery,Bootstrap with MYSQL database.</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0/dist/css/bootstrap.min.css" rel="stylesheet">
<link rel="stylesheet" type="text/css" href="style.css">
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script src="vote_scripts.js"></script>
</head>
<body>
<div class="container">
<div div class="row">
<div style="background:grey; color: #fff;" class="card">
<h1>All Posts </h1> <a href="logout.php">Logout</a>
</div>
</div>
<br>
<div class="row">
<?php
$sql="SELECT * FROM posts ORDER BY post_id DESC";
$stmt=$db->prepare($sql);
$stmt->execute();
$rows=$stmt->fetchAll();
foreach($rows as $row)
{
$votes= 0;
$sql="SELECT vote FROM uservotes WHERE postid=:postid AND userid=:userid";
$stmt=$db->prepare($sql);
$stmt->bindParam(':postid', $row['post_id'],PDO::PARAM_INT);
$stmt->bindParam(':userid', $userid,PDO::PARAM_INT);
$stmt->execute();
$rows=$stmt->fetchAll();
$up = "";
$down = "";
foreach($rows as $rowv){
$rowv['vote'];
}
if(!empty($rowv["vote"])) {
$vote = $rowv["vote"];
if($vote == -1) {
$up = "enabled";
$down = "disabled";
}
if($vote == 1) {
$up = "disabled";
$down = "enabled";
}
}
?> <div class="col-sm-12 list">
<div class="row card_item ">
<div class="col-1">
<div id="links-<?php echo $row['post_id']; ?>" class="voting">
<div class="btn-vote">
<input type="button" title="Vote Up " class="up" onClick="addVote(<?php echo $row['post_id']; ?>,'1')" <?php echo $up; ?> />
<div class="label-vote"><?php echo $row['netvotes']; ?></div>
<input type="button" title="Vote Down" class="down" onClick="addVote(<?php echo $row['post_id']; ?>,'-1')" <?php echo $down; ?> />
</div>
</div>
</div>
<div class="col-11">
<li class="title"><?php echo $row["title"]; ?> </li>
<div class="content"><?php echo $row['content']?> </div>
</div>
</div>
</div>
<?php }
?>
</div>
</div>
</body>
</html>
As we already discussed above, we are creating an upvote and downvote system using PHP, AJAX, JQuery, and MYSQL database. In the code above, we linked the Jquery scripts file, bootstrap CSS file, and extra vote scripts. You can save jquery and bootstrap as local and link to the page. In the index file, we displayed all the posts using PHP PDO prepared statements. We displayed upvote and downvote icon images. As you can see , we called addVote() function on upvote(1 ) and downvote (-1 ) but we didn’t create any addVote() function in JavaScript . Let’s create a javascript file.
In the JS file, we will create AJAX and jquery code. AJAX will interact vote action file and the vote will be saved into the database.
vote_scripts.js
function addVote(id,vote) {
$.ajax({
url: "vote_action.php",
data: {id:id, vote:vote},
type: "POST",
beforeSend: function(){
$('#links-'+id+' .btn-vote').html("<img src='loading.gif' height='50px'>");
},
success: function(data){
//$('#data').html(data);
var obj = JSON.parse(data);
var vote = obj.vote;
var vote_status= obj.netvotes;
var up,down;
switch(vote) {
case "1":
vote = vote+1;
up="disabled";
down="enabled";
//vote_status = vote_status+1;
break;
case "-1":
vote = vote-1;
up="enabled";
down="disabled";
//vote_status = vote_status-1;
break;
case "0":
up="enabled";
down="enabled";
break;
}
$('#vote-'+id).val(vote);
$('#vote_status-'+id).val(vote_status);
var vote_button_html = '<input type="button" title="Up" class="up" onClick="addVote('+id+',\'1\')" '+up+' /><div class="label-vote">'+vote_status+'</div><input type="button" title="Down" class="down" onClick="addVote('+id+',\'-1\')" '+down+' />';
$('#links-'+id+' .btn-vote').html(vote_button_html);
}
});
}
In the vote scripts file above, we created the addVote() function. When a user clicks the upvote or downvote image icon then addVote() function works. The id and vote value go to the vote_action.php file using ajax without page refreshing.
Let’s create a vote action PHP file –
vote_action.php
<?php require_once("config.php"); include("functions.php");
$getnewvote=$_POST["vote"];
if(!empty($_POST["id"])) {
$count=user_count_invote($_POST["id"],$_SESSION["userid"],$db);
if($count==0){
$resultq=insert_vote($_SESSION["userid"],$_POST["id"],$_POST["vote"],$db);
}
else
{
$rows=fetch_invote($_POST["id"],$_SESSION["userid"],$db);
foreach($rows as $rowm){
$oldrank=$rowm['vote'];
}
$_POST["vote"];
if($oldrank==1 && $_POST["vote"]==-1)
{
$resultq=update_vote($_SESSION["userid"],$_POST["id"],'-1',$db);
delete_vote($_POST["id"],$_SESSION["userid"],$db);
}
elseif($oldrank==0 && $_POST["vote"]==-1)
{
$resultq=update_vote($_SESSION["userid"],$_POST["id"],'-1',$db);
}
elseif($oldrank==-1 && $_POST["vote"]==1)
{
$resultq=update_vote($_SESSION["userid"],$_POST["id"],'+1',$db);
delete_vote($_POST["id"],$_SESSION["userid"],$db);
}
elseif($oldrank==0 && $_POST["vote"]==1)
{
$resultq=update_vote($_SESSION["userid"],$_POST["id"],'+1',$db);
}
else {}
}
if(!empty($resultq)){
switch($getnewvote) {
case "1":
update_vote_inposts($_POST["id"],'+1',$db);
break;
case "-1":
update_vote_inposts($_POST["id"],'-1',$db);
break;
}
}
$rows=fetch_netvote($_POST["id"],$db);
foreach($rows as $rowl){
$total_vote=$rowl['netvotes'];
}
$rows=fetch_invote($_POST["id"],$_SESSION["userid"],$db);
$rank=0;
foreach($rows as $rowm){
$rank=$rowm['vote'];
}
$response = array(
'netvotes' => $total_vote,
'vote' => $rank
);
echo json_encode($response);
}
?>
After Ajax responds, post id and vote (1 or -1 ) are delivered to the vote_action.php file.
In the code above, we created logic to insert votes, update votes, and update net votes in the posts table. We echo JSON response and in the JS file, we parse the JSON response and stored it in variables.
In the vote action PHP file, we used many user-defined functions listed below –
user_count_invote() – User already voted on this post or not. Count numbers –
fetch_invote() – Fetch already vote value .
update_vote() – Update vote in uservotes table –
delete_vote() – Delete vote row from in uservotes table when vote value is 0.
update_vote_inposts() – Update netvotes in posts .
These functions are created in the functions.php file.
Let’s create a functions.php file. We already included the function.php file in the vote_action.php file.
functions.php
<?php
function user_count_invote($postid,$userid,$db){
$sql= "SELECT count(*) from uservotes WHERE userid=:userid AND postid=:postid";
$stmt = $db->prepare($sql);
$stmt->bindParam(':postid', $postid, PDO::PARAM_INT);
$stmt->bindParam(':userid', $userid, PDO::PARAM_INT);
$stmt->execute();
$number_of_rows = $stmt->fetchColumn();
return $number_of_rows;
}
function insert_vote($userid,$postid,$vote,$db){
$sql="INSERT INTO uservotes(userid,postid,vote) VALUES(:userid,:postid,:vote)";
$stmt = $db->prepare($sql);
$stmt->bindParam(':userid', $userid, PDO::PARAM_INT);
$stmt->bindParam(':postid', $postid, PDO::PARAM_INT);
$stmt->bindParam(':vote', $vote, PDO::PARAM_STR);
$stmt->execute();
if($stmt)
{
return true;
}
}
function fetch_invote($postid,$userid,$db){
$sql= "SELECT * from uservotes WHERE userid=:userid AND postid=:postid";
$stmt = $db->prepare($sql);
$stmt->bindParam(':postid', $postid, PDO::PARAM_INT);
$stmt->bindParam(':userid', $userid, PDO::PARAM_INT);
$stmt->execute();
$row = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $row;
}
function update_vote($userid,$postid,$vote_type,$db){
$sql="UPDATE uservotes SET vote= vote$vote_type WHERE userid=:userid AND postid=:postid";
$stmt = $db->prepare($sql);
$stmt->bindParam(':userid', $userid, PDO::PARAM_INT);
$stmt->bindParam(':postid', $postid, PDO::PARAM_INT);
$stmt->execute();
if($stmt){
return true;
}
}
function update_vote_inposts($postid,$vote_type,$db){
$sql="UPDATE posts SET netvotes= netvotes$vote_type WHERE post_id=:postid";
$stmt = $db->prepare($sql);
$stmt->bindParam(':postid', $postid, PDO::PARAM_INT);
$stmt->execute();
}
function fetch_netvote($postid,$db){
$sql= "SELECT netvotes from posts WHERE post_id=:postid";
$stmt = $db->prepare($sql);
$stmt->bindParam(':postid', $postid, PDO::PARAM_INT);
$stmt->execute();
$row = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $row;
}
function delete_vote($postid,$userid,$db){
$sql= "DELETE from uservotes WHERE postid=:postid AND userid=:userid";
$stmt = $db->prepare($sql);
$stmt->bindParam(':postid', $postid, PDO::PARAM_INT);
$stmt->bindParam(':userid', $userid, PDO::PARAM_INT);
$stmt->execute();
}
?>
In the code above, we used PHP PDO prepared statements.
Now, you will need a CSS stylesheet to display upvote and downvote image icons.
style.css
li{
color: #000000;
text-decoration: none;
font-size: 20px;
list-style-type: none;
}
.list{
padding-bottom: 20px;
}
.card {
padding: 5px;
}
.content{
font-size: 14px;
}
.btn-vote input[type="button"]{width:22px;height:22px;border:0;cursor:pointer;}
.up {background:url('up.png')}
.up:disabled {background:url('up_off.png')}
.down {background:url('down.png')}
.down:disabled {background:url('down_off.png')}
.label-vote {width:22px;height:22px;text-align:center;font-size: 14px;}
.voting{ float: right; }
.card_item { border: 1px solid black;}
In the code above, you can see four image icons png. You can find these images on google or any site and save them in the same folder. You can use font-awesome icons instead of these image icons.
Recommended Posts:-
