PHP Example | Create a Rating System Using jQuery Ajax and MySQL

In this php example, we will introduce how to create a rating system. In order to create a rating system, we should answer these questions:

  • How to dispaly rating star?
  • How to change and get the star score?
  • How to send our rating score to web server?
  • How to save our rating score?

We will answer these question in ths example.

Step 1: create tables to save rating score in mysql

We will save rating score in mysql by creating some tables.

For example:

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,
  `userid` int(11) NOT NULL,
  `postid` 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: connect mysql and query scores

We can connect msyql and query rating scores to display.

For example:

<?php
$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "tutorial"; /* Database name */

$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
 die("Connection failed: " . mysqli_connect_error());
}

Step 3: create a html to display rating score

We will create a html to display rating score from mysql.

For example:

<?php include "config.php"; ?>

<!-- CSS -->
<link href="style.css" type="text/css" rel="stylesheet" />
<link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/font-awesome/latest/css/font-awesome.min.css">
<link href='jquery-bar-rating-master/dist/themes/fontawesome-stars.css' rel='stylesheet' type='text/css'>
 
<!-- Script -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<script src="jquery-bar-rating-master/dist/jquery.barrating.min.js" type="text/javascript"></script>

<div class="content">

 <?php
 $userid = 4;
 $query = "SELECT * FROM posts";
 $result = mysqli_query($con,$query);
 while($row = mysqli_fetch_array($result)){
  $postid = $row['id'];
  $title = $row['title'];
  $content = $row['content'];
  $link = $row['link'];

  // User rating
  $query = "SELECT * FROM post_rating WHERE postid=".$postid." and userid=".$userid;
  $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 postid=".$postid;
  $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">
   <h2><a href='<?php echo $link; ?>' class='link' target='_blank'><?php echo $title; ?></a></h2>
   <div class="post-text">
    <?php echo $content; ?>
   </div>
   <div class="post-action">
   <!-- Rating -->
   <select class='rating' id='rating_<?php echo $postid; ?>' data-id='rating_<?php echo $postid; ?>'>
    <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 $postid; ?>'><?php echo $averageRating; ?></span>

   <!-- Set rating -->
   <script type='text/javascript'>
   $(document).ready(function(){
    $('#rating_<?php echo $postid; ?>').barrating('set',<?php echo $rating; ?>);
   });
   </script>
   </div>
  </div>
 <?php
 }
 ?>

</div>

Step 4: create css style for html page

To make our html page beautiful, we shoul set some css style for our html page.

.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;
}

Step 5: send our rating score to web server

We can use jquery and ajax to send our rating score to web server and save it to mysql.

$(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 postid = split_id[1]; // postid

     // AJAX Request
     $.ajax({
       url: 'rating_ajax.php',
       type: 'post',
       data: {postid:postid,rating:value},
       dataType: 'json',
       success: function(data){
         // Update average
         var average = data['averageRating'];
         $('#avgrating_'+postid).text(average);
       }
     });
   }
  }
 });
});

In this example, we will send rating score to rating_ajax.php.

Step 6: receive rating score and save it to mysql

We can use a php file to process rating score sent by client, then save it to mysql.

<?php

include "config.php";

$userid = 4; // User id
$postid = $_POST['postid'];
$rating = $_POST['rating'];

// Check entry within table
$query = "SELECT COUNT(*) AS cntpost FROM post_rating WHERE postid=".$postid." and userid=".$userid;

$result = mysqli_query($con,$query);
$fetchdata = mysqli_fetch_array($result);
$count = $fetchdata['cntpost'];

if($count == 0){
 $insertquery = "INSERT INTO post_rating(userid,postid,rating) values(".$userid.",".$postid.",".$rating.")";
 mysqli_query($con,$insertquery);
}else {
 $updatequery = "UPDATE post_rating SET rating=" . $rating . " where userid=" . $userid . " and postid=" . $postid;
 mysqli_query($con,$updatequery);
}

// get average
$query = "SELECT ROUND(AVG(rating),1) as averageRating FROM post_rating WHERE postid=".$postid;
$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);
?>

Finally, we will see a rating system as follows:

PHP Example | Create a Rating System Using jQuery Ajax and MySQL