Codular

HomeWriters RSS

Part 2: Build a Rating System

Introduction

In part 1 of this tutorial we covered how we'd create the frontend of this rating system, using jQuery to alter the stars that appear and to send off the storage request asynchronously. Here we'll cover the backend of the rating system from the database structure through to the JSON output with PHP.

We're only concentrating on one rating system at the moment, which works for one rating interface, not for multiple. To support multiple you could add an 'id' column to the database that you'd also send through with the jQuery on the frontend.

MySQL

We're not going to be using anything complex here with the table, the table structure will list one row per rating by each user, we'll have a unique index set up on the IP so that someone can only vote once.

Note: Here we're storing the IP address as a string, you will want to look into storing it as an integer using one of the many IP to Integer functions that there are around this will speed up searching speeds etc.

CREATE TABLE `rating` (
    `ratingid` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `rating` FLOAT UNSIGNED NOT NULL,
    `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `ip` VARCHAR( 39 ) NOT NULL,
    UNIQUE (`ip`)
) ENGINE = INNODB

Nothing too complicated here with the database, now to head to the PHP.

PHP

Here we'll be using MySQLi for all of our database interactions, the flow of the script that we use will follow the below logic:

  1. Check that all fields are provided and that the rating is a numeric value.
  2. Check if the user has rated before.
  3. Store the user's rating.
  4. Return average rating.

Error output

We decided in part 1, that our result JSON would be in the below format:

{ 
    result: 'error',
    msg: 'Unable to add rating - unknown error.'
}

We therefore will write a really simple error function that will handle any error and output it how we want:

function error($message){
    $output = json_encode(
        array(
            'result' => 'error',
            'msg' => $message
        )
    );
    die($output);
}

Here we're using the json_encode method that takes an array as the first parameter, and we'll just simply build up an array with the error message and then echo out the JSON string.

Check the provided fields

Firstly we'll check that we're getting the number of items sent to the script that we want:

if(count($_POST) > 1){
    error('Too many post items received.');
}

Secondly, we want to actually check that we got the item that we want and that it's not blank:

if(!isset($_POST['rating']) || $_POST['rating'] == ''){
    error('No rating value provided.');
}

Finally, we'll check that the value we've received is actually a number that we want, we could do this using is_numeric(), or we could do it using a regular expression and preg_match(). We will use a mixture of methods to make 100% that we have a number in the format that we want, and not with any erroneous parts.

if(!preg_match("/[0-5](?:\.5)/", $_POST['rating']) && $_POST['rating'] < 0 && $_POST['rating'] > 5){
    error('Invalid rating provided.');
}

You'll see we're using a check to also ensure that the value is no less than 0 and no greater than 5.

Check if the user has rated before.

We will simply be running a SELECT query with the IP address of the user and checking the number of returned rows, which if greater than 0 will mean that the person has already voted.

Firstly we need to get the IP address of the user, for that we'll use the line $_SERVER['REMOTE_ADDR'], next we'd us a query just like this:

SELECT `ratingid`
FROM `ratings`
WHERE `ip` = '{$_SERVER['REMOTE_ADDR']}'

If the user has rated before we will simply output an error message saying that the user has already voted:

error('That IP address has already voted, please try using another IP.');

Store the user's rating

This is nothing complex, just a simple INSERT with the value of the rating that the user chose, don't forget to always escape values when using them in a query.

$rating = $db->escape_string($_POST['rating']);

We can then use the following SQL:

INSERT INTO `ratings`
(`rating`, `ip`)
VALUES ('{$rating}', '{$_SERVER['REMOTE_ADDR']}')

Once we've inserted the value, the last thing that we need to do is pull out the average overall rating to return back to the front end.

Return average rating

We can do this by using the built in MySQL function AVG() with some really simple SQL of:

SELECT AVG(`rating`) AS `average`
FROM `ratings`

The above SQL will cast the average rating to the column named average. However, this won't be rounded to the nearest 0.5 like we want it for the front end to work. Here we'll write some quick code to round to the nearest 0.5, in a similar manner that we did on the front end:

function roundToNearestHalf($number){
    return round($number * 2) / 2;  
}

Nothing too complicated here, we just simply now return the number to the front end in the required format that we wrote the front end to handle:

$output = json_encode(
    array(
        'result' => 'success',
        'rating' => roundToNearestHalf($rating)
    )
);
echo $output;

Conclusion

A rating system is not something that should be complex to write, and this hopefully cleaned up some of the minor issues that might be had when writing one. However, this is by no means complete, and there is always room to expand on what is laid down above. If you integrate a rating system using this tutorial, why not let us know on Twitter.

You will want to look into pulling out the average rating and presetting the front end rating system to show the current average rating by setting the relevant class on the stars element.

The complete PHP code is visible below:

<?php

// Error function that stops script processing with die
function error($message){
    $output = json_encode(
        array(
            'result' => 'error',
            'msg' => $message
        )
    );
    die($output);
}

// Round to the nearest 0.5
function roundToNearestHalf($number){
    return round($number * 2) / 2;  
}

// Instantiate database connection
$db = new mysqli('localhost', 'root', 'password', 'rating');

// Check that the connection worked
if($db->connect_errno > 0){
    error('Unable to connect to database [' . $db->connect_error . ']');
}

// Verify that we have enough post items
if(count($_POST) > 1){
    error('Too many post items received.');
}

// Check that the rating was entered
if(!isset($_POST['rating']) || $_POST['rating'] == ''){
    error('No rating value provided.');
}

// Valid the rating amount that was entered.
if(!preg_match("/[0-5](?:\.5)/", $_POST['rating']) && $_POST['rating'] < 0 && $_POST['rating'] > 5){
    error('Invalid rating provided.');
}

// Check if the user has rated before
$sql = <<<SQL
    SELECT `ratingid`
    FROM `ratings`
    WHERE `ip` = '{$_SERVER['REMOTE_ADDR']}'
SQL;

if(!$result = $db->query($sql)){
    error('There was an error running the query [' . $db->error . ']');
}

// Tell the user that they have voted already.
if($result->num_rows){
    error('That IP address has already voted, please try using another IP.');
}

// Store the user's rating.
$rating = $db->escape_string($_POST['rating']);

$sql = <<<SQL
    INSERT INTO `ratings`
    (`rating`, `ip`)
    VALUES ('{$rating}', '{$_SERVER['REMOTE_ADDR']}')
SQL;

if(!$db->query($sql)){
    error('Unable to insert rating to database [' . $db->error . ']');
}

// Get the average rating
$sql = <<<SQL
    SELECT AVG(`rating`) AS `rating`
    FROM `ratings`
SQL;

if(!$result = $db->query($sql)){
    error('There was an error running the query [' . $db->error . ']');
}

// Fetch the average rating
$data = $result->fetch_assoc();

$rating = $data['rating'];

// Output the average rating for the front end to handle
$output = json_encode(
    array(
        'result' => 'success',
        'rating' => roundToNearestHalf($rating)
    )
);
echo $output;
Tags: PHP, MySQL