Converting ColdFusion data for jQuery Plugins - An example
This post is more than 2 years old.
Kerrie asks:
A couple of weeks ago, I read a post you wrote on jQuery and form validation... really peaked my interest so I've been taking a look at not only the validation plugin, but many of the other great jQuery plugins... I found this one last night, and its perfect for an app I'm working on, but I cannot figure out how to return the output of a query to populate the list. In the demo they are returning the results of tvshows.php. I noticed a number of other folks were having the same problem but no solution. Might you have a few spare moments to take a look??
Kerrie, don't feel alone. I've noticed this in a few other jQuery plugins. The author will give you an example of the JSON they want, but they don't describe the JSON in pure data forms. So for example, if the JSON string is an array of strings, they don't say that. They just show it and assume you know that is how arrays are represented in JSON. JSON may be easy, but I definitely can't parse it in my head quite yet. Lets take a look at what the plugin wants:
[{"id":"856","name":"House"}, {"id":"1035","name":"Desperate Housewives"}, {"id":"1048","name":"Dollhouse"}, {"id":"1113","name":"Full House"} ]
Ok, so what do you do here? Obviously this is a pattern where every result is {"id":"X", "name":"Y"}, wrapped in [ and ]. So I took a guess here and thought maybe they wanted an array of structs. There is an easy way to test this - just write a quick test script:
a, just an array: #serializeJSON(a)#
c, an array with a struct: #serializeJSON(c)#
This output:
s, just a struct: {}
a, just an array: []
c, an array with a struct: [{}]
Perfect! So now I know - I need to convert a query into an array of structs. Easy enough, right?
Here is the first CFC method I came up with:
<cffunction name="getNames" access="remote" output="false" returnType="any"> <cfargument name="q" type="string" required="true"> <cfset var entrylookup = ""> <cfset var r = []> <cfset var s = {}><cfquery name="entrylookup" datasource="blogdev">
select id, title
from tblblogentries
where title like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.q#%">
</cfquery>
<cfloop query="entrylookup">
<cfset s = {id=id, name=title}>
<cfset r[arrayLen(r)+1] = s>
</cfloop>
<cfreturn r>
</cffunction>
The docs said one argument, q, was always passed in, so I look for that and pass it to a query. I then loop over the results and create a struct for each, append it to the array, and return the array.
I next whipped up a quick demo script:
<html><head> <script src="/jquery/jquery.js"></script> <script src="/jquery/jquery.tokeninput.js"></script> <link rel="stylesheet" href="/jquery/tokeninput/token-input.css" type="text/css" /> <script> $(document).ready(function() {
$("#name").tokenInput("data.cfc?method=getNames&returnFormat=json", {
hintText: "Type in the name of a blog entry.",
noResultsText: "No results",
searchingText: "Searching..."
})
})
</script> </head>
<body>
<form> <input type="text" name="name" id="name"> </form>
</body> </html>
The code comes from the docs, and me viewing source, for the plugin. As far as I can tell, he doesn't actually document the options, but I was able to guess. So I gave it a try and did it work?
Heck no!
And since it was Ajax and I couldn't see why, I cried into my beer, shut the laptop, and went home.
Oh wait - I have Firebug! And what do I always say when something goes wrong with Ajax? Check Firebug. And guess what - notice the result:
See the case of the ID and NAME values? They are both upper case. I changed my code to use the more verbose struct creation and set the keys so that my case was maintained:
<cffunction name="getNames" access="remote" output="false" returnType="any"> <cfargument name="q" type="string" required="true"> <cfset var entrylookup = ""> <cfset var r = []> <cfset var s = {}><cfquery name="entrylookup" datasource="blogdev">
select id, title
from tblblogentries
where title like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.q#%">
</cfquery>
<cfloop query="entrylookup">
<cfset s["id"] = id>
<cfset s["name"] = title>
<cfset arrayAppend(r, s)>
</cfloop>
<cfreturn r>
</cffunction>
And voila - it worked. So long story short - the basic idea is to try to figure out the real data behind the expected JSON - or the real data form I should say. It would have been nice if the author had said "An array of maps" (or whatever PHP uses) though.
Archived Comments
You have a typo where your fingers autocompleted "real date" when you meant "real data".
Fixed, thanks.
Ray, now I get it! Thanks so very much for taking the time to work on this. Kerrie
Just one thing I had to add to your example (in case anyone else is trying to get this to work). Inside the loop ... <cfset s=structNew()>
<cfloop query="entrylookup">
<cfset s = structNew()>
<cfset s["id"] = id>
<cfset s["name"] = title>
<cfset arrayAppend(r, s)>
</cfloop>
Thanks again Ray :-)
Hmm. Why? Are you not on CF8?
CF8 yes. (I am using the Developer's edition for testing). Without adding structNew() my results were repeated.
array
1 struct
id 343618
name Repeated
2 struct
id 343618
name Kelly Repeated
3 struct
id 343618
name Kelly Repeated
That's odd. The struct has 2 keys, id and name. In each iteration, we overwrite them. Unless its a pointer issue - but I was on CF8 just like you.
Thanks Ray, this saved me a huge headache.
Hi Ray - thanks for this example! Made figuring out CF to jQuery a lot easier!
I do have to agree with Kerrie above though - if I didn't put structNew() inside of the loop then the one data point was just repeated for each row in the query as opposed to each row's values. I am using CF8 as well. (I was only using the last piece of code you had so I don't have the <cfset r[arrayLen(r)+1] = s> in there).
So the code that worked for me is:
<cfloop query="GetStates">
<cfset gsPoint = structNew()>
<cfset gsPoint["id"] = StateID>
<cfset gsPoint["abbrev"] = StateAbbrev>
<cfset gsPoint["name"] = StateName>
<cfset arrayAppend(gsQry, gsPoint)>
</cfloop>
Thanks!