Star Rating System with PHP MySQL & jQuery and Ajax

Star rating system with PHP MySQL & jquery and ajax on products, posts. In this tutorial, we will show you how to build a start rating system with PHP MySQL using jQuery and ajax. As well as, To display the star ratings on the web (HTML) page from the database.

If the user/customer changes the start rating then send an AJAX request to save the user currently rating status on the MySQL database table with PHP.

Sometime, you have products/posts on your website or blog. And may want to get start rating from user. So this tutorial will help you to get review of start rating into your website or blog using jQuery ajax in php mysql.

PHP MySQL Star Rating system using jQuery Ajax

Just follow the below given simple steps to create customer/user star rating system in PHP MySQL using jQuery and ajax. And store star rating into database:

  • 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.

Conclusion

Get star rating and display star rating from database in PHP with jQuery and ajax tutorial, you have learned how to get start rating on posts/products by user/customer using jQuery ajax in php.

Related Posts