Simple Star Review Rating In PHP MySQL (Free Download)

Welcome to a quick tutorial on how to create a simple star rating system with PHP and MySQL. So you want to open a blog post, article, or maybe even products for users to rate? Well, let us walk through a simple and lightweight example in this guide – Read on!

ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

TABLE OF CONTENTS

 

DOWNLOAD & NOTES

Firstly, here is the download link to the example code as promised.

 

QUICK NOTES

  • Create a database and import 1-database.sql.
  • Change the database settings in 2-lib.php to your own.
  • Launch 3a-products.php in your browser.
If you spot a bug, feel free to comment below. I try to answer short questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

SCREENSHOT

 

EXAMPLE CODE DOWNLOAD

Click here to download the source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

 

PHP STAR RATING SYSTEM

All right, let us get into the details of building a simple PHP MYSQL star rating system.

 

STEP 1) STAR RATING DATABASE TABLE

1-database.sql
CREATE TABLE `star_rating` (
  `product_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `rating` smallint(6) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `star_rating`
  ADD PRIMARY KEY (`product_id`,`user_id`);
Field Description
product_id Partial primary key and foreign key. The product that is being rated, or you can change this to an article ID… Or whatever that you are rating.
user_id Partial primary key and foreign key. The user ID of the rating.
rating The number of stars given.

Yes, 3 fields are all we need. Feel free to add more fields as your project requires.

 

 

STEP 2) PHP STARS RATING LIBRARY

2-lib.php
<?php
class Stars {
  // (A) CONSTRUCTOR - CONNECT TO DATABASE
  private $pdo;
  private $stmt;
  public $error;
  function __construct () {
    $this->pdo = new PDO(
      "mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=".DB_CHARSET,
      DB_USER, DB_PASSWORD, [
      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_NAMED
    ]);
  }

  // (B) DESTRUCTOR - CLOSE DATABASE CONNECTION
  function __destruct () {
    if ($this->stmt !== null) { $this->stmt = null; }
    if ($this->pdo !== null) { $this->pdo = null; }
  }
 
  // (C) HELPER - RUN SQL QUERY
  function query ($sql, $data=null) {
    $this->stmt = $this->pdo->prepare($sql);
    $this->stmt->execute($data);
  }
 
  // (D) SAVE/UPDATE USER STAR RATING
  function save ($pid, $uid, $stars) {
    $this->query(
      "REPLACE INTO `star_rating` (`product_id`, `user_id`, `rating`) VALUES (?,?,?)",
      [$pid, $uid, $stars]
    );
    return true;
  }

  // (E) GET USER STAR RATINGS
  function get ($uid) {
    $this->query("SELECT * FROM `star_rating` WHERE `user_id`=?", [$uid]);
    $star = [];
    while ($r = $this->stmt->fetch()) { $star[$r["product_id"]] = $r["rating"]; }
    return $star;
  }
 
  // (F) GET AVERAGE STAR RATING
  function avg () {
    $this->query(
      "SELECT `product_id`, ROUND(AVG(`rating`), 2) `avg`, COUNT(`user_id`) `num`
      FROM `star_rating` GROUP BY `product_id`"
    );
    $average = [];
    while ($r = $this->stmt->fetch()) {
      $average[$r["product_id"]] = [
        "avg" => $r["avg"], // average rating
        "num" => $r["num"] // number of reviews
      ];
    }
    return $average;
  }
}

// (G) DATABASE SETTINGS - CHANGE TO YOUR OWN !
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8");
define("DB_USER", "root");
define("DB_PASSWORD", "");

// (H) NEW STARS OBJECT
$_STARS = new Stars();

This one looks complicated at first, but keep calm and look carefully.

  • (A, B, H) The constructor automatically connects to the database when new Stars() is created, the destructor closes the connection.
  • (C) query() A helper function to run an SQL query.
  • (D To F) There are only 3 functions here!
    • save() Adds/updates a star rating entry
    • get() Fetches all the ratings set by the specified user
    • avg() Gets the average ratings for all products.
  • (G) Self-explanatory, remember to change the database settings to your own.

 

 

STEP 3) PRODUCTS PAGE

3A) THE HTML

3a-page.php
<?php
// (A) CORE LIBRARY + USER ID
require "2-lib.php";
$uid = 901; // fixed for demo, use your own session user id
 
// (B) UPDATE STAR RATINGS
if (isset($_POST["pid"]) && isset($_POST["stars"])) {
  echo $_STARS->save($_POST["pid"], $uid, $_POST["stars"])
    ? "<div class='note'>Rating Saved</div>" : "<div class='note'>Error</div>" ;
}
?>

<div id="demo"><?php
// (C) GET RATINGS
$average = $_STARS->avg(); // average ratings
$ratings = $_STARS->get($uid); // ratings by current user
 
// (D) OUTPUT DUMMY PRODUCTS
$products = [1 => "Foo Bar", 2 => "Goo Bar"];
foreach ($products as $pid=>$pdt) { ?>
<div class="pCell">
  <img class="pImg" src="box.png">
  <div class="pName"><?=$pdt?></div>
  <div class="pReview">Customer Review</div>
  <div class="pStar" data-pid="<?=$pid?>"><?php
    $rate = isset($ratings[$pid]) ? $ratings[$pid] : 0 ;
    for ($i=1; $i<=5; $i++) {
      $css = $i<=$rate ? "star" : "star blank" ;
      echo "<div class='$css' data-i='$i'></div>";
    }
  ?></div>
  <div class="pStat">
    <?=$average[$pid]["avg"]?> out of 5 stars.
  </div>
  <div class="pStat">
    <?=$average[$pid]["num"]?> customer ratings.
  </div>
</div>
<?php }
?></div>
 
<!-- (E) HIDDEN FORM TO UPDATE STAR RATING -->
<form id="ninForm" method="post" target="_self">
  <input id="ninPdt" type="hidden" name="pid">
  <input id="ninStar" type="hidden" name="stars">
</form>

Oh no, this looks like trouble, but let’s walk through it part by part again.

  1. The PHP library shouldn’t need any further introductions, but take note of $uid = 901. We use a fixed dummy user for this demo, use the session user ID in your own system.
  2. This part updates the star rating for a product, but it is only processed when the user clicks on a star and updates the rating.
  3. Gets the average star ratings for all products, and also for the current user.
  4. Generates the HTML for the dummy products, and also the rating stars. The “highlight number of stars on hover” and “update stars rating” parts will be done with Javascript.
  5. When the user clicks on a star, the hidden product ID and rating fields will be set. The form will be submitted and part B will handle the rating update.

 

 

3B) THE JAVASCRIPT

3b-products.js
var stars = {
  // (A) INIT - ATTACH HOVER & CLICK EVENTS
  init : () => {
    for (let d of document.getElementsByClassName("pStar")) {
      let all = d.getElementsByClassName("star");
      for (let s of all) {
        s.onmouseover = () => { stars.hover(all, s.dataset.i); };
        s.onclick = () => { stars.click(d.dataset.pid, s.dataset.i); };
      }
    }
  },

  // (B) HOVER - UPDATE NUMBER OF YELLOW STARS
  hover : (all, rating) => {
    let now = 1;
    for (let s of all) {
      if (now<=rating) { s.classList.remove("blank"); }
      else { s.classList.add("blank"); }
      now++;
    }
  },
  
  // (C) CLICK - SUBMIT FORM
  click : (pid, rating) => {
    document.getElementById("ninPdt").value = pid;
    document.getElementById("ninStar").value = rating;
    document.getElementById("ninForm").submit();
  }
};
window.addEventListener("DOMContentLoaded", stars.init);

The final piece of the puzzle.

  • Remember the “hover to update the number of stars”? stars.hover() deals with that.
  • On clicking a star, stars.click() will set the selected product ID, rating, and submit the form.

 

 

EXTRA BITS & LINKS

That’s it for the simple project and example. Here are a few small extras that may be useful to you.

 

MUST USERS BE LOGGED IN?

Yes, it is best to keep the rating system to registered users only. It is otherwise difficult or impossible to identify who is who, it will be total chaos if we allow anyone to rate; It will become a spam magnet quickly. If you really have to open to the public, consider using the PHP session ID as the “unique user key”.

  • Change database user_id to VARCHAR.
  • session_start();
  • Use session_id() as the user_id.

 

STARS RATING FOR SEO

<script type="application/ld+json">
{
  "@context": "http://schema.org/",
  "@type": "Product",
  "name": "PRODUCT NAME",
  "image": "https://site.com/image.jpg",
  "description": "PRODUCT IMAGE",
  "sku": "IF ANY",
  "mpn": "IF ANY",
  "brand": {
    "@type": "Thing",
    "name": "BRAND NAME"
  },
  "aggregateRating": {
    "@type": "AggregateRating",
    "ratingValue": "4.5",
    "reviewCount": "2"
  }
}
</script>

Yes, we can actually add a small JSON-LD product snippet and include the average rating for SEO purposes. As this is kind of out-of-point for this tutorial, I will just point to:

 

THE END

Thank you for reading, and we have come to the end of this guide. I hope that it has helped you with your project. If you have anything to add to this project, please feel free to comment below. Good luck and happy coding!

12 thoughts on “Simple Star Review Rating In PHP MySQL (Free Download)”

  1. Hello,
    thanks for this great code.
    I use the ranking to set the destination of the next bike tour with my friends. There are 5 tours to choose from.
    Since the bike tours page is not public, I don’t want to use a (user_id) identification, but the “session_id” instead.
    Unfortunately I don’t know where and how to use the “session_start()” command. Can you help me on this.
    Thank you very much and good luck
    Helmut

  2. I am getting an error “SQLSTATE[23000]: Integrity constraint violation: 1048 Column ‘user_id’ cannot be null” do you know why this is?

  3. Muy buen trabajo de explicación, pero si me permites ( desde españa) deberia verse en el producto las estrellas mas votadas , si 5 estrellas tienes 30 votos y 1 estrella un voto , no debe verse al entrar el ultimo voto efectuado sino el numero de estrella que tenga mas votos.

    1. Sorry, I don’t speak Spanish, and Google translate is not making a lot of sense… I am guessing you want to show the past ratings on products, sorted by the number of stars –

      SELECT * FROM `star_rating` WHERE `product_id`=? ORDER BY `rating` DESC

Leave a Comment

Your email address will not be published. Required fields are marked *