DEVseo

Edit

This post is now very old and the methods within have now been deprecated. Please reference my  newer article to see an updated version of the best way to get information from a database using PHP PDO.

If you have been to this blog before you may know that I have released a script written in JavaScript that allows you to easily add star ratings to anything you require. You can find that blog here.

However, in that blog I didn't go through the steps required to actually save the rating. After being contacted asking for help on the matter and wanting to write an nice introduction to PHP and MySQL I felt that this would be a good time to kill two birds with one stone. So, read on to learn how to write to and read from a MySQL database with PHP (and how to incorporate that into my Star Rating script.

Starting With PHP

If we're starting from the very beginning I guess it would be beneficial to make sure that you have everything you need before you start using the tutorial. You will obviously need some form of web server that is running both MySQL and PHP (whether it be on a hosting package or your local computer). I'll not go into details here but if you do not have this, or do not know how to set this up yourself check this tutorial.

Once you have something to work with we can begin! I'm going to base this tutorial on the presumption that you know some HTML and therefore know some of the terminology of the web. All PHP scripts must begin by opening a PHP 'tag' and ending with a closing PHP 'tag'. An example of this is:

<?php
	// Your PHP code goes here
?>

You can omit the 'php' bit from the opening tag, but for semantics sake I have always used it. I'm not going to go into a lot of detail here because there is a hell of a lot to learn about PHP but the basics should get you by for this purpose. The // are a way of commenting in PHP, just like JavaScript, and can be used to comment a line. Other ways to comment in PHP include /* comment */, which can be used for multiple-lined comments, or #, which acts the same as two forward slashes.

PHP Variables

A variable in PHP is pretty much the same as a variable in JavaScript. It contains a value that it has been assigned that cannot start with anything but a letter and must contain only alphanumeric characters, but other than that it can be any string. Unlike JavaScript, however, PHP variables begin with a $ rather than the word 'var':

<?php
	$myvar = 'a string';
?>

In the above example the variable $myvar contains the string 'my string'. You can reference this string anywhere later in the script by simply typing $myvar. For example, in PHP there are a few ways to actually print data to the browser including print and code. If you wanted to show the value of this variable to someone looking at your site you would do something like:

<?php
	print $myvar;
?>
or
<?php
	echo $myvar;
?>

Database & MySQL

We'll leave PHP for now and go through a database quite quickly. There are a couple of ways of reading and writing to databases. You can use various programming languages, a command line or (by far the easiest method to start with), phpMyAdmin. The great thing about phpMyAdmin is that it has an easy-to-use user interface allows you to access and update data with a few clicks. I'm going to assume that you have a database set up for this tutorial and we'll go through the steps of adding a tables and reading from and writing to this 'ratings' table.

The first thing you need to do in phpMyAdmin is select your database (if you have more than one), then click the 'SQL' link. The following page allows you to run SQL commands on your database to perform such actions as searching, adding, removing and updating tables in that database. What we're going to do is create a 'ratings' table to store the information for the ratings on your page.

All you need to do is copy the following and paste it into the text box on the SQL page in your database:

CREATE TABLE IF NOT EXISTS `ratings` (
  `rating_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'The id of the rating',
  `rating` int(11) NOT NULL COMMENT 'The rating for the page',
  `rating_page_id` int(11) NOT NULL COMMENT 'The id of the page to be rated',
  `rating_page_title` varchar(512) NOT NULL COMMENT 'The title of the page to be rated',
  `rating_user_id` int(11) NOT NULL COMMENT 'If a user is signed in, get their id',
  PRIMARY KEY (`rating_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0

This might look a little scary to start with but the great thing about SQL is that it is a sequential language, which means it basically does what it says on the tin! For example, consider the top line CREATE TABLE IF NOT EXISTS `ratings`. This line does exactly what it reads, creates a new table called ratings if it doesn't already exist! As long as that ran without errors you should now see a new table appear in the left hand pane on phpMyAdmin.

Contacting The Database in PHP

Okay, we've got the MySQL table set up, and we know a little about PHP (don't worry, you'll learn more as we go along). Now we need to know how the PHP script can contact the database. I will be assuming that your script and database are both running on the same server and therefore you can contact the database from PHP using 'localhost' (more about this later). If you are not you may have to change 'localhost' to the IP address of your database and ensure that you have permissions to access it from your script.

Consider the following code, which is a very simple PHP script that connects to a database:

<?php
$host = 'localhost';
$user = 'your username';
$pass = 'your password';
$dbname = 'database name';
$dbc = @mysql_connect($host, $user, $pass) OR die('Could not connect to MySQL: '. mysql_error() );
@mysql_select_db($dbname) OR die ('Could not select the database: '. mysql_error() );
?>

The first 4 lines set up some variables which the PHP functions mysql_connect and mysql_select_db use to connect to your database and then select the database that has the table you want in. You will obviously need to change these variables to the ones you use to connect to your local database. The @ symbol before the PHP functions just described simply suppress errors being sent to the browser in case the script can't connect to the database. The last thing you want is for horrible error messages to be shown to the user if your script can't connect to the database for whatever reason.

If everything was successful the variable $dbc should now contain a reference to the database connection which we can later use to write to and read from the database. The second part of those last 2 lines OR DIE sound a little evil but all they do is, if the database could not be contacted, or the table could not be found an error message will appear and it can say whatever you want it to. You could even use a redirect by changing this a little, but I won't go through that now.

As was mentioned above, if your database is on the same server as your script is running you should be able to access the database by setting the host to simply 'localhost', as I have done here. However, you will need to change this to the address, or IP of your database if it exists elesewhere.

Voila, we should now have a database connection from our PHP script to our database. Now the fun begins!

Saving A Rating To The Database

In the Star Rating Script for YUI when you click a star it will get the action from the rating form (which is hidden by the javascript), set all of the information into a post method and post that data to the URL in the action of the form (appending a variable called XHR so we can check whether it came from an ajax request or not).

What we want to do is make sure that our action set on the form points to the URL of our new PHP script. As long as it is we are all set to start saving ratings to the database.

Upon clicking a star an ajax request sends the data to your script. In your script we need to capture the details sent from the page and save them to the database. Because the method of the form is set to 'post' any data that is sent to the server is available via the global variable $_POST. So, if you have an input with a name of rating $_POST['rating'] will be the value of that rating. For example:

$rating = isset($_POST['rating']) ? $_POST['rating'] : null;
$pageId = isset($_POST['page_id']) ? $_POST['page_id'] : null;
$pageName = isset($_POST['page_name']) ? $_POST['page_name'] : null;
$xhr = isset($_GET['xhr']) ? true : null;

The first 3 lines get the values from the post array and cast them to variables, which (in my example) will give us the rating that was cast, the page id of the page that was rated and the page name. You probably don't need all this information, and you can change the form to add whatever information you need, such as a post id for blogs etc. The final variable tells us whether the response is from an ajax request, or a normal form submission. The reason we use $_GET for the xhr, rather than $_POST is because this value was appended to the form action and is therefore not in the posted values.

So, we have the values (some error checking should probably be done here but I'll leave that for you to research) now we need to save them to the database. We have our connection script which has given PHP access to our database. Now we need to do something like the following:

// Create our query
$query = "
INSERT INTO
	ratings
	(rating, page_id, page_name)
VALUES
	($rating, $pageId, '$pageName')
";
// Run the query on the database and get a response
$result = mysql_query($query);
if(mysql_affected_rows($result) === 1){
	print "Success";
} else {
	print "Failed";
}

Those few lines are all you need to actually save a rating to your database (as noted earlier some names may change for you). One thing to note here is that you must ensure any string that is passed in the VALUES part of that code has inverted commas around it. Strings must be surround by inverted commas, but integers do not need them (although you can add them if you wish). Knowing which field requires which type will depend on how your table in the database is set up. Just ensure that anything that requires just a number is an integer and anything that is a string is a varchar (variable character [0-0][a-Z]).

In order to get the rating (and then average it to show the average rating for the page) you will need a query that looks something like the following:

// Create our query
$query = "
SELECT
	rating
FROM
	ratings
WHERE
	page_id = '$pageId'
";
// Get the rows that match from the database
$result = mysql_query($query);
// Create an array for our results to be stored in
$ratings = array();
while($row = mysql_fetch_array($result)){
	// For each row, add to the ratings array
	$ratings[] = $row['rating'];
}

We now have an array that contains all of the ratings for the current page. Please note that in order to add to an array it is always best practise to define the array first. You can get all sorts of errors for not defining a variable before it is used. So, we have our ratings, what do we do with them now?

if(!empty($ratings)){
	$average = number_format(array_sum($ratings)/count($ratings), 1);
}

Because PHP has some very useful functions (although not one specifically for getting the average!) we can quite easily work out the average rating for a number of values by calling the above three functions. number_format simply formats a number to the specified number of decimal places (the number provided after the comma), array_sum gets the sum of the values in the array and count() counts the number of objects in the array. This should give us an average such as 4.6, or 2.8

All you need to do now is set this value to the title attribute of the form and when the page is loaded next the JavaScript will automatically set the correct number of stars highlighted based on this value.

A Little Housekeeping

That is pretty much it, but there is one more thing to go through. When a rating has been saved you may notice that the stars automatically show the new rating. That is because as a response from the ajax call, the JavaScript expects one of two values: a string that just says "Failed" or a number. If it finds a number as the return value it will update the stars according to this value (you will need to run a select query like the one above once you have saved the rating to get this value). If it finds a string saying "Failed" it will output a message to the user telling them that their rating has failed.

And that's it. If you have any comments or question, or need me to go over anything else that I've probably missed, please feel feel to contact me or leave a little comment in the section below.

Thanks for reading!