Spry Search Within Multiple Search Fields

In this article Alex will explain how you can, quick and easy, use the Spry Search from the Spry Data Utilities Toolkit to perform a search within multiple search fields.

Introduction

One of the most frequently asked questions on Spry Data Utilities Toolkit is "How to use Spry Search with multiple search fields?".
The short answer is this: You have to do it EXACTLY the same way as you would without Spry.

The steps are as follows:

1.       Add all the form fields required to filter your Recordset.

2.       Set up your Recordset to select records based on the values from those fields.

Example #1: Exact match for two or more fields

In this example we have a database table user_accounts. Two of the columns in the table are First_Name and Last_Name. The goal is to build the functionality which would allow you to search this table for a combination of first and last name. This video shows step-by-step how to build a single field search (by first name only). We just need to expand this functionality by adding the second field - last name.

1. Using Forms tab on the Insert bar add another text field to your search form and name it the same as the database column you will be searching - Last_Name.
   
   
2. Modify the Recordset to include the newly created field in the search.

1. Double-click your Recordset name in the Server Behaviors panel to open the Recordset dialog:


As you can see this view allows you specify a single search field only.

2. Click "Advanced" button to switch to the Advanced view.
Once in the Advanced view click the "+" button right above the Variables list to add a new variable.
Enter a unique name in the Name field, select Text as the Type (because last name is just a piece of text, not date or number). Enter some impossible value in the Default value (this is to make sure that search is not triggered without invoking it by user) - "-1" is a good choice.
Finally specify the Runtime value. The pattern is $_POST['field_name'] where field_name is the name of your search field.
In our case it's Last_Name:



This is PHP syntax. If you are using ASP the syntax for the Runtime value would be Request.Form("Last_Name").
For ColdFusion the Runtime value is not being specified and the dialogs would look slightly different.

3. Click OK and the variable will appear in the Variables list:



Now it is time to add it to the query.

4. Place your cursor right after "WHERE First_Name=colname" in the SQL box.
Type " AND Last_Name=colname2":


For ColdFusion the syntax would be "AND Last_Name=#FORM.Last_Name#".

Click OK and save the file.
That's all! Now when you search by first name and last name the results will include only those records where both fields match entered values.

If instead you want to find records matching either first name or last name use "OR" instead of "AND" in your SQL statement:


In both cases we will be looking for an exact match.
If you want the query to find results based only on the first few characters typed by a user the SQL syntax will be slightly different:

PHP:
SELECT *
FROM user_accounts
WHERE First_Name LIKE colname% OR Last_Name LIKE colname2%
ORDER BY First_Name ASC

ASP:
SELECT *
FROM user_accounts
WHERE First_Name LIKE MMColParam% OR Last_Name LIKE MMColParam2%
ORDER BY First_Name ASC

ColdFusion:
SELECT *
FROM user_accounts
WHERE First_Name LIKE #FORM.First_Name#% OR Last_Name LIKE #FORM.Last_Name#%
ORDER BY First_Name ASC

Use the patterns described above to add more fields/variables to your search if required.