A Walk Through the Ratings System Code
Let's take a detailed look at the code. The first function starts off by setting the number of book details that are going to be shown on each page, the value is set to one.
Then we run a query that will give us the count of the total books in the database. It is important that we don't limit this query in anyway.
We then store the results in a variable called
Count(*)keyword. Basically, we want a result set that is ordered sequentially by
We then add a
LIMIT clause to the query. The
result_set querystring that you see is passed on to the actual script by this function. It will contain the next set of results from the total results that we retrieved in the previous query. If the result set variable does not contain any values, we set it to zero and append it to the SQL query.
Otherwise, we store the given value in the
result_set variable and adjust the SQL query accordingly.
When the query is built, we run it with the given
LIMIT to get the final result and store it in an array for easy access. Remember that the book information will be retrieved by the user from this array:
Now that we have all the information stored in the array, we have to access it. We access the stored information by running a for loop...
...and then extracting the information as required. We need the following information about the books:
- Book title
- Book description
- Book ID
All of this information is stored in the
$sql_array variable. So we retrieve it:
We need to show all of this data, so we display it in a table. Below, we start to build the table that is going to host the retrieved information.
Then we create the Next/Previous links, which is a bit tricky to do. First, we need to determine if the total records is more than zero, because if it is zero then there are no records:
Then we check to see if the result set value is less than the total amount of records:
If so, we subtract the total number of records (books) that we want to show on the page from the result set value and store the remainder in a variable called
$res1. Then we create a hyperlink with the 'previous link'.
Next, we calculate and display the page links.
Now we check to see if the page value (calculated above) is greater than one.
We then run a for loop that will give us the number of books that we still need to go through before the total results are completed.
Finally, we show the link below. I've disabled the hyperlink, but if you want to show the number of books that are left to view, then simply enable this line of code:
Now we need to determine if the 'Next' navigation link should be shown or not. First, we check to see if the result set is greater than or equal to zero or if it is less than the total amount of results that we extracted in our previous query.
If the value that is stored in the
res1 variable is less than the total, then the 'Next' link is created, because there are still some books left that the user can access.
Finally, the function returns the ID of the book concerned.
Figure 2 shows a screenshot of what the function produces.
Since we've already looked at the other functions of the above script, we will now explore the remaining code. The next function is responsible for presenting the ratings table. The function basically creates an HTML form that is hosted by an HTML table.
First, the form tag is created. Notice that the
action attribute of the form is assigned to a PHP global variable called
$_SERVER['PHP_SELF']. This variable tells the form to send its data (i.e. the data collected from the user) to the page from which it is called. For example, if the function is called in a script called form.php then the form data will be sent to that page.
Then the code starts to build the HTML table that will host the rating form elements. The form itself contains mostly radio buttons. Each button has a ratings number ranging from 5 (Excellent) down to 1 (Poor). The only other significant form element that is the hidden form field, which we will see later.
The hidden form field below will store the book ID that is passed on from the
The table and form elements are then closed.
Figure 3 shows a screenshot of the form that is produce by the function.
The next two functions deal with inserting data into the database. The
insert_rating() function is of course responsible for inserting ratings information into the database. Although the ratings database table has more than two fields, the function itself has only two parameters: the
bookid and rating value. The other required information is automatically inserted by the function.
$currdate variable below is responsible for storing the current date. Currently the date format is set as
yyyy-mm-dd, but with the
date() function you can change it to any format that you want.
We then continue to create the SQL query that will be responsible for adding the rating to the database. Notice that we use MySQL's
NOW() function to add the date. This is because MySQL's date format is
yyyy-mm-dd, so remember to format it like that.
Now we run the query.
insert_book_details() function works pretty much in the same way that the
insert_rating() function does; it is responsible for adding new book information to the books table.
The code starts by building the query and then runs it, using the
mysql's mysql_query() function.
rankings() function is responsible for calculating and ranking the different books based on the ratings that it received over a period of time.
The SQL is created using MySQL's
AVG() function, which is used to calculate the average of a given field. In our case, we want it to calculate the average for the
rating_val table field, so we write:
This is exactly what the SQL query below does, along with limiting the number of rankings that it wants to show to three.
Next, we store the results of the query in the
Now we need to present the rankings in a neat and clear way to the user, but only if the query returned any results. We check for this by testing to see if the
$res variable contains anything.
If so, we begin to build the table. It will show two pieces of information: the book title and ranking. The table itself has both static and dynamic parts to it. The static parts contain the headers for the table and the dynamic part will show the book title and rank.
To make this part of the table dynamic, we run a while loop that will list the different books with their titles and rank as the loop iterates through the results of the query that is stored in the
After dynamically creating all the rows, the table is closed.
The Remaining Scripts
There are three scripts remaining for this application. We are going to start with a code listing of the raninkin.php script. This script is responsible for showing the user the rankings of the books that are contained in our database.
The code for the script is very simple, because most of the work is done by the
ranking() function that is included in the functions page. It starts off by including the database connection and functions files.
The HTML is then started.
Next, the navigation panel is created.
rankings() function is called to show the current rank of all the books in the database.
The next script (newbook.php) enables a user to add a new book to the books database table.
The newbook.php script is about as complicated as it is simple. So let's take a look at the code. First, the HTML page is started.
Then we get to the PHP code portion of the application. When the form data is collected and sent, the PHP section of the page immediately springs into action.
First, we validate the form data. We do this by checking to see if the form variables are empty or not. They should not be.
Because all the data that is collected from the form should be of type string, we test the data by using the
is_numeric() function. You can use the
is_string() function here.
If we are satisfied that there are no errors, then we do one final "clean" of the variables and then post them to the database.
Below is the form that is responsible for collecting the information about the new book.
The PHP code below is where error messages are going to be displayed.
The remaining parts of the form simply lay out the different fields that collect the required information.
That's it for the custom user rating system. The application on the whole is generic in nature, so some improvements and customizations can still be made with some tweaks here and there. For example, this article talks only about books. You could add a page that enables users to select other items, such as music CDs or videos, and present them with a ratings box for each of the individual items. This way you will be able to present users with different types of products or items that they can rate.
Original: Dec. 22, 2010