Follow

Welcome to TechYari!

Tutorials on webdesign, web development and more...

Rating System in PHP with jQuery, Ajax & MySql Database



By  Vipin Dubey     11/04/2014    Labels:,,,, 
In this tutorial I am going to show you how to build a rating system in php using jQuery, Ajax and MySql. We will be using jQuery to show rating stars and we will also make ajax request when user clicks on a star to rate an article. MySql databse is being used to store our article and related rating data for each article.

To start we need to create two tables, one for storing articles and another for storing ratings for those articles by different users. In order to keep it simple I am leaving the user part. So we will have anonymous ratings ( Just to keep it simple ).


Download Source Live Demo


Setting Up Tables for article and ratings


Below is the script to create article and article rating table. Run this script in your database and populate article table with few entries manually to test.


CREATE TABLE article(
   id INT NOT NULL AUTO_INCREMENT,
   text VARCHAR(200) NOT NULL,
   PRIMARY KEY (id)
);

CREATE TABLE article_rating(
   article_id INT NOT NULL,
   rating INT NOT NULL
);

Configuring PHP to connect to database (database-config.php).


Create a PHP file and use below code to connect to MySql from PHP. Change variable values according to your environment.


<?php
   // define database related variables
   $database = 'your_database_name_here';
   $host = 'localhost';
   $user = 'root';
   $pass = '';

   // try to conncet to database
   $dbh = new PDO("mysql:dbname={$database};host={$host};port={3306}", $user, $pass);

   if(!$dbh){

      echo "unable to connect to database";
   }
   
?>

Inserting user ratings to database via PHP (rate.php).


To rate each article we will store article id and article rating to previously created article_rating table. Here we receive article id and rating from an ajax GET request.


<?php 
 require 'database-config.php';

 $id = "";
 $rating = "";
 
 if(isset($_GET['id'])){
  $id = $_GET['id'];
 }
 if (isset($_GET['rating'])) {
  $rating = $_GET['rating'];

 }
 
 

 $q = 'INSERT INTO article_rating(article_id,rating) VALUES (:id,:rating)';

 $query = $dbh->prepare($q);

 $query->execute(array(':id' => $id, ':rating' => $rating));


 if($query->rowCount() != 0){
  echo "rated successfully";
 }else{
  echo "There was an error";
 }


?>

PHP code to get articles and their ratings from database.


Create an index.php file and paste following code at top section before html tag. The code listed below gets all the articles and their ratings from the database and saves it into an array. We will be using this array to show them later (index.php).


<?PHP 
    
      require 'database-config.php';
      // Get all the articles from the database
      $q = 'SELECT * FROM article';
      $query = $dbh->prepare($q);
      $query->execute();


    // Iterate for each article returned via query  
    $articles = array(); 
    
    while($row = $query->fetch(PDO::FETCH_ASSOC)){
         $data = "";
         $id = $row['id'];
         $text = $row['text'];
         $average = 0;
         
         // Get total count of rating for an article
         $q1 = 'SELECT count(*) FROM article_rating where article_id=:id';
         $query1 = $dbh->prepare($q1);
         $query1->execute(array(':id' => $id));
         $row1 = $query1->fetch(PDO::FETCH_ASSOC);
         $count = $row1['count(*)'];
         
         if($count > 0){
             // Get all ratings for an article
             $q2 = 'SELECT rating FROM article_rating where article_id=:id';
             $query2 = $dbh->prepare($q2);
             $query2->execute(array(':id' => $id));
             
             // Sum up the ratings 
             $rating = 0; 
             while($row2 = $query2->fetch(PDO::FETCH_ASSOC)){
               $rating = $rating + $row2['rating'];
             } 
             
             //Calculate average rating by deviding the sum by total ratings and rounding the result. 
             $average = ceil($rating/$count);
         }else{

             $average = 0;
         }
         
         $data = array($id,$text,$average);
         //Store each article and related rating in articles array
         $articles[] = $data;
    }
   
?>

Code to show articles on homepage (index.php).


Now use following code between body tags to show articles in your index.php file. I am using twitter bootstrap for alignment purpose. Please include that as well in your index.php file to get desired results or you can also style them yourself.


       <?php for($i = 0; $i<sizeof($articles);$i++){ ?>

                      <div class="article row" article-id="<?php echo $articles[$i][0] ?>">
                        <div class="id col-md-2"><?php echo $articles[$i][0] ?></div>
                        <div class="text col-md-7"><?php echo $articles[$i][1] ?></div>
                        <div class="rating col-md-3 <?php echo $articles[$i][2] ?>"><?php echo $articles[$i][2] ?></div>
                      </div>

                     <?php } ?> 

Styling star ratings (style.css).


Create a css file and paste below code, save with any name and include the file in your index.php file created earlier


.info{text-align: center;}
.row{padding: 10px;
background: beige;
margin-bottom: 5px;
font-size: 16px;
font-weight: 600;}
h2{padding: 10px;}
.gray{color:rgb(189, 187, 187)}
.red{color:red;}

Creating star icons for rating, applying hovers styles for stars and making ajax request when user selects a rating. (main.js)


Create a javascript file and paste below code, you also need to include this file in your index.php file.
Here we achieve three things
1. We apply star icons using bootstrap classes based on rating numbers.
2. When user hovers over a star to rate an article we highlight the stars by checking the star position and show him a nice effect using jquery
3. Lastly when user selects a rating by clicking on a star we make an ajax request with jquery and insert the data into our article_rating table.


$(document).ready(function(){

 // Apply style based on ratings from database

 $('.5').html('<span class="glyphicon glyphicon-heart" rating="1"></span><span class="glyphicon glyphicon-heart" rating="2"></span><span class="glyphicon glyphicon-heart" rating="3"></span><span class="glyphicon glyphicon-heart" rating="4"></span><span class="glyphicon glyphicon-heart" rating="5"></span>');
  $('.4').html('<span class="glyphicon glyphicon-heart" rating="1"></span><span class="glyphicon glyphicon-heart" rating="2"></span><span class="glyphicon glyphicon-heart" rating="3"></span><span class="glyphicon glyphicon-heart" rating="4"></span><span class="glyphicon glyphicon-heart gray" rating="5"></span>');
   $('.3').html('<span class="glyphicon glyphicon-heart" rating="1"></span><span class="glyphicon glyphicon-heart" rating="2"></span><span class="glyphicon glyphicon-heart" rating="3"></span><span class="glyphicon glyphicon-heart gray" rating="4"></span><span class="glyphicon glyphicon-heart gray" rating="5"></span>');
    $('.2').html('<span class="glyphicon glyphicon-heart" rating="1"></span><span class="glyphicon glyphicon-heart" rating="2"></span><span class="glyphicon glyphicon-heart gray" rating="3"></span><span class="glyphicon glyphicon-heart gray" rating="4"></span><span class="glyphicon glyphicon-heart gray" rating="5"></span>');
     $('.1').html('<span class="glyphicon glyphicon-heart" rating="1"></span><span class="glyphicon glyphicon-heart gray" rating="2"></span><span class="glyphicon glyphicon-heart gray" rating="3"></span><span class="glyphicon glyphicon-heart gray" rating="4"></span><span class="glyphicon glyphicon-heart gray" rating="5"></span>');
      $('.0').html('<span class="glyphicon glyphicon-heart gray" rating="1"></span><span class="glyphicon glyphicon-heart gray" rating="2"></span><span class="glyphicon glyphicon-heart gray" rating="3"></span><span class="glyphicon glyphicon-heart gray" rating="4"></span><span class="glyphicon glyphicon-heart gray" rating="5"></span>');



 // Appy style on hover by user

 $("span[rating='1']").hover(function(){
  console.log("selected 1");
  $(this).addClass('red');
  },function() {
        $( this ).removeClass('red');
     }
 );

 $("span[rating='2']").hover(function(){
  console.log("selected 1");
  $(this).prev().addClass('red');
  $(this).addClass('red');
  },function() {
   $( this ).prev().removeClass('red');
        $( this ).removeClass('red');
     }
 );

 $("span[rating='3']").hover(function(){
  console.log("selected 1");
  $(this).prev().prev().addClass('red');
  $(this).prev().addClass('red');
  $(this).addClass('red');
  },function() {
   $( this ).prev().prev().removeClass('red');
   $( this ).prev().removeClass('red');
        $( this ).removeClass('red');
        
     }
 );

 $("span[rating='4']").hover(function(){
  console.log("selected 1");
  $(this).prev().prev().prev().addClass('red');
  $(this).prev().prev().addClass('red');
  $(this).prev().addClass('red');
  $(this).addClass('red');
  },function() {
   $( this ).prev().prev().prev().removeClass('red');
   $( this ).prev().prev().removeClass('red');
   $( this ).prev().removeClass('red');
        $( this ).removeClass('red');
        
     }
 );

 $("span[rating='5']").hover(function(){
  console.log("selected 1");
  $(this).prev().prev().prev().prev().addClass('red');
  $(this).prev().prev().prev().addClass('red');
  $(this).prev().prev().addClass('red');
  $(this).prev().addClass('red');
  $(this).addClass('red');
  },function() {
   $( this ).prev().prev().prev().prev().removeClass('red');
   $( this ).prev().prev().prev().removeClass('red');
   $( this ).prev().prev().removeClass('red');
   $( this ).prev().removeClass('red');
        $( this ).removeClass('red');
        
     }
 );
 
    // Making ajax request when user selects a rating for an article.
 $('.glyphicon').click(function(){

  var rating = $(this).attr('rating');
        var id = $(this).parent().parent().attr('article-id');
  console.log(rating);
  console.log(id);

  $.ajax({
       url: "rate.php",
       type: 'GET',
       data: {id : id,rating:rating},
       success: function(data) {  
       console.log(data); 
       location.reload();
   },
      error: function(e) {
      console.log(e); 
  }
 });
 })

});

That's it we have build a rating system in php using jQuey and Ajax. If you run into any issues you can download the source code from here
I hope you enjoyed this article. Please share your thoughts and queries using comment box below the article.

About Vipin Dubey

A web developer, blogger, amateur designer and an open source enthusiast.

No comments:

Post a Comment


Contact Form

Name

Email *

Message *

Labels

Follow TechYari on

Translate

About TechYari

Tech Yari is a Technical Blog featuring articles on Web Development, Web Design, Search Engine Optimization, Social Media Marketting and Programming.