Simple example of loading a ColdFusion query with jQuery

Earlier last week a reader (and forgive me, I wrote down your idea, not your name) asked for a simple demonstration of how to use jQuery to load in a ColdFusion query via Ajax. I whipped up a quick, and hopefully simple, set of demos to help show how easy this is. First, please remember that there are many ways we can this. Just like ColdFusion provides many ways to skin the cat, so does jQuery. I’m going to demonstrate two main ways as I feel it really shows the two types of Ajax calls most folks will use.

In broad terms, most folks will use Ajax to either load rendered content or pure data. So let’s say you want to show a list of people on a page. You want to load this via Ajax. You could have the server return the list, printed out with HTML, line breaks, etc. This is rather simple and is especially useful for times when your formatting needs are complex. It is a heck of a lot easier to format dates in ColdFusion then JavaScript. (Although I bet jQuery has a good date library!)

The alternative is to load pure data. This can be XML or JSON (typically JSON) which is then handled on the client. This requires more work, but typically results in less ‘traffic’ as you are only sending the data, not data plus formatting.

So which should you use? Whichever works best for you! (Yes, I know, a non-answer. Sorry.) Here are two demos of both in action.


<html>

<head>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>
<script>

function loadQuery() {
	$.get('data.cfm',{},function(data){
		$("#content").html(data)
	})
	return false
}

$(document).ready(function() {
	$("#loadLink").click(loadQuery)
});

</script>
</head>

<body>

<p>
	<a href="" id="loadLink">Load Query</a>
</p>

<div id="content">
</div>

</body>

</html>

In my first example, I have a simple page that consists of one link and one empty div. Notice then loadQuery handles making the Ajax request and publishing it to the empty div. The ColdFusion file handles both creating the query and rendering it (although normally I’d get the query elsewhere, from a CFC for example):


<cfset q = queryNew("person,coolness")>

<cfset queryAddRow(q)>
<cfset querySetCell(q, "person", "Scott Slone")>
<cfset querySetCell(q, "coolness", "100")>

<cfset queryAddRow(q)>
<cfset querySetCell(q, "person", "Scott Stroz")>
<cfset querySetCell(q, "coolness", randRange(1,100))>

<cfset queryAddRow(q)>
<cfset querySetCell(q, "person", "Raymond Camden")>
<cfset querySetCell(q, "coolness", randRange(1,100))>

<cfset queryAddRow(q)>
<cfset querySetCell(q, "person", "Todd Sharp")>
<cfset querySetCell(q, "coolness", randRange(1,100))>

<cfset queryAddRow(q)>
<cfset querySetCell(q, "person", "Scott Pinkston")>
<cfset querySetCell(q, "coolness", randRange(1,100))>

<h1>People</h1>

<cfoutput query="q">
<b>#person#</b> has a cool score of #coolness#.<br/>
</cfoutput>

There isn’t anything special about the query, except for a shout out to Scott Slone for feeding my Star Wars addiction via woot.com today!

You can see this in action here: (Removed demo link, see note at bottom)

Now let’s look at the alternative. I’ll start on the server side first. Here is data2.cfm. Same query, but now we serve it up as JSON and don’t perform any formatting:


<cfset q = queryNew("person,coolness")>
... querySetCells cut from code to make it shorter ...

<cfset data = serializeJSON(q)>
<cfcontent type="application/json" reset="true"><cfoutput>#data#</cfoutput>

The front end now needs to get a bit more complex. I’ve only modified the loadQuery function so I’ll just paste in that:


function loadQuery() {
	$.getJSON('data2.cfm',{},function(data){
		
		//map columns to names
		var columnMap = {}
		
		for (var i = 0; i < data.COLUMNS.length; i++) {
			columnMap[data.COLUMNS[i]] = i	
		}

		//begin making my str
		var str = '<h1>People</h1>'
		
		for (var i = 0; i < data.DATA.length; i++) {
			str += '<b>'+data.DATA[i][columnMap.PERSON]+'</b>'
			str += ' has a cool score of '+data.DATA[i][columnMap.COOLNESS]+'<br/>'
		}
	
		$("#content").html(str)

	})
	return false
}

Ok, so the first change is the switch from get to getJSON. This just tells jQuery to go ahead and expect JSON and turn it into a native JavaScript object for me. At that point I wasn’t sure what to do. Where do I turn when I want to just… play/test/etc with JavaScript? Firebug. I ran this:

console.dir(data)

So I could look at the result. The result had 2 keys: COLUMNS and DATA. COLUMNS was an array of column names. DATA was an array of data (big surprise there). I realized that the first column in the COLUMNS array matched the first column of data. So if I wanted the person column, I could either hard code the value 0, or, do what I did here, which is to create an object that stored the column name and the corresponding position.

I then create my string. Notice how I make use of columnMap to address the data in the array. Once done, I then simply place the HTML in the div.

You can see this in action here: (Removed demo link, see note at bottom)

Enjoy!

For folks looking for the old code for this, you can download a zip of it here: https://static.raymondcamden.com/enclosures/jquerycfquery.zip

Raymond Camden

Simple example of loading a ColdFusion query with jQuery

About Raymond Camden

Comments