Creating a Simple Database Web Search Part 2 --ColdFusion

In the first part of this series, I showed how to create a Master/Detail page set using built in Dreamweaver tools, and then add a basic search form querying against one field in the database. What if you want more than that though? The Dreamweaver recordset dialog box allows one filter against one field. This is sometimes useful, but usually not. Typically you might also want to allow other HTML fields into the search. You will want to search allowing multiple search words. Finally, you might want to search multiple database fields. I will address all three situations in this article, which will be targeted to ColdFusion users only. Part 2 for PHP users was released here. The basic script used to start the exercises, which was the completed results page from Part 1, is saved as results_start.cfm in the accompanying download.

Multiple HTML Search Fields

The first point to address is what to do if you have multiple search fields on a web page. Let's say you have a keyword search box as presented in the last article that searches for text in a product name, but also want to allow a user to search a price range. A second search field would give the end user an opportunity to enter a maximum price. A third field would allow a minimum price. You can't do this in the simple Recordset dialog box any more. Open the recordset and switch to the Advanced view, if it's not already there. The SQL code looks something like this:

SELECT ProductID, ProductName, UnitPrice, UnitsInStock
FROM dbo.Products
WHERE ProductName LIKE %#URL.Search#%

This is Dreamweaver-specific SQL code. Dreamweaver automatically replaces the parameter with a <cfqueryparam> tag. The dynamic parameter necessary for the code to work are provided by the Recordset server behavior and show in Code View:

<cfquery name="Recordset1" datasource="northwind">
SELECT ProductID, ProductName, UnitPrice, UnitsInStock, QuantityPerUnit
FROM dbo.Products
WHERE ProductName LIKE
<cfqueryparam value="%#URL.Search#%" cfsqltype="cf_sql_clob" maxlength="40">
</cfquery>

Note: Dreamweaver does not always maintain your SQL formatting. It was formatted here for readability.

We'll take the first statement and add our two new parameters to it -- URL.MinPrice and URL.MaxPrice. There are no form fields yet, but that's ok -- we want the page to work whether a user has entered something or not.

Modify the SQL to read like this in the dialog box:

SELECT ProductID, ProductName, UnitPrice, UnitsInStock, QuantityPerUnit
FROM dbo.Products
WHERE ProductName LIKE %#URL.Search#%
AND UnitPrice >= #URL.MinPrice#
AND UnitPrice <= #URL.MaxPrice#

Figure 1: Dreamweaver recordset dialog box after adding two new parameters

The variables will need to be added as shown in the following screenshots:

Figure 2: The URL.MinPrice variable

Figure 3: The URL.MaxPrice variable

And then, the URL.Search parameter will need to be changed to allow a % symbol as the default value. This ensures that when a user does not enter a search term that all results are returned -- this allows the URL.MinPrice and URL.MaxPrice values to determine search results in addition to the search box.

Figure 4: The URL.Search variable

If you save and browse to the page now, you should see all results.

Next, add two text fields to the page: MinPrice and MaxPrice. You can put these right under the search box, with labels of "Minimum Price" and "Maximum Price". If you browse the page now, you will still get all results. However, if you click the "Submit" button without entering anything, you will get no results. The default values you put into the Recordset dialog box only affect the page when the form is not submitted. We want them to also be in effect when someone doesn't fill in the field -- for example if a person searches for a specific keyword but doesn't enter a minimum or maximum price, then no records will be returned. This is not the desired outcome. There are at least two ways to handle this. The first way would be to modify the recordset code on the page and modify the SQL code if no parameters are passed. This is a good method for a hand-coded page, but we'll use another method that is more friendly to maintaining Dreamweaver recordset code. This is the code for the default parameters as it stands now:

This section of code shows what is going on: a default value is created, but if a form field is defined, the value is used in its place. We will add a section of conditional statements to set the default in the event a blank parameter is submitted:

Save the page and browse it. Now, when you click the submit button with no values in the form fields, all results are returned, however if you put a value in one of the boxes, the filters will work. For example, entering the value "50" into the MinPrice box will show only results where the price is higher than $50.

The recordset will now show a red check mark in the server behaviors panel, but will still list the fields correctly in the Bindings panel. However, editing the server behavior will now strip the hand-coded modifications. The completed page, to compare with your own, is saved as results1.cfm in the download that accompanies the article.

Dynamic SQL

To make the search even better, we'll allow the user to enter multiple keywords; this is where it gets tricky. The task of creating a dynamic SQL query is fairly straightforward, but it requires string manipulation and knowledge of SQL as well. Also, Dreamweaver has strict rules governing its recordsets, and if you change the way that the SQL is created by adding server-side conditional logic into the SQL, the recordset will cease to work in the Bindings panel. As long as you already have your recordset columns on the page where you want them, though, this isn’t a problem.

What you’ll have to do is remove the first part of the Where clause from the SQL statement while you are in Code view on the page, not from the recordset dialog box, and replace it with a new variable called sqlString. The remaining SQL statement will then look like this:

<cfquery name="Recordset1" datasource="northwind">
SELECT ProductID, ProductName, UnitPrice, UnitsInStock, QuantityPerUnit
FROM dbo.Products
#PreserveSingleQuotes(sqlString)#
AND UnitPrice >= <cfqueryparam value="#URL.MinPrice#" cfsqltype="cf_sql_numeric">
AND UnitPrice <= <cfqueryparam value="#URL.MaxPrice#" cfsqltype="cf_sql_numeric">
</cfquery>

At this point, we will cease to use the Dreamweaver recordset box. Essentially, we've replaced WHERE ProductName LIKE <cfqueryparam value="%#URL.Search#%" cfsqltype="cf_sql_clob" maxlength="40"> with #PreserveSingleQuotes(sqlString)#, and removed the parameter from the list of parameters. The PreserveSingleQuotes function is used so that ColdFusion doesn't replace our quotes from the dynamic SQL with escaped quotes.

A good trick to use is to make a copy of the original SQL statement and include it in the page within comments:

This way, if you ever need the Bindings or Server Behaviors panel functionality, you can comment out your custom code and replace it with the original code.

You will need to remove the default values for URL.Search. It is no longer needed or desired. The script takes care of default values.

The next thing to do is to add a block of code to the page to parse the text field into three possible situations:

One word or a group of words is entered.
A group of words is entered inside of quotes.
A group of words is entered separated by commas or spaces.

The first option will use the standard Where clause that was previously created. The second option will require the quotes to be stripped off before using the standard Where clause that was previously created. The third option requires that the words be separated. For instance, if the web user enters the words sauce, hot, pepper, the words need to be added to the Where clause like this:

WHERE ProductName LIKE '%sauce%' AND ProductName LIKE '%hot%' AND
ProductName LIKE '%pepper%'

The difficulty lies in the fact that there is no way of knowing how many words will be entered. You can write a short script to take care of the three situations. This pseudocode illustrates the functionality that the script will accomplish:

Define sqlString to hold the 'where' clause of the SQL statement. myField holds the incoming form field data
IF myField has quotes
  remove the quotes
  Build SQLstr as the Where clause using the phrase that had quotes around it
ELSE IF myField has commas or spaces
  separate the parts of myField
  construct a Where clause to allow for all of the substrings in myField
ELSE
  use myField as it stands and construct the Where clause
END OF IF STATEMENTS
Execute the SQL adding the Where clause to it

Following is the ColdFusion code that you should insert right before the recordset is created.

<cfif IsDefined("URL.search")>
 <cfset fieldToSearch = "ProductName">
 <cfset myField = Trim(URL.search)>
 <cfif (find('"',myField))>
 <cfset myField= Replace(myField,'"','',"all")>
 <cfset sqlString = " WHERE #fieldToSearch# LIKE '%#myField#%'">
 <cfelseif find(',',myField) OR find(' ',myField)>
 <cfset sqlString = " WHERE (">
 <cfset bellChar = chr(7)>
 <cfset myField = Replace(myField, ",", bellChar, "All")>
 <cfset myField = ReReplace(myField, "\s+", bellChar, "All")>
 <cfloop index="splitField" list="#myField#" delimiters="#bellChar#">
 <cfset sqlString = sqlString & " #fieldToSearch# LIKE '%">
 <cfset sqlString = sqlString & splitField & "%'">
 <cfif splitField NEQ ListLast(myField, "#bellChar#")>
 <cfset sqlString = sqlString & " AND ">
 </cfif>
 </cfloop>
 <cfset sqlString = "#sqlString#)">
 <cfelse>
 <cfset sqlString = " WHERE #fieldToSearch# LIKE '%#myfield#%'">
 </cfif>
<cfelse>
 <cfset sqlString = " WHERE (1 = 1) ">
</cfif>

You’ll notice that after you add the variable to the SQL statement, the recordset can no longer be tested in the Bindings panel, and attempting to expand the recordset or use server behavior will fail with an error. This is normal and merely indicates that Dreamweaver no longer recognizes the recordset as something that it generated. When you begin hand-coding, you can close the Bindings panel and Server Behaviors panel -- they are useless at this point.

If you attempt to edit a Server Behavior with a red check mark in it, you should make sure that the red check mark isn’t there because you changed the code manually. By re-editing the Server Behavior, the red check mark will disappear, but so will your hand-coded script.

To view the sqlString created by our script, put this bit of code at the top of the viewable page (after the <body> tag):

<cfoutput>The SQL code generated is: 
SELECT ProductID, ProductName, UnitPrice, UnitsInStock, QuantityPerUnit 
FROM dbo.Products 
#sqlString# 
AND UnitPrice >= #URL.MinPrice# 
AND UnitPrice <= #URL.MaxPrice# 
</cfoutput>

This will show the search query used by the script. For example, if you type in the following:

anton chef

The following will be shown as the query:

The SQL code generated is:
SELECT ProductID, ProductName, UnitPrice, UnitsInStock, QuantityPerUnit
FROM dbo.Products
WHERE ( ProductName LIKE '%anton%' AND ProductName LIKE '%chef%')
AND UnitPrice >= 0
AND UnitPrice <= 99999

Using the standard Northwind table, two products will be returned.

The page now contains our second search situation -- searching on more than one word. The file is saved as results2.cfm in the accompanying download.

Searching multiple fields

Now all we have left to add is the ability to search mulitple database fields. Our sample Products table doesn't have another text field that would be a good field to search, but all products have categories, so we'll join to the Categories table to search on CategoryName in addition to ProductName. First, create the join in the SQL on the page:

<cfquery name="Recordset1" datasource="northwind">
SELECT ProductID, ProductName, UnitPrice, UnitsInStock, QuantityPerUnit
FROM dbo.Products
INNER JOIN Categories c
ON p.CategoryID = c.CategoryID
#PreserveSingleQuotes(sqlString)#
AND UnitPrice >= <cfqueryparam value="#URL.MinPrice#" cfsqltype="cf_sql_numeric">
AND UnitPrice <= <cfqueryparam value="#URL.MaxPrice#" cfsqltype="cf_sql_numeric">
</cfquery>

There are two easy ways to make a search query work across multiple fields. The first way is to use OR statements in the query:

SELECT * FROM MyTable WHERE ProductName LIKE '%search%' OR CategoryName LIKE '%search%'

This is a good method, but would require a more complex script. We will use the easy way here: concatenate the fields. A sample search query would look like this:

SELECT * FROM MyTable WHERE ProductName + CategoryName LIKE '%search%'

The fields are concatenated on the WHERE clause only. To create this funtionality, simply change this line in the original search script...

...to this:

Note: If using MySQL, use CONCAT(ProductName, CategoryName) instead of ProductName + CategoryName

Now, the combined field will be searched each time. A search for "seafood" turns up 12 records. (Seafood does not appear in any of the product names.) A search for "seafood,crab" turns up one result.

The resulting script is saved as results3.cfm in the accompanying download.

Conclusion

There are many ways to create a database search, but a simple database search can be made slightly more robust with a few carefully placed instances of hand-coding in an otherwise automated Dreamweaver-created page.