Ask Ben: Building A jQuery And ColdFusion Rating System
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
<!--- 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
<!--- 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
<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.
Want to use code from this post? Check out the license.
Reader Comments
Just curious, why did you opt for a cfm page instead of a cfc with returnformat="json"?
@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.
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.
@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.
@Chin,
Thank you, this has been fixed!
Hi Ben,
I meant prepended, not appended lol.
Thanks, Ben!
@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
@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.
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?
@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.
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...
@Joe,
1.3.2 Should have the data() method. Perhaps there is something wrong with the JSON you are trying to evaluate.
@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!
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!
@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.
@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.
@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.
@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?
@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.
@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.
This was exactly what we needed! I know this isn't a CF issue, but this works perfect in Firefox, but not at all in IE. I'm hoping there is just something small to trigger the click and we can move on with styling it.
Thanks for your contributions!
@Matt,
Hmm. I am not sure if I ever tested it in IE. Is it throwing a Javascript error? Or is it just not appearing to do anything?
Thanks for the reply. It is a Javascript Error:
Webpage error details
User Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.0; WOW64; Trident/4.0; SLCC1; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30618; MS-RTC LM 8)
Timestamp: Thu, 21 Jan 2010 15:35:59 UTC
Message: Syntax error
Line: 213
Char: 11
Code: 0
URI: http://arborday.org/shopping/giveatree/contest/vote.cfm
By viewing the source and going to 213, this is the general area. Specifically it's on the the list.data metadata assignment.
if (metaData.size()){
// Apply meta data.
list.data("metaData",eval( "(" + metaData.text() + ")" )
);
// Remove the meta data node.
metaData.remove();
}
@Matt,
Make sure you have the latest version of jQuery. I believe the data() method is somewhat new - I know there are versions of jQuery that didn't have it. But, I don't think that would cause a syntax error - just an Undefined method error.
We typically just use the Google CDN, but we even downloaded 1.4 locally. We sincerely appreciate your help. Does it work in IE for you? For us, it works get on every other browser.
@Matt,
Hmm, looks like it breaks my IE as well (the original demo). Let me pop in and see if I can figure it out.
@Matt,
I figured it out. Looks like text() doesn't work well with Script and IE. If you replace this line:
eval( "(" + metaData.text() + ")" )
... with this line:
eval( "(" + metaData.html() + ")" )
... you should be good. Sorry for not testing this IE. I use FireFox so often, I forget to test things when doing R&D stuff.
@Matt,
Thanks again for pointing this issue out. I did a little further exploration and it looks like the only way to get Script-tag-content data is with the html() tag in IE.
www.bennadel.com/blog/1829-Script-Tags-jQuery-And-Html-Text-And-Contents-.htm
I used a modification of your system to make a thumbs up / down rating system, it worked fine on my testing serve, but on the remote I received an 500 internal server error "Invalid CFML construct found on line 2 at column 22.
ColdFusion was looking at the following text:{"
I had to modify your code from
<cfset apiResponse = {
success = true,
errors = [],
data = ""
} />
to
<cfset apiresponse.succes=true>
<cfset apiresponse.errors=[]>
<cfset apiresponse.data="">
After that it is working fine.
Thanks for your code Ben.
@Gustavo,
You probably have different versions of ColdFusion locally and live. The code I have uses implicit struct creation - something introduced in ColdFusion 8. You probably have CF7 on the production server, which needs the older-style struct creation broken out on different lines as you found.
Ben, have you ever used jQuery's 'raty' plugin?
http://plugins.jquery.com/project/raty
Very nice. Another interesting exercise (hint, hint) might be to use this plugin to make a CF version ala HotOrNot DOT com.
Basically, you rate a single random image on a 10 point scale. When a vote is cast, the avg vote and number of votes are returned. You cannot double vote and no image is shown more than once.
Interested? :)
Hi Ben.
I get an sql error when invoking the script:
[Macromedia][SQLServer JDBC Driver][SQLServer]Die 'rating.id'-Spalte ist in der Auswahlliste ungültig, da sie nicht in einer Aggregatfunktion und nicht in der GROUP BY-Klausel enthalten ist.
Means translated: The column rating.id is in the selection list not allowed as she is not in a aggregat function and not in the Group-By clause.
Any idea what to do?
Thanks a lot ...
Me too Antoine, any ideas anyone?
Answer: Group By needs to look like this:
GROUP BY i.id, r.image_id, er.id
This is a really nice little solution Ben. Just finished implementing it (with a few tweaks) and pretty happy. Many thanks for sharing.
Ok - a 2nd set of eyes helped: had Rate_Image.cfm referenced in lowercase, and had to increase number of chars allowed in DB for one of the fields. Works fine now... Thanks Ben!