Ajax and XML: Ajax for ratings and comments

Add friendly rating and commenting features to your application with Ajax techniques

Jack D Herrington (jherr@pobox.com), Senior Software Engineer, Leverage Software Inc.

Jack D. Herrington is a senior software engineer with more than 20 years of experience. He's the author of three books: Code Generation in Action, Podcasting Hacks, and PHP Hacks. He has also written more than 30 articles. You can reach Jack at jherr@pobox.com.

Summary: In the age of the people-powered Web, allowing your readers to rate and review content on your site is critical. Discover just how easy it is to add rating and commenting features to a site with Ajax.

View more content in this series

Tag this!

Date: 24 Jul 2007
Level: Intermediate
Activity: 7448 views

We all love to rate stuff. I think it must be part of our DNA. My daughter and I like to go to the movies; while she used to like them all before, she's much pickier now that she's four. I've taught her to use the "thumbs up" and "thumbs down" rating system à la Ebert and Roeper. (She gave Shrek the Third a thumbs up.) I think this is why rating and reviewing things like products or articles on the Internet is so popular.

This article shows how to use a combination of MySQL, PHP, Prototype.js, and Asynchronous JavaScript™ and XML (Ajax) to add simple rating and comment functionality to any site.

The rating system

While my daughter likes thumbs up and thumbs down, I realize that most people work on a slightly more nuanced system: the five-star rating system, where one star is really bad, five stars are really good, and three stars are average. In this example, I apply the five-star rating system to movies. You're more than welcome to change my movies to whatever suits your needs — articles, products, podcasts.

Listing 1 shows the schema for the example, starting with a table for the movies. It's quite simple: an auto-incrementing movie ID and the name of the movie. Then a rating table holds each vote for the movie. This table is bound to the movie table through the movie_id and has one more field — the rating, which is a number between 1 and 5.

Listing 1. rating.sql

                
DROP TABLE IF EXISTS movies;

CREATE TABLE movies (
  movie_id INTEGER NOT NULL AUTO_INCREMENT,
  name VARCHAR( 128 ) NOT NULL,
  PRIMARY KEY ( movie_id )
);

DROP TABLE IF EXISTS ratings;

CREATE TABLE ratings (
  movie_id INTEGER NOT NULL,
  rating INTEGER NOT NULL
);

If you want to enforce "one person, one vote," you must add a user ID to the ratings table as well to ensure that the person logs only one vote per movie. I wanted to keep this example easy, so I left that part out.

To get the schema into the database, you must first create the database, then add the schema. You do this using the command-line directives:

% mysqladmin create comments
% mysql comments < ratings.sql

You might need to add login credentials depending on your MySQL installation.

To get things going, you need a page that displays the available movies and points to a rating page for each movie. Listing 2 shows this index page.

Listing 2. index.php

                
<html>
<body>
<?php
// Install the DB module using 'pear install DB'
require_once("DB.php");

$db =& DB::Connect( 'mysql://root@localhost/comments', array() );
if (PEAR::isError($db)) { die($db->getMessage()); }

$res = $db->query( 'SELECT * FROM movies' );
while( $res->fetchInto( $row ) )
{
?>
<a href="rate.php?id=<?php echo($row[0]) ?>"><?php echo($row[1]) ?></a><br/>
<?php
}
?>
</body>
</html>

To make it easy on myself, I added some famous movies to the movies table using Structured Query Language (SQL) statements, which you can get from Download. You can see the movies on the index page in Figure 1.

Figure 1. The movie list

If you don't get this listing from the example code, perhaps you haven't installed the DB module from the PEAR repository. To install it, simply run this command on the command line:

% pear install DB

Now, with the list of movies shown, it's time to implement the page that allows you to rate the movies as well as display the current vote tally and average ranking. Listing 3 shows how you do all this with the rate.php page.

Listing 3. rate.php

                
<?php
// Install the DB module using 'pear install DB'
require_once("DB.php");

$db =& DB::Connect( 'mysql://root@localhost/comments', array() );
if (PEAR::isError($db)) { die($db->getMessage()); }

$id = $_GET['id'];
$title = '';

$res = $db->query( 'SELECT name FROM movies WHERE movie_id=?', array( $id ) );
while( $res->fetchInto( $row ) ) { $title = $row[0]; }
?>
<html>
<head>
<title><?php echo($title); ?></title>
<script src="prototype.js"></script>
<script>
function rate( value ) {
        new Ajax.Updater( 'rating', 'ratemovie.php?id=<?php echo($id)?>&v='+value );
}
</script>
</head>
<body>
<h1><?php echo($title); ?></h1>

<div id="rating">
<img src="star_off.gif" onclick="rate(1)"></img>
<img src="star_off.gif" onclick="rate(2)"></img>
<img src="star_off.gif" onclick="rate(3)"></img>
<img src="star_off.gif" onclick="rate(4)"></img>
<img src="star_off.gif" onclick="rate(5)"></img>
<br/><br/>
<?php

$res2 = $db->query(
        'SELECT count( rating ), sum(rating ) FROM ratings WHERE movie_id=?',
        $id
);
while( $res2->fetchInto( $row ) )
{
?>
Votes: <?php echo($row[0]); ?><br/>
Average Rating: <?php echo($row[1]/$row[0]); ?>
<?php
}
?>
</div>

</body>
</html>

At the top of the file, you get the title of the movie given the ID passed in as a parameter. In the middle of the script, you include the prototype.js library and create a rate() JavaScript function that invokes the ratemovie.php page through Ajax using the Prototype library. Then, you add a set of star images, each of which has a script that invokes the rate() function if a star is clicked.

At the bottom of the file, you run a quick query to get the count of votes and the sum of the votes for the given movie. Then, you display that data to let the reader know how the movie is being reviewed.

The ratemovie.php script, in Listing 4, is responsible for adding a rating to the database, then returning some Hypertext Markup Language (HTML) code to replace the voting stars and the review count on the original page.

Listing 4. ratemovie.php

                
<?php
// Install the DB module using 'pear install DB'
require_once("DB.php");

$db =& DB::Connect( 'mysql://root@localhost/comments', array() );
if (PEAR::isError($db)) { die($db->getMessage()); }

$v = $_GET['v'];
$id = $_GET['id'];

$sth = $db->prepare( 'INSERT INTO ratings VALUES ( ?,? )' );
$db->execute( $sth, array( $id, $v ) );
?>
<img src="star_<?php echo( ($v>0)?'on':'off' ) ?>.gif"></img>
<img src="star_<?php echo( ($v>1)?'on':'off' ) ?>.gif"></img>
<img src="star_<?php echo( ($v>2)?'on':'off' ) ?>.gif"></img>
<img src="star_<?php echo( ($v>3)?'on':'off' ) ?>.gif"></img>
<img src="star_<?php echo( ($v>4)?'on':'off' ) ?>.gif"></img>
<br/><br/>
<?php
$res2 = $db->query(
  'SELECT count( rating ), sum(rating ) FROM ratings WHERE movie_id=?',
  $id
);
while( $res2->fetchInto( $row ) )
{
?>
Votes: <?php echo($row[0]); ?><br/>
Average Rating: <?php echo($row[1]/$row[0]); ?>
<?php
}
?>

The rating page is shown in action in Figure 2.

Figure 2. The rating page

When you click one of the stars, the vote is added to the database; the HTML code for the stars, the votes, and the average rating are changed to reflect the addition of the vote.

Ideally, I would like to be able to see all the scores for the movies on the home page. Listing 5 shows a new version of the index that achieves this goal.

Listing 5. index2.php

                
<html>
<body>
<table>
<?php
// Install the DB module using 'pear install DB'
require_once("DB.php");

$db =& DB::Connect( 'mysql://root@localhost/comments', array() );
if (PEAR::isError($db)) { die($db->getMessage()); }

$res = $db->query( 'SELECT * FROM movies' );
while( $res->fetchInto( $row ) )
{

$res2 = $db->query(
  'SELECT count( rating ), sum(rating ) FROM ratings WHERE movie_id=?', $row[0]
);
$rating = 0.0;
while( $res2->fetchInto( $row2 ) ) { $rating = $row2[1] / $row2[0]; }
?>
<tr><td align="center">
<?php echo( $rating > 0 ? $rating : 0 ) ?>
<td><td>
<a href="rate2.php?id=<?php echo($row[0]) ?>"><?php echo($row[1]) ?></a>
</td></tr>
<?php
}
?>
</table>
</body>
</html>

You can see the new version of the index, with ratings for each movie, in Figure 3.

Figure 3. The updated movies page

And that's all it takes to implement a voting scheme with Ajax, PHP, MySQL, and the super-handy Prototype.js JavaScript library.

In the next part of the example, you implement comments or reviews for the movies.

Commenting

Commenting systems on the Web vary from extremely simple — such as most blog commenting systems, which just have a string of comments to a blog entry — to very sophisticated, threaded commenting systems such as those you see on Slashdot.

This example goes with something on the simple end of the spectrum. You can tweak it depending on your needs.

Start with an additional table to add to the existing database schema, as shown in Listing 6.

Listing 6. comments.sql

                
DROP TABLE IF EXISTS comments;
CREATE TABLE comments (
  movie_id INTEGER NOT NULL,
  email VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL,
  comment TEXT NOT NULL
);

It's a comments table that hooks up to the movie through the movie_id field. Then, there's an e-mail address, a name for the commenter, and the text of the comment. This is a fairly rudimentary commenting system that is similar to what's available on blog software such as WordPress or MoveableType.

If you want to create a threaded commenting system, you would need to add an auto-incrementing ID to the table, then have a nullable parent_id field that points back to the parent comment for each comment. And if that field is null, the comment is a top-level comment.

To add commenting functionality to the rating page, you're just going to include some additional script at the bottom of the page. Listing 7 shows this new PHP code.

Listing 7. rate2.php

                
...
<h2>Comments</h2>
<div id="comments">
<?php
$res3 = $db->query(
  'SELECT * FROM comments WHERE movie_id=?',
  $id
);
while( $res3->fetchInto( $row3 ) )
{
?>
<div>
<a href="mailto:<?php echo($row3[1]) ?>"><?php echo($row3[2]) ?></a> says:
'<?php echo($row3[3]) ?>'
</div>
<?php
}
?>
</div>

<div style="margin-top:20px;">Add your own comment:</div>

<form id="cform">
<input type="hidden" name="id" value="<?php echo($id)?>">
<table>
<tr><td>Name:</td><td><input type="text" name="name"></td></tr>
<tr><td>Email:</td><td><input type="text" name="email"></td></tr>
<tr><td>Comment:</td><td><textarea name="comment" id="comment_text"></textarea></td></tr>
</table>
</form>
<button onclick="addcomment()">Add Comment</button>

<script>
function addcomment()
{
  new Ajax.Updater( 'comments', 'addcomment.php',
  {
    method: 'post',
    parameters: $('cform').serialize(),
    onSuccess: function() {
       $('comment_text').value = '';
    }
  } );
}
</script>
</body>
</html>

The script starts with populating a "comments" <div> tag with the current comments on this movie from the database. Then, a standard HTML <form> tag contains fields for the commenter name, his or her e-mail address, and the comment. The form also contains a hidden value (the ID of the movie currently being viewed) so that the comment-adding script knows which movie to assign the comment to.

The Add Comment button located below the form invokes the addcomment() JavaScript function. This function uses the Ajax.Updater object from the Prototype.js library to invoke the addcomment.php script. It packages the name, e-mail address, and comment using the incredibly handy serialize() function, also supplied by Prototype.js.

The code specifies that if it's successful, it will reset the text of the comment field, thus allowing users to add as many comments as they wish without having to re-type their names and e-mail addresses.

Listing 8 shows the addcomment.php script.

Listing 8. addcomment.php

                
<?php
require_once("DB.php");

$id = $_POST['id'];

$db =& DB::Connect( 'mysql://root@localhost/comments', array() );
if (PEAR::isError($db)) { die($db->getMessage()); }

$sth = $db->prepare( 'INSERT INTO comments VALUES ( ?, ?, ?, ? )' );
$db->execute( $sth, array( $id,
  $_POST['email'], $_POST['name'], $_POST['comment' ] ) );

$res = $db->query('SELECT * FROM comments WHERE movie_id=?', $id );
while( $res->fetchInto( $row ) )
{
?>
<div>
<a href="mailto:<?php echo($row[1]) ?>"><?php echo($row[2]) ?></a> says:
'<?php echo($row[3]) ?>'
</div>
<?php
}
?>

To start, the script adds the comment specified in the POST data into the database. It then outputs all the comments as HTML code, exactly as was done on the original page but with the exception that this time, it displays the additional comment that the user provided.

You can see the new rating page in Figure 4.

Figure 4. The updated rating page, now with comments

This commenting system gives instant feedback to viewers. When users click Add Comment, the comments are updated with the new comment right in place. The system also shows any additional comments that other viewers submit in the meantime.

If you want you can extend the example and poll the server on a regular basis to update the comments section with fresh comments. Of course, you shouldn't call addcomment.php to do that. You want another script that simply returns the comments without adding new ones. To make this easy, Prototype.js provides an Ajax.PeriodicalUpdater class that — given an ID, a refresh rate, and a URL — will update any portion of the Web page as often as you specify.

Adding RSS feeds

Another easy way to extend this example is to export the list of movies, with their rankings, as an RSS feed. In Listing 9, you can see the code that does this.

Listing 9. rss.php

                
<?php
header( "content-type:text/xml" );
?>
<rss version="0.91">
<channel>
<title>Movie rankings</title>
<link>http://localhost/comments/rss.php</link>
<?php
require_once("DB.php");

$db =& DB::Connect( 'mysql://root@localhost/comments', array() );
if (PEAR::isError($db)) { die($db->getMessage()); }

$res = $db->query( 'SELECT * FROM movies' );
while( $res->fetchInto( $row ) )
{

$res2 = $db->query(
  'SELECT count( rating ), sum(rating ) FROM ratings WHERE movie_id=?', $row[0]
);
$rating = 0.0;
while( $res2->fetchInto( $row2 ) ) { if ( $row2[0] > 0 ) $rating = $row2[1] / $row2[0]; }
?>
<item>
<title><?php echo($row[1]) ?> - 
<?php echo( $rating > 0 ? $rating : 0 ) ?> stars</title>
<link>http://localhost/commentsts/rate2.php?id=<?php echo($row[0]) ?></link>
<description><?php echo($row[1]) ?></description>
</item>
<?php
}
?>
</channel>
</rss>

The code in Listing 9 is just an alternative to writing out the data in an HTML format. Instead of <table>, <tr>, and <td> tags, I use <title>, <description>, and <link> tags to point to each of the movie pages. When I point to this page in my Firefox browser, I see something like Figure 5.

Figure 5. The RSS feed in the browser

That's pretty easy stuff. It's really not that hard to get XML feeds out of PHP.

And when I run the code locally by using this command line:

% php rss.php

I can see the RSS XML directly. Listing 10 shows an excerpt of the RSS.

Listing 10. RSS excerpt

                
<rss version="0.91">
<channel>
<title>Movie rankings</title>
<link>http://localhost/comments/rss.php</link>
<item>
<title>Star Wars - 4.5 stars</title>
<link>http://localhost/commentsts/rate2.php?id=1</link>
<description>Star Wars</description>
</item>
<item>
...

Conclusion

You'll see a lot of hype about user-generated content and how it's the driving force behind Web 2.0. As you can see from the examples in this article, you can easily build Ajax applications with great tools like the Prototype.js library. With functionality to make it easy for viewers to rate and comment on material on your site, you provide the golden user-generated content.

Download

Description	Name	Size	Download method
Source code for this article	x-ajaxxml5-code.zip	22 KB	HTTP

Information about download methods

Resources

Learn

The PHP home page: Visit an invaluable resource for PHP programmers with info about this widely used scripting language.
Prototype library: Explore this JavaScript Framework designed to ease development of dynamic Web applications.
The Scriptaculous JavaScript library: Find display helpers and effects to make your Web site fly with this Prototype-based framework.
The Prototype.js documentation page: Get additional information on the Prototype JavaScript library with links to the official Prototype blog and many other resources.
jQuery: Explore another JavaScript library that provides similar functionality to Prototype.js.
The Yahoo UI Library: Check out Yahoo!'s toolkit for Ajax.
developerWorks XML zone: Learn all about XML at the developerWorks XML zone.
IBM XML certification: Find out how you can become an IBM-Certified Developer in XML and related technologies.
XML technical library: See the developerWorks XML Zone for a wide range of technical articles and tips, tutorials, standards, and IBM Redbooks.
developerWorks technical events and webcasts: Stay current with technology in these sessions.
Ajaxian: Explore a great resource and keep up with developments in Ajax and the front-end widgets that use it.

Get products and technologies

IBM® DB2® Enterprise 9: Download a trial version of DB2 9 or DB2 Express-C 9, a no-charge version of DB2 Express 9 data server.

Discuss

XML zone discussion forums: Participate in any of several XML-centered forums.

About the author

Comments (Undergoing maintenance)

Trademarks | My developerWorks terms and conditions

Close [x]

Help: Update or add to My dW interests

What's this?

This little timesaver lets you update your My developerWorks profile with just one click! The general subject of this content (AIX and UNIX, Information Management, Lotus, Rational, Tivoli, WebSphere, Java, Linux, Open source, SOA and Web services, Web development, or XML) will be added to the interests section of your profile, if it's not there already. You only need to be logged in to My developerWorks.

And what's the point of adding your interests to your profile? That's how you find other users with the same interests as yours, and see what they're reading and contributing to the community. Your interests also help us recommend relevant developerWorks content to you.

View your My developerWorks profile

Return from help

Close [x]

Help: Remove from My dW interests

What's this?

Removing this interest does not alter your profile, but rather removes this piece of content from a list of all content for which you've indicated interest. In a future enhancement to My developerWorks, you'll be able to see a record of that content.

View your My developerWorks profile

Return from help

static.content.url=http://www.ibm.com/developerworks/js/artrating/

SITE_ID=1

Zone=XML, Open source, Web development

ArticleID=241671

ArticleTitle=Ajax and XML: Ajax for ratings and comments

publish-date=07242007

author1-email=jherr@pobox.com

author1-email-cc=dwxed@us.ibm.com