Using jQuery based jqGrid with ColdFusion: Part 1

<link rel="stylesheet" type="text/css" media="screen" href="themes/basic/grid.css" />
<link rel="stylesheet" type="text/css" media="screen" href="themes/jqModal.css" />	
<script src="jquery.js" type="text/javascript"></script>
<script src="jquery.jqGrid.js" type="text/javascript"></script>
<script src="js/jqModal.js" type="text/javascript"></script>
<script src="js/jqDnR.js" type="text/javascript"></script>

<table id="list" class="scroll" cellpadding="0" cellspacing="0"></table>
<div id="pager" class="scroll" style="text-align:center;"></div>
<div id="mysearch"></div>

$(document).ready(function()
		{
			$("#list").jqGrid(
			{
				url:'Users.cfc?method=getUsers', //CFC that will return the users
				datatype: 'json', //We specify that the datatype we will be using will be JSON
				colNames:['ID','FirstName','LastName', 'DisplayName','UserName','UserAccountingCode','Phone'], //Column Names
				//The Column Model to define the data. Note you can make columns non sortable, specify width, alignment, etc.
				colModel :[
					{name:'id',index:'id', width:50, sorttype:"int"},
					{name:'FirstName',index:'FirstName', width:150, sorttype:"string"},
					{name:'LastName',index:'LastName', width:150, align:"left",sorttype:"string"},
					{name:'DisplayName',index:'DisplayName', width:150, align:"left",sorttype:"string"},
					{name:'UserName',index:'UserName', width:150,align:"left",sorttype:"string"},
					{name:'UserAccountingCode',index:'UserAccountingCode', width:150, sortable:false},
					{name:'Phone',index:'Phone', width:150, sortable:false}
				],
				pager: $('#pager'), //The div we have specified, tells jqGrid where to put the pager
				rowNum:4, //Number of records we want to show per page
				rowList:[4,8,12], //Row List, to allow user to select how many rows they want to see per page
				sortorder: "asc", //Default sort order
				sortname: "ID", //Default sort column
				viewrecords: true, //Shows the nice message on the pager
				imgpath: '/PLite/Test/themes/basic/images', //Image path for prev/next etc images
				caption: 'Users', //Grid Name
				height:'auto', //I like auto, so there is no blank space between. Using a fixed height can mean either a scrollbar or a blank space before the pager
				recordtext:'Total Records', //On the demo you will notice "7 Total Records" - The Total Reocrds text comes from here
				pgtext:'/',//You notice the 1/3, you can change the /. You can make it say 1 of 3
				editurl:"Users.cfc?method=getUsers",//Not used right now.
				toolbar:[true,"top"],//Shows the toolbar at the top. I will decide if I need to put anything in there later.
				//The JSON reader. This defines what the JSON data returned from the CFC should look like
				jsonReader: {
					root: "ROWS", //our data
					page: "PAGE", //current page
					total: "TOTAL", //total pages
					records:"RECORDS", //total records
					cell: "", //not used
					id: "0" //will default first column as ID
					}
				}
			);			
			
		}
	);

<cffunction name="getUsers" access="remote" returnformat="json">
		<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 var arrUsers = ArrayNew(1)>
		
		
		<cfquery name="selUsers" datasource="RIADemo">
			SELECT
				ID, FirstName, LastName, DisplayName, UserName, UserAccountingCode, Phone
			FROM
				Users
			<!--- Sorting Here --->
			<cfif Arguments.sidx NEQ "">	
				ORDER BY #Arguments.sidx# #Arguments.sord#  
			<cfelse>
				ORDER BY ID #Arguments.sord#
			</cfif>
			
			
		</cfquery>
		
		<!--- 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>
		
		<!--- When building the array --->
		<cfset i = 1>
		
		<cfloop query="selUsers" startrow="#start#" endrow="#end#">
			<!--- Array that will be passed back needed by jqGrid JSON implementation --->		
			<cfset arrUsers[i] = [#ID#,#FirstName#,#LastName#,#DisplayName#,#UserName#,#UserAccountingCode#,#Phone#]>
			<cfset i = i + 1>			
		</cfloop>
		
		<!--- Calculate the Total Number of Pages for your records. --->
		<cfset totalPages = Ceiling(selUsers.recordcount/arguments.rows)>
		
		<!--- The JSON return. 
			Total - Total Number of Pages we will have calculated above
			Page - Current page user is on
			Records - Total number of records
			rows = our data 
		--->
       <cfset stcReturn = {total=#totalPages#,page=#Arguments.page#,records=#selUsers.recordcount#,rows=arrUsers}>
		
	<cfreturn stcReturn>
		
	</cffunction>