FrontPage Tips by Ward Cameron Enterprises
Take Your Web Site to New Levels

Using FrontPage to Search Multiple Fields AND Tables in the Same Database

This tutorial is part of a larger discussion on creating search forms for your FrontPage Database website. Click here to read the full tutorial.

The process involves several steps

1. Searching begins with website design

2. Create a Progressive Search Form

3. Add an All Values option to your Search Form

4. Using Drop-down Menus with Multiple Select

5. Add a Text Search Field Using the DRW

6. Search Multiple Fields in the Same Table

7. Search Multiple Fields or Tables in the Same Database

In our least tutorial, I showed you how to search multiple fields in the same table. What happens when the fields you want to search are contained in different tables? Using a little FrontPage Magic, we can create a custom query that will let you search multiple tables as easily as you can search a single table. 

As an example, in MountainNature.com, I had three tables that included species data, one each for plants, animals and birds. Each of the tables contained fields for Common Name and Latin Name. I wanted to create a search form that would allow a user to put in either the common OR the Latin name and still return a result and have it return a result regardless of whether the name was in the plant, animal or bird table.

We accomplish this through the use of a Union Query. We discussed this in an earlier tutorial. In this case, we'll use a custom sql query to achieves our desired goals. In step 2 of the Database Results Wizard, I used the following sql:

SELECT IndexName, CommonName, LatinName
FROM BirdIndexName
WHERE CommonName LIKE '::CommonName::'
UNION
SELECT IndexName, CommonName, LatinName
FROM PlantIndexName
WHERE CommonName LIKE '::CommonName::'
UNION
SELECT IndexName, CommonName, LatinName
FROM AnimalIndexName
WHERE CommonName LIKE '::CommonName::'
ORDER BY IndexName

This code uses a union query to connect the plants, animals and bird tables together and then uses the WHERE clause to check my search term against the CommonName field in each of the tables.

Let's create an example using the Northwind Database

In this tutorial, we'll create a similar example using the Northwind database. In this case, using the Customers table, we'll create a form that will allow you to find records regardless of whether they enter a company name OR a contact name.

On a blank page, select Insert Database Results. Select the Northwind database connection and click the Next button to move to Step 2. This is where we'll enter the Custom SQL code to make this custom search form work.

Type the following into the custom sql:

SELECT CompanyName, ContactName
FROM Customers
WHERE CompanyName LIKE '::SearchTerm::'
UNION
SELECT CompanyName, ContactName
FROM Suppliers
WHERE CompanyName LIKE '::SearchTerm::'
ORDER BY CompanyName

This tells the Database Results Wizard (DRW) to find records by comparing the field SearchTerm against the CompanyName field in either the Customers OR the Suppliers tables.

If you want to take this one step further and have the search form find a match in either the CompanyName OR ContactName field in either of the Customers OR the Suppliers tables, then change the custom query to the following:

SELECT CompanyName, ContactName
FROM Customers
WHERE CompanyName LIKE '::SearchTerm::'
OR ContactName LIKE '::SearchTerm::'
UNION
SELECT CompanyName, ContactName
FROM Suppliers
WHERE CompanyName LIKE '::SearchTerm::'
OR ContactName LIKE '::SearchTerm::'
ORDER BY CompanyName

In this case, all we've done is added two more lines of sql code (highlighted in yellow) to tell it to also check the ContactName field.

Click OK to close the Custom Query window and then Next to move to Step 3.

Click the More Options button and type "Type a Search Term in the Form Provided Above" into the Message to Display if No Records Returned field. This will prompt the user to type a search term when the form is first opened.

Click next to step through the DRW, selecting Table - one record per line in Step 4 and finally Add Search Form in Step 5. Your Database Results Wizard should resemble the following:

Don't forget to use wild cards to make your search more effective. In these search forms, the percent symbol (%) acts as a wild card.

1. Find names that begin with a letter or phrase by placing the percent sign after the letter or phrase (phrase%).
2. Find names or companies that contain a phrase by enclosing the phrase within percent signs (%the%).
3. Find names or companies that end with a letter or phrase by entering %phrase into the search term.

You can give it a try here:

Don't forget that you are not limited to using the built-in search form as part of the DRW.

This versatile technique helps you smash through the limits placed upon you by most simple search forms. Now, you can take your search forms to the next level...and all it took was a little FrontPage Magic.