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

About Robert Zehnder

Web application developer specializing in ColdFusion/Railo and Open Source development.

Posted on November 27, 2009, in ColdFusion, jQuery. Bookmark the permalink. 20 Comments.

  1. Great piece. How could this be applied to say, an XML file as the data source?

    Thanks.

  2. That can be handled easy enough, you would just need to use the XMLSearch function to filter out the XPath that you wanted. I will throw together a quick example at lunch for you.

  3. thanks again for posting both articles. i’m learning a lot from your blog.

  4. 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.

  5. 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

  6. 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.

  7. 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

  8. thi sis how the query is looking in debugging

    SELECT FirstName
    FROM artists

    ORDER BY FirstName

  9. cannot convert the value “”” to a boolean this is coming from len(trim(search))

  10. [Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name ‘%%’.

  11. this is how search value is going to take empty tahts what i am not able to debug

  12. 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

  13. thank you, this worked for me so easily!

  1. Pingback: Tweets that mention Dynamic search filtering with jQuery « KISDigital Blog -- Topsy.com

  2. Pingback: Dynamic filtering with jQuery and XML « KISDigital Blog

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

%d bloggers like this: