slim

Creating a star rating system part (3)

Jan. 5, 2006

In part 2 I show how you could make a star rating system using php and some nifty css Now I want to show you the files that make it all work with a mySQL database. I am also going to show how, with a few minor changes, this can become an independent reusable system. First lets create the table on the database to hold the info for the rating system.

CREATE TABLE ratings(
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	total_votes INT NOT NULL,
	total_value INT NOT NULL,
	which_id INT NOT NULL,
	which_table VARCHAR(255),
	used_ips LONGTEXT NULL
	);

Now you may look at me and say "hey, in the last tutorial you are updating the actual article table, why are you making a new one". The answer is that this approach makes the system stand on its own. With just a minor change to the code we turn this from dependent to independent.

The above code run through a mySQL query will create a table with those columns. This is only needed once per database. This will hold all of the ratings in a table that can be cross checked with article, gallery or other tables to keep track of rating items. Example: a database with two tables; one articles the other ratings. The star ratings page is included in the article page and when someone votes, that vote and ip address is tallied and fed into the ratings table. When the page reloads the rating is then pulled out of the database according to 'which_id' and 'which_table'.

To make this stand on its' own there only needs to be a few small changes.

mysql_query("UPDATE $tableName SET 
total_votes='".$added."', total_value='".$sum."', 
used_ips='".$insert."' WHERE id='".$_GET['id']."' 
AND which_section='".$_GET['section']."'");

Add 'which_section' to the update call and of course to the insert call. I also need to change the url that sends the ratings variable so that it shares what section this belongs to.

<li><a href="#" title="Rate this 1 star out of 5" 
class="one-star" onclick="location='tutorial.php?section=
articles&id=<?php echo $_GET['id'] ?>&vote=1'">1

Below I have linked some common terms to php.net and mysql.com so you can read about what they do to get a better understanding of how they work.

$rating_posted=$_GET['vote'];
//gets the passed voted variable by the stars value

$id=$_GET['id'];
$query=mysql_query("SELECT * FROM 
$tableName WHERE id='".$id."' ")
or die(" Error: ".mysql_error());

$numbers=mysql_fetch_assoc($query);

mySQL databases cannot store an array unless it is coded in a particular way. To keep the ip addresses in the same place I serialized them on the insert -

$count=$numbers['total_votes'];
//how many votes total
$current_rating=$numbers['total_value'];
//total number of rating added together and stored

Throughout the process of calling on the database for information I fed the array into a variable. These variables allow you to call on the data by its column name in the database, making it a breeze to access data.

$insert= serialize($checkIP);
mysql_query("UPDATE $tableName SET 
total_votes='".$added."', total_value='".$sum."', 
used_ips='".$insert."' WHERE id='".$_GET['id']."'
AND which_section='".$_GET['section']."'")");

and then unserialize the array to access the information for ip address comparison.

$checkIP=unserialize($numbers['used_ips']);
//array of ip address that have already voted

There are many handy build in functions of php, one of which is globals, these items make life easier when trying to gather host, form, server or session information from or about a user.

$current_ip=$_SERVER['REMOTE_ADDR'];
//gets the ip address of the current user

This next item is called a ternary conditional. You will love these babies, they are basically a short hand for writing an if/else statement.

$tense=($count==1) ? "vote" : "votes";
//plural form votes/vote

Lastly but not least of all I used number_format built in function of php to give me a nice neat number that I can display anyway I want. Remember how back in the day you had to multiply by some number and divide by another to get 'pretty' floats, well not any more. Simply feed in the float and tell it how many decimal places to display and voile. You'll also notice the '@' symbol in front of each call I make to this function. The reason is that when the rating system is on its first call the numbers associated with it are all zero. This function throws an error if you try to divide zero by zero, so I suppress the error using the '@' symbol.

@number_format($sum/$count,2)

---