Star Rating System with PHP, MySQL, jQuery and Ajax
A star rating system is a method of evaluating and displaying the quality or performance of something using a number of stars. The number of stars typically ranges from one to five, with one star being the lowest rating and five stars being the highest rating.
In this tutorial, we will show you how to build 5 star rating system with PHP MySQL using jQuery and Ajax. As well as, To display the 5 star ratings on the web (HTML) page from the database.
Table of Contents
- Step 1: Create Star Rating Table into Database
- Step 2: Create DB Connection PHP File
- Step 3: Create Html to Get and Display Star Rating From Database
- Step 4: Store Star Rating Into Database using jQuery Ajax
Step 1: Create Star Rating Table into Database
First of all, open your database and run the following sql queries to create country, state and city table into the database:
Run this following sql query to create posts and post_rating table into your database:
CREATE TABLE `posts` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(100) NOT NULL,
`content` text NOT NULL,
`link` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `post_rating` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`post_id` int(11) NOT NULL,
`rating` int(2) NOT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;Step 2: Create DB Connection PHP File
In this step, create a file name db.php and update the following code into db.php file:
<?php
$servername='localhost';
$username='root';
$password='';
$dbname = "my_db";
$conn=mysqli_connect($servername,$username,$password,"$dbname");
if(!$conn){
die('Could not Connect MySql Server:' .mysql_error());
}
?>Note that, This code is used to create a MySQL database connection in PHP project.
Step 3: Create Html to Get and Display Star Rating From Database
In this step, create a display-star-rating.php file and update the below PHP and HTML code into display-star-rating.php file.
Note that, This HTML code shows the star rating on posts. And also user give rating by clicking on stars.
Now, update the following html form into display-star-rating.php file:
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<title>Get And Display Star Rating In PHP with jQuery Ajax</title>
<!-- CSS -->
<link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/font-awesome/latest/css/font-awesome.min.css">
<link href='https://cdnjs.cloudflare.com/ajax/libs/jquery-bar-rating/1.2.2/themes/fontawesome-stars.min.css' rel='stylesheet' type='text/css'>
<!-- Script -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js" type="text/javascript"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery-bar-rating/1.2.2/jquery.barrating.min.js" integrity="sha512-nUuQ/Dau+I/iyRH0p9sp2CpKY9zrtMQvDUG7iiVY8IBMj8ZL45MnONMbgfpFAdIDb7zS5qEJ7S056oE7f+mCXw==" crossorigin="anonymous"></script>
<style>
.content{
border: 0px solid black;
border-radius: 3px;
padding: 5px;
margin: 0 auto;
width: 50%;
}
.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 a.link{
text-decoration: none;
color: black;
}
.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>
</head>
<body>
<div class="container">
<div class="content">
<?php
include "db.php";
$user_id = 10;
$query = "SELECT * FROM posts";
$result = mysqli_query($con,$query);
while($row = mysqli_fetch_array($result)){
$post_id = $row['id'];
$title = $row['title'];
$content = $row['content'];
$link = $row['link'];
// User rating
$query = "SELECT * FROM post_rating WHERE post_id=".$post_id." and user_id=".$user_id;
$userresult = mysqli_query($con,$query) or die(mysqli_error());
$fetchRating = mysqli_fetch_array($userresult);
$rating = $fetchRating['rating'];
// get average
$query = "SELECT ROUND(AVG(rating),1) as averageRating FROM post_rating WHERE post_id=".$post_id;
$avgresult = mysqli_query($con,$query) or die(mysqli_error());
$fetchAverage = mysqli_fetch_array($avgresult);
$averageRating = $fetchAverage['averageRating'];
if($averageRating <= 0){
$averageRating = "No rating yet.";
}
?>
<div class="post">
<h1><a href='<?php echo $link; ?>' class='link' target='_blank'><?php echo $title; ?></a></h1>
<div class="post-text">
<?php echo $content; ?>
</div>
<div class="post-action">
<!-- Rating -->
<select class='rating' id='rating_<?php echo $post_id; ?>' data-id='rating_<?php echo $post_id; ?>'>
<option value="1" >1</option>
<option value="2" >2</option>
<option value="3" >3</option>
<option value="4" >4</option>
<option value="5" >5</option>
</select>
<div style='clear: both;'></div>
Average Rating : <span id='avgrating_<?php echo $post_id; ?>'><?php echo $averageRating; ?></span>
<!-- Set rating -->
<script type='text/javascript'>
$(document).ready(function(){
$('#rating_<?php echo $post_id; ?>').barrating('set',<?php echo $rating; ?>);
});
</script>
</div>
</div>
<?php
}
?>
</div>
</div>
<script type="text/javascript">
$(function() {
$('.rating').barrating({
theme: 'fontawesome-stars',
onSelect: function(value, text, event) {
// Get element id by data-id attribute
var el = this;
var el_id = el.$elem.data('id');
// rating was selected by a user
if (typeof(event) !== 'undefined') {
var split_id = el_id.split("_");
var post_id = split_id[1]; // post_id
// AJAX Request
$.ajax({
url: 'store-star-rating-db.php',
type: 'post',
data: {post_id:post_id,rating:value},
dataType: 'json',
success: function(data){
// Update average
var average = data['averageRating'];
$('#avgrating_'+post_id).text(average);
}
});
}
}
});
});
</script>
</body>
</html>Step 4: Store Star Rating Into Database using jQuery Ajax
In this step, create an again new PHP file named store-star-rating-db.php. This PHP code will store star rationg on post by user into database table.
To update the following php and html code into store-star-rating-db.php file:
<?php
include "db.php";
$user_id = 10; // User id
$post_id = $_POST['post_id'];
$rating = $_POST['rating'];
// Check entry within table
$query = "SELECT COUNT(*) AS postCount FROM post_rating WHERE post_id=".$post_id." and user_id=".$user_id;
$result = mysqli_query($con,$query);
$fetchdata = mysqli_fetch_array($result);
$count = $fetchdata['postCount'];
if($count == 0){
$insertquery = "INSERT INTO post_rating(user_id,post_id,rating) values(".$user_id.",".$post_id.",".$rating.")";
mysqli_query($con,$insertquery);
}else {
$updatequery = "UPDATE post_rating SET rating=" . $rating . " where user_id=" . $user_id . " and post_id=" . $post_id;
mysqli_query($con,$updatequery);
}
// get average
$query = "SELECT ROUND(AVG(rating),1) as averageRating FROM post_rating WHERE post_id=".$post_id;
$result = mysqli_query($con,$query) or die(mysqli_error());
$fetchAverage = mysqli_fetch_array($result);
$averageRating = $fetchAverage['averageRating'];
$return_arr = array("averageRating"=>$averageRating);
echo json_encode($return_arr);Note that, if you found any error ralated to jQuery star rating library. So, You can download the plugin from here. And then include in your display-star-rating.php file.
Thanks for reading !!!
#jquery #php #mysql #ajax