Ask Ben: Building A jQuery And ColdFusion Rating System

Posted July 6, 2009 at 3:10 PM

Tags: ColdFusion, Javascript / DHTML, Ask Ben

Check out this page... http://csssnap.com/ Look at the ratings widget under each thumbnail. Is this possible with CF? It seems to update on the fly (is that Ajax + jQuery) to a database that then spits back the average of all the votes. It then blocks you from voting again if you have already voted (perhaps via IP tracking). Since I have no clue (yet) as to how to make something like this, I thought I might make a nice blog entry. :) Thanks, E.

NOTE: Before I get into this, I want to preface this blog entry with the note that this is not a tutorial on how to best create a jQuery plugin for rating widgets. There's a bunch of those that already exist, look really nice, and function more robustly than the one I made below. This blog post is about how to create a simple end-to-end rating system powered by ColdFusion and jQuery. It is meant simply to get the mental juices flowing and help people move in a given direction.

That said, I have built a very small, very simple rating system powered by ColdFusion and MySQL on the backend and single page website featuring jQuery on the front end. In this demo, a user can rate a given image only once. The overall rating for each image is adjusted over time based on the ratings stored in the database.

 
 
 
 
 
 
 
 
 
 

In this demo ratings app, uniqueness of the user is defined by their IP address and their browser's user agent. I don't like tying uniqueness to IP address alone because I feel it is too limiting, especially today when several users might share a given IP address (such as within an office settings). As such, I think using the combination of IP address and user agent allows for more flexibility while at the same time, staying within the intent of the single-user-rating rules.

The data table that I use to store the ratings information is quite simple:

ratings

  • id: int (auto increment)
  • ip_address: varchar
  • user_agent: varchar
  • rating: int
  • date_created: date/time
  • image_id: int

The image_id field in the above table would be the foreign key to the image table. However, as you saw in the video above, I don't have an image table - the image table was created inline with the main page query. The rating column is the rating selected by the given user (as defined by the ip_address and the user_agent).

When it comes to the code, there's really two players: the code that handles the API request for the user rating and the code that displays the images and the ratings widget. Since the API is all ColdFusion and a bit easier to understand, let's take a look at that first:

Rate_Image.cfm

 Launch code in new window » Download code as text file »

  • <!--- Create a unified API resposne. --->
  • <cfset apiResponse = {
  • success = true,
  • errors = [],
  • data = ""
  • } />
  •  
  •  
  • <!--- Try to execute the api request / response. --->
  • <cftry>
  •  
  • <!--- Param the FORM variable. --->
  • <cfparam name="form.image_id" type="numeric" />
  • <cfparam name="form.rating" type="numeric" />
  •  
  •  
  • <!---
  • Check to see if this user has already rated this image.
  • We do not want to allow duplicate ratings.
  • --->
  • <cfquery name="existingRating" datasource="#application.dsn#">
  • SELECT
  • r.id
  • FROM
  • rating r
  • WHERE
  • r.image_id = <cfqueryparam value="#form.image_id#" cfsqltype="cf_sql_integer" />
  • AND
  • r.ip_address = <cfqueryparam value="#cgi.remote_addr#" cfsqltype="cf_sql_varchar" />
  • AND
  • r.user_agent = <cfqueryparam value="#cgi.http_user_agent#" cfsqltype="cf_sql_varchar" />
  • </cfquery>
  •  
  •  
  • <!--- Check to see if the rating exists. --->
  • <cfif existingRating.recordCount>
  •  
  • <!--- Add error. --->
  • <cfset arrayAppend(
  • apiResponse.errors,
  • "You have already rated this image."
  • ) />
  •  
  • </cfif>
  •  
  •  
  • <!--- Check to see if we have any errors. --->
  • <cfif NOT arrayLen( apiResponse.errors )>
  •  
  • <!--- Insert new rating. --->
  • <cfquery name="insertRating" datasource="#application.dsn#">
  • INSERT INTO rating
  • (
  • ip_address,
  • user_agent,
  • rating,
  • date_created,
  • image_id
  • ) VALUES (
  • <cfqueryparam value="#cgi.remote_addr#" cfsqltype="cf_sql_varchar" />,
  • <cfqueryparam value="#cgi.http_user_agent#" cfsqltype="cf_sql_varchar" />,
  • <cfqueryparam value="#form.rating#" cfsqltype="cf_sql_integer" />,
  • <cfqueryparam value="#now()#" cfsqltype="cf_sql_timestamp" />,
  • <cfqueryparam value="#form.image_id#" cfsqltype="cf_sql_integer" />
  • );
  •  
  • <!--- Get the new overall rating. --->
  • SELECT
  • (
  • SUM( r.rating ) /
  • COUNT( r.rating )
  • ) AS overall_rating
  • FROM
  • rating r
  • WHERE
  • r.image_id = <cfqueryparam value="#form.image_id#" cfsqltype="cf_sql_integer" />
  • ;
  • </cfquery>
  •  
  •  
  • <!--- Set the current rating as the response data. --->
  • <cfset apiResponse.data = insertRating.overall_rating />
  •  
  • </cfif>
  •  
  •  
  • <!--- Catch any api errors. --->
  • <cfcatch>
  •  
  • <!--- Set the error in our api response object. --->
  • <cfset apiResponse.errors = [ cfcatch.message, cfcatch.detail ] />
  •  
  • </cfcatch>
  • </cftry>
  •  
  •  
  • <!--- Check to see if we have any errors at this point. --->
  • <cfif arrayLen( apiResponse.errors )>
  •  
  • <!--- Flag the API request as unsuccessful. --->
  • <cfset apiResponse.success = false />
  •  
  • </cfif>
  •  
  •  
  • <!--- Searialize the API response into our JSON value. --->
  • <cfset jsonResponse = serializeJSON( apiResponse ) />
  •  
  • <!--- Convert the response string to binary for streaming. --->
  • <cfset binaryResponse = toBinary( toBase64( jsonResponse ) ) />
  •  
  •  
  • <!--- Stream the binary data back. --->
  • <cfheader
  • name="content-length"
  • value="#arrayLen( binaryResponse )#"
  • />
  •  
  • <cfcontent
  • type="text/x-json"
  • variable="#binaryResponse#"
  • />

As with most all API responses in my applications, this API request will return a unified API response object with the following keys:

  • Success: Boolean - Determines if the request was successful.
  • Errors: Array - A collection of API errors.
  • Data: Any - Any data that the API needs to respond with.

The ColdFusion here is rather straightforward; the image ID and the user-selected rating is passed-in via a form submission. We do some checking to see if the user has already rated this image (based on the unique combination of image ID, user agent, and IP address). If so, then we flag the request as an error. If not, then we insert the user rating and return the current overall rating for that image.

Now that we have that page down, let's take a look at the main index.cfm page. This page gathers the images and then displays them along with the ratings widgets:

Index.cfm

 Launch code in new window » Download code as text file »

  • <!--- Query for images to rate. --->
  • <cfquery name="image" datasource="#application.dsn#">
  • SELECT
  • i.id,
  •  
  • <!--- Get the current rating for the image. --->
  • (
  • CASE
  • WHEN
  • COUNT( r.rating ) > 0
  • THEN
  • (
  • SUM( r.rating ) /
  • COUNT( r.rating )
  • )
  • ELSE
  • 0
  • END
  • ) AS rating,
  •  
  • <!--- Query for existing rating by user. --->
  • COALESCE( er.id, 0 ) AS has_existing_rating
  • FROM
  • (
  • SELECT 1 AS id UNION ALL
  • SELECT 2 AS id UNION ALL
  • SELECT 3 AS id
  • ) AS i
  •  
  • <!--- Join this to the rating table to get rating. --->
  • LEFT OUTER JOIN
  • rating r
  • ON
  • i.id = r.image_id
  •  
  • <!---
  • Join this to the rating table AGAIN to see if the current
  • user has already rated the given image.
  • --->
  • LEFT OUTER JOIN
  • rating er
  • ON
  • (
  • er.image_id = i.id
  • AND
  • er.ip_address = <cfqueryparam value="#cgi.remote_addr#" cfsqltype="cf_sql_varchar" />
  • AND
  • er.user_agent = <cfqueryparam value="#cgi.http_user_agent#" cfsqltype="cf_sql_varchar" />
  • )
  •  
  • GROUP BY
  • i.id,
  • r.image_id
  • ORDER BY
  • i.id ASC
  • </cfquery>
  •  
  •  
  •  
  • <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  • <html>
  • <head>
  • <title>jQuery And ColdFusion Rating System Demo</title>
  • <script type="text/javascript" src="jquery-1.3.2.min.js"></script>
  • <script type="text/javascript">
  •  
  • // Define jquery plugin.
  • jQuery.fn.rating = function( postUrl ){
  • // Loop over each list to apply meta data.
  • this.each(
  • function( index, listNode ){
  • var list = $( this );
  • var metaData = list.find( "script.meta-data" );
  •  
  • // Check to see if meta data was found.
  • if (metaData.size()){
  •  
  • // Apply meta data.
  • list.data(
  • "metaData",
  • eval( "(" + metaData.text() + ")" )
  • );
  •  
  • // Remove the meta data node.
  • metaData.remove();
  •  
  • }
  • }
  • );
  •  
  •  
  • // Initialize the links within the list.
  • this.find( "a" )
  • .attr( "href", "javascript:void( 0 )" )
  • .click(
  • function( clickEvent ){
  • var link = $( this );
  • var list = link.parents( "ul:first" );
  • var metaData = list.data( "metaData" );
  •  
  • // Post the rating.
  • jQuery.ajax({
  • type: "post",
  • url: postUrl,
  • data: {
  • image_id: metaData.id,
  • rating: link.text()
  • },
  • dataType: "json",
  • success: function( apiResponse ){
  • // Check to see if the API request
  • // was valid.
  • if (apiResponse.SUCCESS){
  •  
  • // Replace the list with the
  • // current rating.
  • list
  • .empty()
  • .append(
  • "<li>Rating: " +
  • apiResponse.DATA.toFixed( 1 ) +
  • "</li>"
  • )
  • ;
  •  
  • }
  • }
  • });
  •  
  • // Cancel default event.
  • return( false );
  • })
  • ;
  •  
  • // Return jQuery object for chaining.
  • return( this );
  • };
  •  
  •  
  • // When the DOM is ready, initialize the plugin.
  • $(function(){
  • $( "ul" ).rating( "rate_image.cfm" );
  • });
  •  
  • </script>
  • <style type="text/css">
  •  
  • ul.rating {
  • height: 20px ;
  • list-style-type: none ;
  • margin: 10px 0px 0px 0px ;
  • padding: 0px 0px 0px 0px ;
  • }
  •  
  • ul.rating li {
  • float: left ;
  • margin: 0px 5px 0px 0px ;
  • padding: 0px 0px 0px 0px ;
  • }
  •  
  • ul.rating a {
  • background-color: #F0F0F0 ;
  • border: 1px solid #333333 ;
  • color: #333333 ;
  • float: left ;
  • height: 20px ;
  • line-height: 20px ;
  • text-align: center ;
  • text-decoration: none ;
  • width: 20px ;
  • }
  •  
  • </style>
  • </head>
  • <body>
  •  
  • <h1>
  • jQuery And ColdFusion Rating System Demo
  • </h1>
  •  
  • <cfoutput>
  •  
  • <cfloop query="image">
  •  
  • <div style="float: left ; margin-right: 20px ;">
  •  
  • <img
  • src="./images/girl#image.id#.jpg"
  • width="165"
  • style="display: block ;"
  • />
  •  
  • <!--- Check to see if user has rated yet. --->
  • <cfif image.has_existing_rating>
  •  
  • <!---
  • User has already rated, just show the
  • current rating.
  • --->
  • <ul class="rating">
  • <li>
  • Rating: #numberFormat(
  • image.rating,
  • "0.0"
  • )#
  • </li>
  • </ul>
  •  
  • <cfelse>
  •  
  • <!--- Show the rating options. --->
  • <ul class="rating">
  • <!---
  • Set up the meta-data for this image.
  • This data will be applied when the
  • rating plugin is initialized.
  • --->
  • <script
  • type="application/x-json"
  • class="meta-data">
  • {
  • id: #image.id#
  • }
  • </script>
  • <li>
  • <a>1</a>
  • </li>
  • <li>
  • <a>2</a>
  • </li>
  • <li>
  • <a>3</a>
  • </li>
  • <li>
  • <a>4</a>
  • </li>
  • </ul>
  •  
  • </cfif>
  •  
  • </div>
  •  
  • </cfloop>
  •  
  • </cfoutput>
  •  
  • </body>
  • </html>

As I said before, I don't have an images data table so my images are created inline to the main SELECT at the top. I then do something a bit tricky - I join the images table to the ratings table twice: once to get the current rating on the image and once to see if the current user has already rated the image. I then loop over the images query and output the images with the ratings widget. I didn't want to start doing anything too complex here jQuery-wise, so if the user has already rated the image, I am simply display the current rating rather than the ratings widget.

For each rating widget that is displayed, I apply my rating() jQuery plugin. This plugin gathers the meta data from each list (containing the image ID) and then hooks up the click event handlers for the individual rating levels. If a rating link is clicked, the plugin posts the appropriate data to rate_image.cfm. If the API response comes back successfully, the plugin replaces the current ratings widget with the new, overall rating of the widget (as would be displayed on page refresh).

I know the explanation is a bit cursory, but unfortunately, I am running very short on time today. The final player in this whole thing is the Application.cfc, which is extremely small:

Application.cfc

 Launch code in new window » Download code as text file »

  • <cfcomponent
  • output="false"
  • hint="I define the application settings and event handlers.">
  •  
  • <!--- Define the application. --->
  • <cfset this.name = hash( getCurrentTemplatePath() ) />
  • <cfset this.applicationTimeout = createTimeSpan( 0, 0, 5, 0 ) />
  •  
  • <!--- Define the page request settings. --->
  • <cfsetting showdebugoutput="false" />
  •  
  •  
  • <cffunction
  • name="onApplicationStart"
  • access="public"
  • returntype="boolean"
  • output="false"
  • hint="I initialize the application.">
  •  
  • <!--- Define the application. --->
  • <cfset application.dsn = "xyz" />
  •  
  • <!--- Return out. --->
  • <cfreturn true />
  • </cffunction>
  •  
  • </cfcomponent>

Like I said before, this post was not a tutorial on how to build a jQuery ratings plugin - there's too many better ones out there already. This was just supposed to be an end-to-end demonstration of how you might put a ratings system together using ColdFusion, jQuery, AJAX, and a database. I hope this has helped in some way.

Download Code Snippet ZIP File

Post Comment  |  Ask Ben  |  Permalink  |  Print Page



Learning ColdFusion 9 - ColdFusion 9 tutorials, samples, examples, demos

Reader Comments

Drew Wells
Jul 7, 2009 at 12:12 PM // reply »
6 Comments
63 Points

Just curious, why did you opt for a cfm page instead of a cfc with returnformat="json"?


Ben Nadel
Jul 7, 2009 at 1:27 PM // reply »
6,516 Comments
25,763 Points

@Drew,

I generally use CFM pages for API calls simply out of habit. Plus, in a system as simple as this, there's not really much benefit to having a remote-access CFC. The only real bonus is the automatic conversion to JSON; but, that's so small, that I think keeping it in CFM is worthwhile, especially for teaching.


Chin
Jul 22, 2009 at 12:15 AM // reply »
3 Comments
27 Points

Hi Ben,

This is regarding the "For Cut-and-Paste" code section of Index.cfm.
The apiResponse section has some invalid codes. <li class="tab11"> was appended to every response text line.

Thanks for providing this sample application.
It was easy to follow.


Ben Nadel
Jul 22, 2009 at 7:58 AM // reply »
6,516 Comments
25,763 Points

@Chin,

Ooops, thanks a lot! I think that might be the most deeply indented my code has gotten before and the clean-up didn't know what to do. I'll look into that right now.


Ben Nadel
Jul 22, 2009 at 8:00 AM // reply »
6,516 Comments
25,763 Points

@Chin,

Thank you, this has been fixed!


Chin
Jul 22, 2009 at 8:28 AM // reply »
3 Comments
27 Points

Hi Ben,

I meant prepended, not appended lol.
Thanks, Ben!


Gavy
Jul 24, 2009 at 11:11 PM // reply »
10 Comments
112 Points

@ben: Freat Ben, I have always been trying to do such a thing. I did one but that was different from what you have told here.

One More thing, Can i Use Stars in this. AND rating shown in numbers like 3.8 etc. Can't we do the same by showing the result in stars


Ben Nadel
Jul 27, 2009 at 8:46 AM // reply »
6,516 Comments
25,763 Points

@Misty,

Absolutely you can use stars. I would suggesting looking up one of the jQuery plugins for rating systems - they all tend to use stars.


Joe
Sep 9, 2009 at 12:37 PM // reply »
5 Comments
52 Points

Hi, first off thanks for your work on this... it is the only cfm one i could even find!

I do have a problem when running it (basically in copy paste format)... i get a syntax error on

list.data(
"metaData",
eval( "(" + metaData.text() + ")" )
);

Any ideas what causes this?


Ben Nadel
Sep 12, 2009 at 10:23 PM // reply »
6,516 Comments
25,763 Points

@Joe,

What version of jQuery are you using? It's possible you have an older version that didn't yet have the data() storage method yet.


Joe
Sep 14, 2009 at 9:49 AM // reply »
5 Comments
52 Points

Thanks for getting back... i am using

src="http://jqueryjs.googlecode.com/files/jquery-1.3.2.min.js" type="text/javascript" charset="utf-8"

other than that i dont think i changed your code...


Ben Nadel
Sep 24, 2009 at 9:47 AM // reply »
6,516 Comments
25,763 Points

@Joe,

1.3.2 Should have the data() method. Perhaps there is something wrong with the JSON you are trying to evaluate.


Joe
Oct 16, 2009 at 3:08 PM // reply »
5 Comments
52 Points

@Ben,

Not sure why but I could not get the metadata passed from "meta-data" ... I removed this and added the image_id to <a> -- then changed to var metaData = this.id;

Don't know why I had this problem but it is working fine now. Thanks again for this solution!


Kamil
Oct 18, 2009 at 11:13 AM // reply »
4 Comments
42 Points

Hi Ben,

thanks fot your work on that script!

Could you please give me a hint with the code, because yit is great but somehow doesn't work for me.

I just copy+past the above code, created 1 table (rating) and linked jquery-1.3.2.min.js,but js doesn't work-it doesn't insert data or even load. I haven't changed anything in the code, just copied.

What can be wrong?What should I do?

I would be greatful for your help. Many thanks in advance!


Joe
Oct 18, 2009 at 11:21 AM // reply »
5 Comments
52 Points

@Kamil,

I think you need to add er.Id to the group by in SQL for the page to load... Then see my issue above, you might have the same issue as I did.


Kamil
Oct 18, 2009 at 11:39 AM // reply »
4 Comments
42 Points

@Joe,

thanks for reply!

I,m not very fluent in coding-that's why I have just copy+pasted the code hoping it would be enough:)

Could you please specified the code lines, which I should change-it would be much easer for me the correct my code.

I think I have the same problem as you had, because data don't insert. It should be changed in index.cfm? But the table construction is ok?

Please help. Thanks.


Kamil
Oct 19, 2009 at 12:29 PM // reply »
4 Comments
42 Points

@Joe,

Debuger says: TypeError- Result of expression 'metaData' [undefined] is not an object (line 106 of index.cfm).

So when I remove .id from "image_id: metaData.id" the script runs, but there is an error in running rate_image.cfm:

{"ERRORS":["Invalid parameter type.","The value cannot be converted to a numeric because it is not a simple value.Simple values are booleans, numbers, strings, and date-time values."],"SUCCESS":false,"DATA":""}

How did you change the image_id so that the script works? Do you have any script for rating in coldfusion, because I googled all net and found only Ben's one.

Thanks in advace form help.


Joe
Oct 19, 2009 at 1:19 PM // reply »
5 Comments
52 Points

@Kamil,

what i did to get around this was to replace the meta-data by adding the id to <a>
'a id="#image.id#"'

then replaced:
[var metaData = list.data( "metaData" )]
with:
[var metaData = this.id]

Not sure if this is the best way or not, but it worked for me. Maybe someone else can explain why the meta-data script doesnt work for us?


Kamil
Oct 19, 2009 at 4:39 PM // reply »
4 Comments
42 Points

@Joe,

thanks for replay. Somehow it doesn't work-script doesn't run and even trigers(<a>) don't link when I added Id's to <a>'s.

Mayby I did something differently than you. Is it not a ploblem if you could send me your version of script by mail (kamillion@vp.pl). I would be greatful, because I try to solve that problem or find voting system for coldfusion for couple of weeks without result and it is crusial for me. Thanks.


Ben Nadel
Oct 31, 2009 at 4:53 PM // reply »
6,516 Comments
25,763 Points

@Kamil,

Based on the ColdFusion error, it looks like you are not passing the correct image_id or the rating value to the AJAX call.


Post Comment  |  Ask Ben

Recent Blog Comments
komik
Nov 21, 2009 at 6:47 PM
Hal Helms - Real World Object Oriented Development, Sarasota - Day Five
@charlie griefer, Thank you.. ... read »
Jody Fitzpatrick
Nov 21, 2009 at 5:15 PM
Using ColdFusion Structures To Remove Duplicate List Values
@Jose Galdamez, Oh heh yeah I didn't paste the whole code. I should have defined the vars -- my bad. It's fixed thou. Thanks. ... read »
Unibands
Nov 21, 2009 at 4:49 PM
Styling The ColdFusion 8 WriteToBrowser CFImage Output
Great work yet again Ben! Whilst I didn't use this whole code, I copied some of your regex code for a similar problem with the lack of an alt attribute and unescaped ampersands in CFIMAGE for Railo 3 ... read »
Justice
Nov 21, 2009 at 1:13 PM
My First ColdFusion Builder Extension - Encrypting And Decrypting CFM / CFC Files
@Ben, Because I am pedantic, I just want to make sure that everyone knows there is absolutely no encryption going on. There is only encoding and obfuscation. The cfencode tool only obfuscates your C ... read »
Jose Galdamez
Nov 21, 2009 at 12:28 PM
Using ColdFusion Structures To Remove Duplicate List Values
@Jody I can't seem to get your code sample to work. If you are still having problems, try this code out and see if it gets you what you wanted. <!--- Comma delimited list with various duplicates ... read »
Esther Annies
Nov 21, 2009 at 11:03 AM
Groovy Operator Overloading Does Not Work In The ColdFusion Context
Hi Ben, Thanks for this informative post. Now I am reading ur old posts too ... read »
Henry Parker
Nov 21, 2009 at 10:56 AM
HostMySite.com Has The Best ColdFusion Hosting
@Mehul, Yes very nice people, however several downtimes per day which was not acceptable. Hence we had to move out. I am glad you are having good luck with them so far. ... read »
<scriptalert('just a test buddy :)')script
Nov 21, 2009 at 12:41 AM
Ask Ben: Building An AJAX, jQuery, And ColdFusion Powered Application
13311qseqweqwe ... read »