It has been a while since I made my last blog post as work has kept me very busy. Over the weekend I saw that a new jqGrid version had been released and that it contained in-built advanced searching (called multiple search). I also thought it was a good time to revisit my earlier blog post on a full CRUD grid and update it to 3.6 specifications.


This implementation uses the following jqGrid features:

  1. Inbuilt Form based Add, Edit, Delete and Search functionality
  2. Search is done using new Multiple Search feature
  3. Exploration of a few additional colModel parameters

View the Demo : this is all realtime, any updates made will be reflected in the grid.
Now, lets look at the code

The HTML is still very simple.

view plain print about
1<table id="list" class="scroll" cellpadding="0" cellspacing="0"></table>
2<div id="pager" class="scroll"></div>
Let's see the javascript that defines our grid.
view plain print about
1<script>
2        
3    $(document).ready(function()
4        {    
5            $("#list").jqGrid(
6            {
7                url:'Users2.cfc?method=getUsers', //CFC that will return the users
8                datatype: 'json', //We specify that the datatype we will be using will be JSON
9                colNames:['ID','First Name','Last Name', 'Display Name','User Name'], //Column Names
10                //The Column Model to define the data. Note you can make columns non sortable, specify width, alignment, etc.
11                colModel :[
12                    {name:'id',index:'id', width:75, sorttype:"int",editable:false,searchoptions:{sopt:['eq','lt','le','gt','ge']}},
13                    {name:'FirstName',index:'FirstName', width:150, sorttype:"text",searchoptions:{sopt:['eq','bw','ew','cn']},
14                        editable:true,edittype:"text",editoptions:{size:50},editrules:{required:true},formoptions:{elmprefix:"(*)"}},
15                    {name:'LastName',index:'LastName', width:150, align:"left",sorttype:"text",searchoptions:{sopt:['eq','bw','ew','cn']},
16                        editable:true,edittype:"text",editoptions:{size:50},editrules:{required:true},formoptions:{elmprefix:"(*)"}},
17                    {name:'DisplayName',index:'DisplayName', width:150, align:"left",sorttype:"text",searchoptions:{sopt:['eq','bw','ew','cn']},
18                        editable:true,edittype:"text",editoptions:{size:50},editrules:{required:true},formoptions:{elmprefix:"(*)"}},
19                    {name:'UserName',index:'UserName', width:150,align:"left",sorttype:"text",searchoptions:{sopt:['eq','bw','ew','cn']},
20                        editable:true,edittype:"text",editoptions:{size:50},editrules:{required:true},formoptions:{elmprefix:"(*)"}}
21                ],//searchoptions parameter is used to limit the operators available during search
22                pager: $('#pager'), //The div we have specified, tells jqGrid where to put the pager
23                rowNum:20, //Number of records we want to show per page
24                rowList:[20,30,40,50], //Row List, to allow user to select how many rows they want to see per page
25                sortorder: "asc", //Default sort order
26                sortname: "ID", //Default sort column
27                viewrecords: true, //Shows the nice message on the pager
28                imgpath: '/Test/themes/basic/images', //Image path for prev/next etc images
29                caption: 'Users', //Grid Name
30                recordtext: "Record {0} - {1} of {2}",//Pager information to show
31                rownumbers: true,//Show row numbers
32                rownumWidth: "40",//Width of the row numbers column
33                sortable: true,
34                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
35                mtype:'POST',    
36                toolbar:[true,"top"], //Shows the toolbar at the top. We will use it to display user feedback
37                //The JSON reader. This defines what the JSON data returned from the CFC should look like
38                jsonReader: {
39                    root: "ROWS", //our data
40 page: "PAGE", //current page
41 total: "TOTAL", //total pages
42 records:"RECORDS", //total records
43                    userdata:"USERDATA", //Userdata we will pass back for feedback
44                    cell: "", //Not Used
45                    id: "0" //Will default to first column
46                    },
47                editurl:"Users2.cfc?method=addeditUser" //The Add/Edit function call                
48                }                
49            ).navGrid('#pager',
50                {
51                    search:true,searchtitle:"Search",//title set for hover over display
52                    edit:true,edittitle:"Edit User",
53                    add:true,addtitle:"Add User",
54                    del:true,deltitle:"Delete User"
55                },
56            // Edit Options. savekey parameter will keybind the Enter key to submit.
57            {editCaption:"Edit User",edittext:"Edit",closeOnEscape:true, savekey: [true,13],errorTextFormat:commonError,width:"450"
58                ,reloadAfterSubmit:true,bottominfo:"Fields marked with (*) are required",top:"60",left:"70"},
59            {addCaption:"Add User",closeOnEscape:true,savekey: [true,13],errorTextFormat:commonError,width:"450"
60                ,reloadAfterSubmit:true,bottominfo:"Fields marked with (*) are required",top:"60",left:"70"}, //Add Options
61            {url:"Users2.cfc?method=delUser",caption:"Delete User",closeOnEscape:true,errorTextFormat:commonError,top:"60",left:"70",
62                reloadAfterSubmit:true}, //Delete Options
63             //Search Options. multipleSearch parameter lets it know we are going to use the new advanced search feature
64            {errorTextFormat:commonError,Find:"Search",closeOnEscape:true,caption:"Search Users",multipleSearch:true,closeAfterSearch:true}
65            );            
66        
67        //Function will be called when add/edit encounters an error. The returned message is what will be shown to user    
68        function commonError(data)
69        {            
70            return "Error Occured during Operation. Please try again";
71        }
72            
73        }
74    );
75    </script>

Hopefully the comments in the code help understand what is going on. I am taking advantage of new colModel definitions that we can specify to help the multiple search feature. You can see this in the searchoptions parameter specification. The sopt array array specifies which operators can be associated with the search field, so for example, ID which is an integer can only handle equal, less than, less than equal to, greater than and greater than equal to operators. While the rest of the search fields which are text fields use operators more suited to text based searching.

We are also using the formoptions parameter and passing the elmprefix value. This value we have passed will be appended to our input elements. In this case we will use it to tell the user which fields are required.

In addition, we have specified additional parameters for the Add/Edit/Delete options. These include specifying the savekey (this is set to Enter, so pressing Enter will submit the Add/Edit form), closeOnEscape is set to true (you can close those modal dialogs with the Esc key). We have also specified our error handler for Add/Edit which is the function commonError. It will return a generic error message to the user. In order to use the multiple search implementation in jqGrid we have specified the multipleSearch parameter to true (so simple it is).

We also have our CFC component that will form the bridge between our grid and database.

view plain print about
1<cfcomponent output="false">
2    
3    <cffunction name="getUsers" access="remote" returnformat="json">
4        <cfargument name="page" required="no" default="1" hint="Page user is on">
5     <cfargument name="rows" required="no" default="10" hint="Number of Rows to display per page">
6     <cfargument name="sidx" required="no" default="" hint="Sort Column">
7     <cfargument name="sord" required="no" default="ASC" hint="Sort Order">
8     <cfargument name="nd" required="no" default="0">
9     <cfargument name="_search" required="no" default="false">
10     <cfargument name="filters" required="no" default="">
11        
12        <cfset var arrUsers = ArrayNew(1)>
13        <cfset var strMsg = "">
14        <cfset var strMsgType = "Success">
15        <cfset var records = "">
16        <cfset var blnSearch = Arguments._search>
17        <cfset var strSearch = "">    
18        
19        <!--- Determine whether this a search call and if so build our search parameters --->
20        <cfif Arguments.filters NEQ "" AND blnSearch>
21            <cfset stcSearch = DeserializeJSON(Arguments.filters)>
22            <cfif isDefined("stcSearch.groupOp")>
23                <cfset strSearch = buildSearch(stcSearch)>
24            </cfif>            
25        </cfif>
26        
27        <cfquery name="selUsers" datasource="RIADemo" result="res">
28            SELECT
29                ID, FirstName, LastName, DisplayName, UserName
30            FROM
31                Users
32            <cfif blnSearch AND strSearch NEQ "">
33                #PreserveSingleQuotes(strSearch)#
34            </cfif>
35            ORDER BY #sidx# #sord#                
36        </cfquery>
37        
38        
39        
40        <cfset records = selUsers>
41        
42        
43        <!--- Calculate the Start Position for the loop query.
44        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.
45        If you go to page 2, you start at (2-)1*4+1 = 5 --->
46        <cfset start = ((arguments.page-1)*arguments.rows)+1>
47        
48        <!--- Calculate the end row for the query. So on the first page you go from row 1 to row 4. --->
49        <cfset end = (start-1) + arguments.rows>
50        
51        <!--- When building the array --->
52        <cfset i = 1>
53        
54        <cfloop query="records" startrow="#start#" endrow="#end#">
55            <!--- Array that will be passed back needed by jqGrid JSON implementation --->        
56            <cfset arrUsers[i] = [#ID#,#FirstName#,#LastName#,#DisplayName#,#UserName#]>
57            <cfset i = i + 1>            
58        </cfloop>
59        
60        <!--- Calculate the Total Number of Pages for your records. --->
61        <cfset totalPages = Ceiling(records.recordcount/arguments.rows)>
62        
63        <!--- The JSON return --->
64        <cfset strReturn = {total=#totalPages#,page=#Arguments.page#,records=#records.recordcount#,rows=arrUsers}>
65        
66        <cfreturn strReturn>
67        
68    </cffunction>
69    
70    <cffunction name="addeditUser" access="remote" hint="Add or Edit User Data" returnformat="json">
71        <cfargument name="FirstName" required="yes" hint="Field that was Added or editted">
72        <cfargument name="LastName" required="yes" hint="Field that was Added or editted">
73        <cfargument name="DisplayName" required="yes" hint="Field that was Added or editted">
74        <cfargument name="UserName" required="yes" hint="Field that was Added or editted">        
75        <cfargument name="ID" required="yes" hint="the User that was Added or editted">
76        <cfargument name="oper" required="no" default="edit" hint="Whether this is an add or edit">
77        
78        <cfset var strMsg = "">
79        <cfset var strMsgType = "Success">
80        <cfset var userdata = "">
81        
82        <cfif oper EQ "edit">
83            <cfset strMsg = "User Editted">        
84            <!--- Take the data, update your record. Simple. --->
85            <cftry>
86                <cfquery name="editUser" datasource="RIADemo">
87                    UPDATE
88                        Users
89                    SET
90                        FirstName = '#Arguments.FirstName#',
91                        LastName = '#Arguments.LastName#',
92                        DisplayName = '#Arguments.DisplayName#',
93                        UserName = '#Arguments.UserName#'                        
94                    WHERE
95                        ID = #Val(Arguments.ID)#
96                </cfquery>
97            <cfcatch type="any">
98                <!--- Error, return message --->
99                <cfset strMsgType = "Error">
100                <cfset strMsg = "Error Occured when editing user. An Error report has been submitted to support.">
101                <cfthrow message="Error Occured During Edit">
102            </cfcatch>
103            </cftry>
104        <cfelse>
105            <cfset strMsg = "User Added">
106            <cftry>                
107            <cfquery name="addUser" datasource="RIADemo">
108                INSERT INTO USERS(FirstName,LastName,DisplayName,UserName)
109                VALUES ('#Arguments.FirstName#','#Arguments.LastName#','#Arguments.DisplayName#','#Arguments.UserName#')
110            </cfquery>
111            <cfcatch>
112                <!--- Error, return message --->
113                <cfset strMsgType = "Error">
114                <cfset strMsg = "Error occured when Adding User. An Error report has been submitted to support.">
115                <cfthrow message="Error Occured During Add">
116            </cfcatch>
117            </cftry>
118        </cfif>
119        <!--- We just need to pass back some user data for display purposes --->
120        <cfset userdata = {type="#strMsgType#",msg="#strMsg#"}>
121        <cfset strReturn = {userdata=#userdata#}>
122        
123        <cfreturn strReturn>
124        
125    </cffunction>
126    
127    <cffunction name="delUser" access="remote" hint="Delete Selected User" returnformat="json">        
128        <cfargument name="id" required="yes" hint="id to delete">
129        
130        <cfset var strMsg = "">
131        <cfset var strMsgType = "Success">
132        <cfset var userdata = "">
133        <cfset var delUser = "">
134        
135        <cftry>
136            <cfset strMsg = "Deleted User">
137            <cfquery name="delUser" datasource="RIADemo">
138                DELETE FROM Users WHERE ID = #Val(Arguments.id)#
139            </cfquery>
140        <cfcatch>
141            <!--- Error, return message --->
142            <cfset strMsgType = "Error">
143            <cfset strMsg = "Error occured when Deleting User. An Error report has been submitted to support.">
144        </cfcatch>
145        </cftry>
146        
147        <cfset userdata = {type="#strMsgType#",msg="#strMsg#"}>
148        <cfset strReturn = {userdata=#userdata#}>
149        
150        <cfreturn strReturn>
151        
152    </cffunction>
153    
154    <cffunction name="buildSearch" access="private" hint="Build our Search Parameters">
155        <cfargument name="stcSearch" required="true">
156        
157        <!--- strOp will be either AND or OR based on user selection --->
158        <cfset var strOp = stcSearch.groupOp>
159        <cfset var arrFilter = stcSearch.rules>
160        <cfset var strSearch = "">
161        <cfset var strSearchVal = "">
162        
163        <!--- Loop over array of passed in search filter rules to build our query string --->
164        <cfloop array="#arrFilter#" index="arrIndex">
165            <cfset strField = arrIndex["field"]>
166            <cfset strOp = arrIndex["op"]>
167            <cfset strValue = arrIndex["data"]>
168            
169            <cfset strSearchVal = buildSearchArgument(strField,strOp,strValue)>
170            
171            <cfif strSearchVal NEQ "">
172                <cfif strSearch EQ "">
173                    <cfset strSearch = "WHERE (#PreserveSingleQuotes(strSearchVal)#)">
174                <cfelse>
175                    <cfset strSearch = strSearch & "#strOp# (#PreserveSingleQuotes(strSearchVal)#)">                
176                </cfif>
177            </cfif>
178            
179        </cfloop>
180        
181        <cfreturn strSearch>
182                
183    </cffunction>
184    
185    <cffunction name="buildSearchArgument" access="private" hint="Build our Search Argument based on parameters">
186        <cfargument name="strField" required="true" hint="The Field which will be searched on">
187        <cfargument name="strOp" required="true" hint="Operator for the search criteria">
188        <cfargument name="strValue" required="true" hint="Value that will be searched for">
189        
190        <cfset var searchVal = "">
191        
192        <cfif Arguments.strValue EQ "">
193            <cfreturn "">
194        </cfif>
195        
196        <cfscript>
197            switch(Arguments.strOp)
198            {
199                case "eq":
200                    //ID is numeric so we will check for that
201                    if(Arguments.strField EQ "id")
202                    {
203                        searchVal = "#Arguments.strField# = #Arguments.strValue#";
204                    }else{
205                        searchVal = "#Arguments.strField# = '#Arguments.strValue#'";
206                    }
207                    break;                
208                case "lt":
209                    searchVal = "#Arguments.strField# < #Arguments.strValue#";
210                    break;
211                case "le":
212                    searchVal = "#Arguments.strField# <= #Arguments.strValue#";
213                    break;
214                case "gt":
215                    searchVal = "#Arguments.strField# > #Arguments.strValue#";
216                    break;
217                case "ge":
218                    searchVal = "#Arguments.strField# >= #Arguments.strValue#";
219                    break;
220                case "bw":
221                    searchVal = "#Arguments.strField# LIKE '#Arguments.strValue#%'";
222                    break;
223                case "ew":                    
224                    searchVal = "#Arguments.strField# LIKE '%#Arguments.strValue#'";
225                    break;
226                case "cn":
227                    searchVal = "#Arguments.strField# LIKE '%#Arguments.strValue#%'";
228                    break;
229            }            
230        </cfscript>
231        
232        <cfreturn searchVal>
233    
234    </cffunction>
235    
236</cfcomponent>

Source Code (cfm/cfc) Here.
Feel free to contact me if you have any questions and I will try to help you out.