Build PHP MySQL 5 Star Rating System using jQuery AJAX

Last updated on: by Editorial Team

PHP 5 star rating system tutorial; This comprehensive tutorial will guide us on how to effortlessly create PHP 5 star rating system using MySQL, AJAX and jquery bar rating plugin.

Star rating is commonly used for giving ratings on a specific scale. It is primarily used by reviewers for ranking things such as films, TV shows, restaurants, hotels and products on eCommerce websites. Ideally, a system of one to five stars is generally used in hotel ratings, with five stars being the highest and one star considered to be the lowest.

The jQuery bar rating plugin is an awesome package that comes with powerful traits to display ratings in php based application, which is minimal and lightweight and best for implementing ratings in PHP.

We will create a simple star rating using php, and it will be useful for showing the average rating in php for your products and items. This tutorial will use the jQuery bar plugin, which offers PHP review and rating script and seamlessly flexibility to build a 5 Star rating system in PHP.

Furthermore, we will also explain how to store star rating value in the MySQL database through AJAX request. Additionally, this value will keep on changing if the user updates the ratings.

Create PHP MySQL 5 Star Rating System with AJAX

  • Step 1: Create Database and Table
  • Step 2: PHP MySQL Server Connection/li>
  • Step 3: Set Up jQuery Bar Rating Plugin
  • Step 4: Display Star Rating and Average Rating
  • Step 5: Implement 5 Star Rating in PHP

Create Database and Table

Head over to PHPMyAdmin and open the SQL tab; using the SQL section, we can execute SQL queries to create products table and product_rating tables.

Run following command to generate table structure for table `products`.

CREATE TABLE `products` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO products VALUES (1, 'New Balance CK4040 V5', 'Phasellus at scelerisque eros, elementum congue leo.');
INSERT INTO products VALUES (2, 'SS Players Series - Chest Guard', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit.');
INSERT INTO products VALUES (3, "Shrey Supporters - TRUNK", "Ut ultrices nibh non orci sollicitudin.");
INSERT INTO products VALUES (4, "New Balance - Batting Inners", "Sed sed magna consequat, cursus felis.");
INSERT INTO products VALUES (5, "Gray-Nicolls TEST Arm Guard", "Etiam vulputate condimentum facilisis.");
INSERT INTO products VALUES (6, "SS Ton Vintage MSD 4.0 - Cricket Bat", "Aliquam nec tortor porttitor, mollis quam sit amet.");
INSERT INTO products VALUES (7, "Shrey Pro Guard - Titanium", "Vivamus lacinia augue id ipsum luctus molestie.");

Execute command to generate table structure for table `product_rating`.

CREATE TABLE `product_rating` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `rating` int(2) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

PHP MySQL Server Connection

Get into your project folder, create a database folder and give it a name something like db.php, add the database name, username and password to connect PHP to server.

To make the PHP and MySQL database connection, update the database/db.php file:

<?php

    $servername = 'localhost';
    $username = 'root';
    $password = '';
    $dbname = "php_db";
    $connection = mysqli_connect($servername, $username, $password, $dbname);
      
    // Validate database connection
    if(!$connection){
        die('Database connection error : ' .mysql_error());
    }
    
?>

Set Up jQuery Bar Rating Plugin

In this step, we will install and configure the jQuery bar plugin into the PHP MySQL project; hence, click on this link to download the jQuery Bar Rating plugin.

Then, you require to create an assets folder and unzip the bar rating package inside the assets/jquery-bar-rating-master directory.

From this directory you have to import and add the rating and font awesome package’s CSS and library.

Display Star Rating and Average Rating

In this step, we will show you how to fetch products from the database, get ratings from the products rating table, and calculate and show numerical ratings in the products card.

Make sure to create show_star_ratings.php file similarly add all the following code within the file.

<?php
    $user_id = 1;
    $query = "SELECT * FROM products";
    $result = mysqli_query($connection, $query);
    
    while($row = mysqli_fetch_array($result)){
        $product_id = $row['id'];
        $name = $row['name'];
        $description = $row['description'];

        // Star rating
        $query = "SELECT * FROM product_rating WHERE product_id = " . $product_id . " and user_id = " . $user_id;
        $productResult = mysqli_query($connection, $query);
        $getRating = mysqli_fetch_array($productResult);
        $rating = $getRating['rating'];

        // Rating
        $query = "SELECT ROUND(AVG(rating), 1) as numRating FROM product_rating WHERE product_id=".$product_id;
        $avgresult = mysqli_query($connection, $query);
        $fetchAverage = mysqli_fetch_array($avgresult);
        $numRating = $fetchAverage['numRating'];

        if($numRating <= 0){
            $numRating = "Not ratings given.";
        }
?>

<div class="card mb-3">
    <div class="card-body">
        <h2 class="card-title"><?php echo $name; ?></h2>
        <p class="card-text">
            <?php echo $description; ?>
        </p>

        <!-- 5 Star Rating -->
        <select name="star_rating_option" class="rating" id='star_rating_<?php echo $product_id; ?>'
            data-id='rating_<?php echo $product_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>

        Rating : <span id='numeric_rating_<?php echo $product_id; ?>'><?php echo $numRating; ?></span>                
    </div>
</div>
<?php } ?>

jQuery Bar Rating Plugin converts regular select options field into a rating widget. You may also extend the design of this star rating system using CSS.

Implement 5 Star Rating in PHP

Next, you have to create ajax_star_rating.php file at the root of your php project immediately after open this file and add the suggested code inside of it.

Primarily, through this file, we are checking the rating inside the product_rating table. If we don’t find any rating in the table, then we are inserting the rating, and if found an existing rating, then updating or replacing it with the current rating.

Apart from that, we are also calculating the average rating through the SQL ROUND(AVG) Function.

<?php

include "./database/db.php";

$user_id = 1;
$product_id = $_POST['product_id'];
$rating = $_POST['rating'];

// Check rating inside the table
$query = "SELECT COUNT(*) AS countProduct FROM product_rating WHERE product_id = " . $product_id . " and user_id = " . $user_id;

$result = mysqli_query($connection, $query);
$getdata = mysqli_fetch_array($result);
$count = $getdata['countProduct'];

if($count == 0){
 $insertquery = "INSERT INTO product_rating(user_id, product_id, rating) values(". $user_id .", ". $product_id .", ". $rating .")";
 mysqli_query($connection, $insertquery);
}else {
 $updateRating = "UPDATE product_rating SET rating=" . $rating . " where user_id=" . $user_id . " and product_id=" . $product_id;
 mysqli_query($connection, $updateRating);
}

// fetch rating
$query = "SELECT ROUND(AVG(rating),1) as numRating FROM product_rating WHERE product_id=".$product_id;
$result = mysqli_query($connection, $query) or die(mysqli_error());
$getAverage = mysqli_fetch_array($result);
$numRating = $getAverage['numRating'];

$return_arr = array("numRating"=>$numRating);

echo json_encode($return_arr);

Eventually, you need to create an index.php file and import a database connection file, jQuery from CDN, import bar rating JavaScript, bootstrap CSS, and fontawesome and fontawesome-stars CSS.

Don’t forget to add the bar rating script at the bottom to initialize the star rating system in the PHP application.

Update index.php file with advised code.

<?php include "./database/db.php"; ?>

<!DOCTYPE html>
<html>

<head>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta3/dist/css/bootstrap.min.css" rel="stylesheet">
    <link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/font-awesome/latest/css/font-awesome.min.css">
    <link href='./assets/jquery-bar-rating-master/dist/themes/fontawesome-stars.css' rel='stylesheet' type='text/css'>
    <link href="./styles.css" type="text/css" rel="stylesheet" />
</head>

<body>

    <div class="content container mt-5">
       <?php include "./show_star_ratings.php"; ?>
    </div>
</body>

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="./assets/jquery-bar-rating-master/dist/jquery.barrating.min.js"></script>

<!-- Invoke star rating -->
<script type='text/javascript'>
    $(document).ready(function(){
        $('#star_rating_<?php echo $product_id; ?>').barrating('set',<?php echo $rating; ?>);
    });

    $(function () {
        $('.rating').barrating({
            theme: 'fontawesome-stars',
            onSelect: function (value, text, event) {
                
                var el = this;
                var el_id = el.$elem.data('id');
                
                if (typeof (event) !== 'undefined') {
                    var split_id = el_id.split("_");
                    var product_id = split_id[1];
                    $.ajax({
                        url: './ajax_star_rating.php',
                        type: 'post',
                        data: {
                            product_id: product_id,
                            rating: value
                        },
                        dataType: 'json',
                        success: function (data) {
                            var average = data['numRating'];
                            $('#numeric_rating_' + product_id).text(average);
                        }
                    });
                }
            }
        });
    });
</script>
</html>

We deliberately passed the $user_id statically, however you can set the $session for $user_id variable to perfectly manage the rating system.

5 Star Rating in PHP

Conclusion

So thats it for now; we have discussed how to build star rating in PHP using the jQuery Bar Rating plugin not only but also share with you how to store star rating values in the MySQL database by making AJAX request.

We hope this tutorial will help you understand everything better related to the topic. However, not just star rating, but you can also create a square rating, pill rating, reversed rating, horizontal rating and movie rating with this marvellous plugin.