Dynamic search filtering with jQuery


Today I was working on a project for a client and they wanted a way to easily search through their 400 or so users.  I wanted a way to do it dynamically (ala project search on RIAForge) so I whipped up a quick little script that does just that.  Using jQuery made is surprisingly easy, but I figured I would share.

First we set up a simple page that will contain our search box and has the div container that will hold our search box:

Search: <input id="search" type="text" />

<br />
<br />

<div id="userlist"></div>

<script type="text/javascript">

	 $('#search').bind('keyup',updateQuery);

	function updateQuery(){
		$('#userlist').load('query.cfm?search='+ $('#search').val() );
	}

	$(document).ready(function(){
		$('#userlist').load('query.cfm');
	});

</script>

Everything here is pretty self-explanatory.  I created a text input creatively named search, then bound the key up even to the updateQuery() function which in turn updates the userlist div that holds our search results.  The page that actually populates the results, query.cfm, is listed below:

<cfparam name="search" type="string" default="">

<cfquery name="q" datasource="datasource">
	SELECT MemberID, LastName, MiddleName, FirstName
	FROM members
	<cfif len(search)>
	WHERE Concat(FirstName, ' ', MiddleName, ' ', LastName)
	LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#search#%" />
	</cfif>
	ORDER BY LastName, FirstName
</cfquery>

<cfoutput>#q.recordCount# User(s) Found</cfoutput>
<br />
<br />
<cfoutput query="q"> <a href="somepage.cfm?ID=#MemberID#" style="color: blue;">#LastName#, #FirstName# #MiddleName#</a><br />
</cfoutput>

The page accepts one parameter, URL.search which is supplied by the calling page and if it is not supplied, it defaults to an empty string.  If the search parameter is sent, I am combining individual fields in the database to get the members full name and searching against that.

With a few lines of code we get a pretty cool effect.  This can be easily modified to fit any given situation so maybe someone will find it useful.

Advertisements

20 thoughts on “Dynamic search filtering with jQuery

  1. hi i just tried to implement this but is not taking nay value and it is taking empty value so that getting all values from query instead of search value.

  2. You should be able to do something like this in MS SQL. I have not used it in a few years so I may be a little rusty…

    SELECT MemberID, LastName, MiddleName, FirstName, FirstName + ‘ ‘ + MiddleName + ‘ ‘ + LastName as FullName
    FROM members

    WHERE FullName
    LIKE

    ORDER BY LastName, FirstName

  3. Grrr… Well, it stripped out all my tags, but you should get the idea. It is pretty much identical to the MySQL call with the exception that we are concatenating the first name, middle name and last name and storing it as FullName. The rest of the cfquery code remained the same.

  4. yes i tired same but len(search) value is taking empty string , there i am getting problem and when i used cfparam tag value to any name then query is working

  5. Hi. How can I use this to search a grid or a list where both grid and search box will be inside of user control that will be displayed via web part on sharepoint site and are using sql server 2008 to pull data from. Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s