Search Functionality in CFGRID

In my previous cfgrid posts we have covered following functionality of cfgrid:

1. Starting with CFGRID( part - 1 )
2. Starting with CFGRID( part - 2 )(Auto Refreshing CFGRID)
3. Export cfgrid Data or Table Data in Excel, Pdf and CSV Format(ColdFusion - 9)
4. Conditionally Change the Color Of a Cell Text In cfgrid

Today, we will see how we can implement searching functionality in cfgrid. Lets  see our cfgrid.cfm code below:

	<!--- Default page size of the grid--->
	<cfparam name="url.pageSize" default="10">
	<cfform name="logForm">
	<div>
	<b style="float:left;">Search Column: &nbsp;&nbsp;</b>
	<!--- Selects columns which you want to filter --->
	<select name="searchColumn" id="searchColumn">
	<option value="">--Select Column--</option>
	<option value="Course_ID">Course ID</option>
	<option value="Dept_ID">Department ID</option>
	<option value="CorName">Course Name</option>
	<option value="CorLevel">Course Level</option>
	</select>
	<!--- Input field to provide search text for searchings --->
	<b>&nbsp;&nbsp;Filter Text:&nbsp;&nbsp;</b>
	<input type="text" name="searchBox" id="searchBox" value="" />
	</div>
	<cfgrid format="html" name="courseGrid" pagesize="#url.pageSize#" selectmode="row" height="200"
	bind="cfc:cfgrid.getCourses({cfgridpagesize},
	{cfgridpage},
	{cfgridsortcolumn},
	{cfgridsortdirection},
	{logForm:searchColumn},{logForm:searchBox})">
	<!--- To bind a form field with grid use: {formName:formFieldName} --->
	<!--- Grid Columns --->
	<cfgridcolumn name="Course_ID" width="100" header="Course ID" headerbold="true" />
	<cfgridcolumn name="Dept_ID" width="180" header="Department ID" headerbold="true" />
	<cfgridcolumn name="CorName" width="180" header="Course Name" headerbold="true" />
	<cfgridcolumn name="CorLevel" width="180" header="Course Level" headerbold="true" />
	</cfgrid>
	</cfform>

view raw cfgrid.cfm hosted with ❤ by GitHub

Now, see the cfgrid.cfc:

	<cfcomponent output="false">
	<cffunction name="getCourses" description="This functions returns the courses" returntype="Struct"
	access="remote" output="false">
	<cfargument name="pageSize" required="true" type="numeric"
	hint="The page size specifies no of record you want to fetch" />
	<cfargument name="pageNo" required="true" type="numeric"
	hint="The page no " />
	<cfargument name="gridsortcolumn" required="true" type="string"
	hint="One coulmn name of CourseList table used for sorting the grid" />
	<cfargument name="gridsortdirection" required="true" type="string"
	hint="Sort Order" />
	<cfargument name="searchColumn" required="true" type="string"
	hint="Search Column" />
	<cfargument name="searchText" required="true" type="string" hint="Search Text" />
	<cfset local.getCourses = queryNew("") />
	<!--- If grid sort column is null then assign --->
	<cfif arguments.gridsortcolumn EQ "">
	<cfset arguments.gridsortcolumn = "Dept_ID" />
	</cfif>
	<cftry>
	<!--- Fetching record from data base--->
	<cfquery name="local.getCourses" dataSource="cfdocexamples">
	SELECT Course_ID, Dept_ID, CorNumber,CorName, CorLevel
	FROM CourseList
	<!--- If any text is passed for searching and some search column is selected
	then go for filtering option --->
	<cfif len(arguments.searchText) AND len(arguments.searchColumn)>
	WHERE
	#arguments.searchColumn# LIKE <cfqueryparam
	cfsqltype="cf_sql_varchar"
	value="%#arguments.searchText#%" />
	</cfif>
	ORDER by #arguments.gridsortcolumn# #arguments.gridsortdirection#
	</cfquery>
	<cfcatch>
	<!--- Put your error log code here --->
	<cfset local.getCourses = queryNew("") />
	</cfcatch>
	</cftry>
	<!--- Convert the query object to grid compatible structure.
	After this conversion we will get the no of record required for the page no we have passed.
	--->
	<cfreturn queryConvertForGrid(local.getCourses, arguments.pageNo, arguments.pageSize) />
	</cffunction>
	</cfcomponent>

view raw cfgrid.cfc hosted with ❤ by GitHub

All the above code is self explanatory. Hope it will help people who are new in ColdFusion and trying to learn about cfgrid.

Happy Coding!!!  :)