jqGrid with ColdFusion
Readers of mine will know I'm a big fan of jQuery. Now, we have a great Ajaxified grid in CF8, but a friend wrote to ask me how to use jqGrid (http://www.trirand.com/blog/) with ColdFusion. No problem: I am a freaking guru, after all!
Four and a half hours later, a broken man, having enlisted help from my colleague, Maciej, I finally have the answer...
Here's the HTML:
<html>
<head>
<title>jqGrid Demo</title>
<link rel="stylesheet" type="text/css" media="screen" href="/jqGrid-3.3/themes/basic/grid.css" />
<link rel="stylesheet" type="text/css" media="screen" href="/jqGrid-3.3/themes/jqModal.css" />
<script src="/jqGrid-3.3/jquery.js" type="text/javascript"></script>
<script src="/jqGrid-3.3/jquery.jqGrid.js" type="text/javascript"></script>
<script src="/jqGrid-3.3/js/jqModal.js" type="text/javascript"></script>
<script src="/jqGrid-3.3/js/jqDnR.js" type="text/javascript"></script>
<script type="text/javascript">
$(document).ready(function(){
$("#list").jqGrid({
url:'InvoiceMgr.cfc?method=getInvoices',
datatype: 'json',
colNames:['Inv No','Date', 'Amount','Tax','Total','Notes'],
colModel :[
{name:'id',index:'id', width:60, sorttype:"int"},
{name:'invdate',index:'invdate', width:90, sorttype:"date"},
{name:'amount',index:'amount', width:80, align:"right",sorttype:"float"},
{name:'tax',index:'tax', width:80, align:"right",sorttype:"float"},
{name:'total',index:'total', width:80,align:"right",sorttype:"float"},
{name:'note',index:'note', width:150, sortable:false}],
pager: $('#pager'),
rowNum:10,
rowList:[10,20,30],
sortorder: "desc",
viewrecords: true,
imgpath: '/jqGrid-3.3/themes/basic/images',
caption: 'Invoices',
jsonReader: {
root: "ROWS",
page: "PAGE",
total: "TOTAL",
cell: "",
id: "id"
}}
);
});
</script>
</head>
<body>
<table id="list" class="scroll"> <div id="pager" class="scroll" style="text-align:center;"> </body>
</html>
And here's the CFC that it calls for data:
<cfcomponent displayname="InvoiceMgr" extends="halogen.Object" output="false">
<cffunction name="getInvoices" access="remote" output="false" returnformat="json">
<cfset invoices = ArrayNew(1) />
<cfquery datasource="test" name="q">
SELECT * FROM invheader
</cfquery>
<cfloop query="q">
<cfset invoices[currentrow] = [#invid#, '#invdate#', #amount#, #tax#, #total#, #note#]>
</cfloop>
<cfset str = {total=1, page=1, records=#q.recordcount#, rows=invoices}>
<cfreturn str />
</cffunction>
</cfcomponent>
Just in case you ever need to know...
<head>
<title>jqGrid Demo</title>
<link rel="stylesheet" type="text/css" media="screen" href="/jqGrid-3.3/themes/basic/grid.css" />
<link rel="stylesheet" type="text/css" media="screen" href="/jqGrid-3.3/themes/jqModal.css" />
<script src="/jqGrid-3.3/jquery.js" type="text/javascript"></script>
<script src="/jqGrid-3.3/jquery.jqGrid.js" type="text/javascript"></script>
<script src="/jqGrid-3.3/js/jqModal.js" type="text/javascript"></script>
<script src="/jqGrid-3.3/js/jqDnR.js" type="text/javascript"></script>
<script type="text/javascript">
$(document).ready(function(){
$("#list").jqGrid({
url:'InvoiceMgr.cfc?method=getInvoices',
datatype: 'json',
colNames:['Inv No','Date', 'Amount','Tax','Total','Notes'],
colModel :[
{name:'id',index:'id', width:60, sorttype:"int"},
{name:'invdate',index:'invdate', width:90, sorttype:"date"},
{name:'amount',index:'amount', width:80, align:"right",sorttype:"float"},
{name:'tax',index:'tax', width:80, align:"right",sorttype:"float"},
{name:'total',index:'total', width:80,align:"right",sorttype:"float"},
{name:'note',index:'note', width:150, sortable:false}],
pager: $('#pager'),
rowNum:10,
rowList:[10,20,30],
sortorder: "desc",
viewrecords: true,
imgpath: '/jqGrid-3.3/themes/basic/images',
caption: 'Invoices',
jsonReader: {
root: "ROWS",
page: "PAGE",
total: "TOTAL",
cell: "",
id: "id"
}}
);
});
</script>
</head>
<body>
<table id="list" class="scroll"> <div id="pager" class="scroll" style="text-align:center;"> </body>
</html>
And here's the CFC that it calls for data:
<cfcomponent displayname="InvoiceMgr" extends="halogen.Object" output="false">
<cffunction name="getInvoices" access="remote" output="false" returnformat="json">
<cfset invoices = ArrayNew(1) />
<cfquery datasource="test" name="q">
SELECT * FROM invheader
</cfquery>
<cfloop query="q">
<cfset invoices[currentrow] = [#invid#, '#invdate#', #amount#, #tax#, #total#, #note#]>
</cfloop>
<cfset str = {total=1, page=1, records=#q.recordcount#, rows=invoices}>
<cfreturn str />
</cffunction>
</cfcomponent>
Thanks
but I'd seen the demos, and the other options - all along i knew what i was after, and now that I've got it up and running, I love it. Well worth the time to sort it out, and i sharpened my cf, xml and jquery skills in the process.
I just upgraded my little CF CMS to use the newest version of jqgrid, with the addition of inline editing and searchable fields via ajax... incredible stuff, and by far the most impressive (and probably most underappreciated) javascript plugin I've laid eyes on so far. once you get the syntax just right, the simplicity of use is just awesome.
I really like seeing all of the love between CF users and jQuery - to me, jQuery and the good solid plugins like jQgrid make client-side scripting just as easy and powerful as CF makes the dynamic stuff. The more people use the two together and get how fun and easy it can be to make your pages sing, the better it is for both communities, and for all of us web-folk in general.
Thanks for sharing - I'll try and do the same w/ my xml version before long.
I tried this example and I get a blank table grid with no errors and no results. Did you have this issue?
Thank you,
Eric
It works as presented in the tutorial.
Thanks!
Eric
From my experience the grid loads blank when the xml response is not actually returning xml. But... it's blank, so how can you see what is going on?
Use the 'console' in firebug to look at the http request being passed for the xml file. Click the request url shown to see the server response that is happening behind the scenes, often a CF error page explaining exactly what the problem is.
That's always a pleasure to read my favorit guru !
I have just tried your exemple and Coldfusion return the following error : "ColdFusion was looking at the following text: [". Did this example work with Coldfusion 7 ?
Thanks
Boris
The example is done for coldfusion 8. With Coldfusion 7, we have to use the CFjson.CFC component to encode data to Json. I had as well to modify a little the CFjson.CFC because it returned data with cote for numbers.
I don't know if my code is the best practice but that's working for me. Here is my code :
<cffunction name="getListeProjets" access="remote" output="true" returnformat="json" hint="Search the list of all the projects">
<cfargument name="page" required="no" default="1" hint="Page user is on">
<cfargument name="rows" required="no" default="10" hint="Number of Rows to display per page">
<cfargument name="sidx" required="no" default="" hint="Sort Column">
<cfargument name="sord" required="no" default="ASC" hint="Sort Order">
<cfset projets = ArrayNew(1) />
<cfquery datasource="hbm" name="getListeProjetsQuery">
SELECT Projet_ID, Proj_nom, proj_reference, proj_date_creation, proj_pt_ID, proj_version
FROM projets
<!--- Sorting Here --->
<cfif Arguments.sidx NEQ "">
ORDER BY #Arguments.sidx# #Arguments.sord#
<cfelse>
ORDER BY ID #Arguments.sord#
</cfif>
</cfquery>
<!--- Calculate the Total Number of Pages for your records. --->
<cfset totalPages = Ceiling(getListeProjetsQuery.recordcount/arguments.rows)>
<!--- Calculate the Start Position for the loop query.
So, if you are on 1st page and want to display 4 rows per page, for first page you start at: (1-1)*4+1 = 1.
If you go to page 2, you start at (2-)1*4+1 = 5 --->
<cfset start = ((arguments.page-1)*arguments.rows)+1>
<!--- Calculate the end row for the query. So on the first page you go from row 1 to row 4. --->
<cfset end = (start-1) + arguments.rows>
<cfset thestruct = StructNew()>
<cfset thestruct["page"] = #Arguments.page#>
<cfset thestruct["total"] = #totalPages#>
<cfset thestruct["records"] = #getListeProjetsQuery.recordcount#>
<cfset thestruct["rows"] = arraynew(1)>
<!--- When building the array --->
<cfset i = 1>
<cfloop query="getListeProjetsQuery" startrow="#start#" endrow="#end#">
<!--- Array that will be passed back needed by jqGrid JSON implementation --->
<cfscript>
thestruct.rows[i] = StructNew();
thestruct.rows[i].projet_id = getListeProjetsQuery.Projet_ID[i];
thestruct.rows[i].proj_date_creation = getListeProjetsQuery.proj_date_creation[i];
thestruct.rows[i].proj_nom = getListeProjetsQuery.proj_nom[i];
thestruct.rows[i].proj_reference = getListeProjetsQuery.proj_reference[i];
thestruct.rows[i].proj_pt_id = getListeProjetsQuery.proj_pt_ID[i];
thestruct.rows[i].proj_version = getListeProjetsQuery.proj_version[i];
</cfscript>
<cfset i = i + 1>
</cfloop>
<cfinvoke component="intranet/controller/commun/JSON" method="encode" data="#thestruct#" returnvariable="result" />
<cfoutput>#result#</cfoutput>
<cfreturn />
</cffunction>
Where did you get themes/basic/grid.css?
I've used your code, the only problem I am having is returning the records back to the cfm page. I can get the
headers to appear.
Inv No Date Amount Tax Total Notes
The cfc does not error. It actually returns data, if I nav to the page. I just can't get that data to return to the cfm page. How did you manage to get the data to appear on the cfm page. I do have debugging turned off.