SQL Search

Finding information in your database can be a simple or a complex process. Depending on the search criteria, you can give your users the capability to narrow down their searches as fine as a specific date range. Suppose that you wanted to find all employees in your database who have the name John Smith; you can create a simple query in Access that selects all the records in the Customers table whose name was equivalent to the name John Smith. Your SQL statement could look similar to this:

 SELECT Name FROM Employees WHERE Name = 'John Smith' 

The result returns all the matching records. Indeed, the Dorknozzle database is filled with information that users may want access to, including the following:

• Employees: As an admin, you can perform a search on your Employees table to extract employee-specific information including name, address, city, state, and so on.

• CreditCards: Again as an admin, you can perform a search on the CreditCards table to extract all the users' credit cards.

• EmployeeStore: Probably the most important container of information, the EmployeeStore table can be searched by an employee to narrow down an employee's search for a specific product.

• Orders: As an admin, you can perform a search to determine how many items you sold on a specific day, week, or month. You could also return a statistical analysis of those results so that you could better understand the employees' ordering habits and possibly suggest products in the future. Furthermore, the shipping and receiving department might want to perform a search for all orders that need to be shipped on a specific day or that need to arrive at their destinations by a certain day. This information can help you determine what kind of shipping services to add to the parcel.

As you can see, just within the Dorknozzle database, there is plenty available to search on. Whether you are approaching the problem as an admin or an employee, the database ultimately is a warehouse of information. How you access that information is covered next.

Creating the Dorknozzle Search Page

In Chapter 23, "A Database Primer," you learned how to display dynamic data to the user. You created a query and integrated that query into your application using a server behavior that presented dynamic data. The problem with that approach is that it's hard-coded, meaning that the data the user ends up seeing always remains the same. What if your users don't want to see what you are choosing to present to them? Fortunately for you, you can allow your users to perform a search in your site based on criteria they specify through the use of form objects, recordsets/DataSets, and variables/parameters. Before we jump ahead, let's dissect a common approach to creating search functionality. Figure 27.1 shows the Amazon website and the search form you would use to search for a book.

Figure 27.1. Most websites employ some way of allowing their users to search for information.

Suppose that you have a database with a list of book titles. The user of the website types a book name; the value of that text box is dynamically appended to a WHERE or a LIKE clause in a SQL statement. The database is then queried, and the results are presented to the user in a well-structured manner. Of course, this kind of intuitive functionality isn't limited to large companies such as Amazon and eBay. You, too, can create functionality such as this using Dreamweaver. To create your own basic SQL-based search page, regardless of server model, follow these steps:

1.	Create a new page by selecting File, New. Select the Templates tab, choose the Dorknozze template called "template," and click Create.
2.	Immediately save the page as search.asp, search.aspx, search.cfm, or search.php, depending on the server model you're using.
3.	Select the Content Goes Here text in the Content editable region and delete it.
4.	With your cursor still in the editable region, choose Insert, Form, Form. With the form selected, change the value of the Action text box in the Properties Inspector to read search_results.asp, search_results.aspx, search_results.cfm, or search_results.php, depending on the server model you're using. Make sure that the GET value is selected from the Method menu. This configuration causes the value the user enters in the text field to be passed along with the URL in the address bar (more on this later).
5.	Insert a text field form object by selecting Insert, Form, Text Field. With the text field selected, enter the value txtSearch in the Name text box in the Properties Inspector.
6.	Place your cursor next to the text field and insert a Submit Button form object into the form by choosing Insert, Form, Button. Change the Value text field in the Properties Inspector to read Search. The result is shown in Figure 27.2. Figure 27.2. Create a new search page by adding the necessary Text Field and Submit Button form objects.

Save your work and test the results in the browser. Enter a value and click the Search button. As Figure 27.3 shows, the value you enter into the text field is passed along with the URL in the address bar.

Figure 27.3. Enter a value in the search text box and click Search. The value is passed along with the URL in the address bar.

Figure 27.3 shows the value being searched on displayed in the address bar as a parameter appended to the URL string. As you can see, the page isn't returning a result just yet. Let's do that next.

Creating the Dorknozzle Search Results Page

Now that the search page has been created, you'll want some way of collecting the value of the user's input and processing the result of the search. The search results page is where all the work is done. The search results page must contain the following components for the search to be processed correctly:

• The recordset or DataSet that represents the table being searched.

• The proper variables/parameters to capture the user's input from the search page.

• Dynamic text to display the result of the search to the user.

Remember that a recordset/DataSet is always used to capture the results of the table information. To create a new recordset or DataSet for the search results page, follow these steps:

1.	Create a new page by selecting File, New. Select the Templates tab, choose the Dorknozzle template titled "template," and click Create.
2.	Immediately save the page as search_results.asp, search_results.aspx, search_results.cfm, or search_results.php, depending on the server model you're using.
3.	Select the recordset (or DataSet if you're working in ASP.NET) option from the Bindings panel's Add (+) menu. The Recordset/DataSet dialog box appears.
4.	Click the Advanced button. Name the recordset/DataSet rsSearch (or dsSearch if you're working in ASP.NET).
5.	Select the connDorknozzle option from the Connection menu.
6.	You can either manually construct your SQL query by typing the code directly into the SQL text box or you can select the appropriate fields from the Database Items selection box. For now, simply add the following code to the SQL text box: SELECT * FROM EmployeeStore WHERE ItemName = 'Dorknozzle Shirt' Notice that the value we're appending to the WHERE clause of the SQL statement is the text Dorknozzle Shirt. The completely formatted Recordset/DataSet dialog resembles Figure 27.4. Figure 27.4. Create the hardcoded query to extract a single record from the database.
7.	Click the Test button. The results, as shown in Figure 27.5, show the data for the Dorknozzle Shirt. Figure 27.5. The result for the Dorknozzle Shirt is shown in the Test SQL Statement window.

Although the query seems to work perfectly, it's still not dynamic to the user. As things stand now, the Dorknozzle Shirt value always appears as the searched item. The next section introduces you to working with variables (also known as parameters if you're working in ASP.NET or ColdFusion). Variables/parameters allow you to capture the users' input and ultimately make the search much more dynamic.

Working with Variables in ASP and PHP

Up to this point, we've yet to discuss the topic variables. Variables in Dreamweaver's Recordset dialog allow you to capture user information from sessions, form requests, cookies, and application variables. Variables let you work dynamically by passing values from one page to another. If you take the search page as an example, a user submits the input in a form object to the search results page. In the search results page, a piece of functionality must be added to capture that input so that it can be automatically appended to the SQL statement's WHERE clause. In the ASP and PHP server models, that functionality is called a variable.

NOTE

The ASP and PHP server models support variables; the ASP.NET and ColdFusion server models support parameters. Dedicated sections to both ASP.NET and ColdFusion are outlined next.

Variables in Dreamweaver enable you to capture the data being sent from a previous page, store it, and then use it at any time in the current page. Variables contain three properties:

• Name: The physical name of the variable.

• Default Value: A default value to be assigned to the variable so that its value is never empty at run-time.

• Run-time Value: The value to assign the variable.

You can set up a variable to capture the user's input from the search page by following these steps:

1.

With the Recordset dialog still open in Advanced mode, add a variable to the variables list by selecting the Add (+) button. The variable should contain the following properties based on the server technology you are using (ASP or PHP):

Server Code	Name	Default Value	Runtime Value
ASP	Search	abc	Request.QueryString("txtSearch")
PHP	Search	abc	#txtSearch#

NOTE

Notice that we entered the value abc as the default value. Because this is a required value, the text abc guarantees that at least a value is sent across at runtime.

2.

To capture requests made from a form via POST in ASP, you use the Form collection of the Request object or Request.Form followed by the name of the text box name within quotes. To capture requests made from a form via GET, you use the QueryString collection of the Request object or Request.QueryString followed by the name of the parameter name which will be coming across the browser's address bar within quotes. In PHP, it's simply a matter of enclosing the name of the form object with the # symbol.

3.

Modify the SQL statement as follows:

 SELECT * FROM EmployeeStore WHERE ItemName LIKE '%Search%' 

4.

As you can see, you want to use the keyword LIKE followed by the name of the variable you just created (Search). Also, we use the % operator as a way of retrieving all the values beginning and ending with the value we're passing in as a parameter. The result of the completely formatted Recordset dialog should resemble Figure 27.6.

Figure 27.6. Format the SQL statement in the Recordset dialog to accept the Search variable as a parameter.

5.

Click OK. The recordset appears in the Bindings panel.

6.

Add all the dynamic text elements to the page, dragging the fields from the recordset into their respective positions on the page. You might also want to add captions for the field names. In addition, drag the Total Records field into the header so that users are aware of how many records the search produced (total record count isn't available in the PHP server model). Figure 27.7 shows the result of the dynamic text and total field additions.

Figure 27.7. Add the fields from the recordset as dynamic text. Under the ASP server model, you can also add the total number of records the search produced.

Save your work and test the results in the browser. Figure 27.8 shows the search result when I typed the value Dorknozzle Shirt and clicked Submit.

Figure 27.8. Dorknozzle Shirt was searched for and the results came back with a match.

As you can see, the search produced a filtered result (1 record) based on the value I entered in the search text box. The results are made possible because of the variable. The variable captured the results of the request sent by the form's submission. The variable was then appended to the SQL statement, which caused the dynamic search for the object's value.

Working with Parameters in ASP.NET

Similar to the ASP server model, the search functionality can easily be created using ASP.NET. The difference between ASP/PHP and ASP.NET, however, lies in how we store and use the value that's coming across, appended to the URL in the address bar. As you saw in the previous section, variables are the method used for collecting, storing, and then subsequently reusing the value coming across. In the ASP.NET server model, we use parameters instead of variables. Like the variables we used in the ASP and PHP models, parameters are the mechanism used for dynamically passing and using values coming from one page to the other. Parameters in ASP.NET support the following three properties:

• Name: The physical name of the parameter.

• Type: The data type associated with the type of data being stored in the database table.

• Value: The value to assign the variable. This value is dynamically constructed using an intuitive Build dialog.

Now that the DataSet has been created and the SQL statement has been generated, you'll want to dynamically change the value that appears just after the LIKE keyword. Essentially, that value becomes a parameter, meaning that rather than using a variable to store the value being sent across, a parameter is passed to the SQL statement dynamically. To create a new parameter for the SQL statement, follow these steps:

1.	With the DataSet dialog still open in Advanced mode, add a parameter to the parameters list by selecting the Add (+) button. The Add Parameter dialog appears.
2.	Type the name txtSearch in the Name text box.
3.	Select the Char option from the Type menu.
4.	Click the Build button. The Build Value dialog appears.
5.	Make sure that the value txtSearch exists in the Name text box.
6.	Choose the URL Parameter option from the Source menu.
7.	Type the value abc in the Default Value text box. The formatted Build Value dialog resembles Figure 27.9. Figure 27.9. Build the parameter's value using the Build Value text box.
8.	Click OK to close the Build Value dialog. The Value text box in the Edit Parameter dialog is now populated with a conditional expression similar to Figure 27.10. Figure 27.10. The Value text box in the Parameter dialog is populated with a conditional expression.
9.	Click OK to close the Edit Parameter dialog. Notice that the parameter is added to the Parameters list in the DataSet dialog box. Of course, if you need to re-open the Edit Parameter dialog to make changes, simply click the Edit button.
10.	Modify the SQL statement to accept a parameter. In ASP.NET, this is done by appending the LIKE keyword followed by the ? symbol to the WHERE clause as follows: SELECT * FROM EmployeeStore WHERE ItemName LIKE ? The result of the completely formatted DataSet dialog should resemble Figure 27.11. Figure 27.11. Format the SQL statement in the DataSet dialog to accept the parameter.
11.	Click OK. The DataSet appears in the Bindings panel.
12.	Assuming search_results.aspx is already open, go ahead and add all the dynamic text elements to the page, dragging the fields from the DataSet into their respective positions on the page. You might also want to add captions for the field names. Figure 27.12 shows the result of the dynamic text additions. Figure 27.12. Add the fields from the DataSet as dynamic text.

Save your work and test the results in the browser. Figure 27.13 shows the search result when I type the value Dorknozzle Shirt and click Submit.

Figure 27.13. Dorknozzle Shirt was searched for and the results came back with a match.

As you can see, the search produced a filtered result (1 record) based on the value I entered in the search text box. The results are made possible because of the parameter. The parameter captured the results of the request when the form was submitted. The parameter was then appended to the SQL statement, and the exact result based on the filtered criteria was returned.

Working with Parameters in ColdFusion

Similar to the ASP, ASP.NET, and PHP server models, search functionality for our Dorknozzle site can easily be created using the ColdFusion server model. The difference between ColdFusion and the other server models, however, lies in how we store and use the value that's coming across, appended to the URL in the address bar. As you saw in the previous two sections, variables and parameters were used for collecting, storing, and then subsequently reusing the value coming across. In the ColdFusion server model, however, variables and parameters are not necessary. Instead we can dynamically append to the WHERE clause of the SQL statement the value of the form name as a parameter, enclosed with # symbols. To demonstrate this, let's build the search functionality in ColdFusion by following these steps:

1.	With the Recordset dialog box still open in Advanced mode, modify the SQL statement to accept the form object's name enclosed with # symbols in the WHERE clause as follows: SELECT * FROM EmployeeStore WHERE ItemName LIKE '#txtSearch#' In this case, the txtSearch form name is appended to the WHERE clause as a parameter. ColdFusion is smart enough to check either the HTTP headers or the address bar for a form object that matches the parameter called txtSearch.
2.	Click OK. The recordset appears in the Bindings panel.
3.	Add all the dynamic text elements to the page, dragging the fields from the recordset into their respective positions on the page. You might also want to add captions for the field names. Figure 27.14 shows the result of the dynamic text additions. Figure 27.14. Add the fields from the recordset as dynamic text.

Save your work and test the results in the browser. Figure 27.15 shows the search result when I type the value Dorknozzle Shirt and click Submit.

Figure 27.15. The Dorknozzle Shirt was searched for and the results came back with a match.

As you can see, the search produced a filtered result (1 record) based on the value I entered in the search text box.

Repeating Regions

Now that your application has search capabilities, you might want to allow the user to see more than one item returned from the search at a time. For now, our search works fine as is because we're performing a search (Dorknozzle Shirt) that returns only one result from the EmployeeStore table. But what if the user performed a search on just the word Dorknozzle? In this case, our search would return multiple results because nearly every item in the EmployeeStore table begins with the word Dorknozzle. But would it really return multiple results? Let's try it! Type the word Dorknozzle in the search page and click Search. As you'll notice, the results page returns just the first item in our EmployeeStore table.

The problem with our approach doesn't lie in the functionality we've added, rather it lies in the functionality we haven't added. Our recordset/DataSet is returning multiple results, the problem is that we haven't added functionality for repeating all the values contained in the recordset/DataSet within the search results page. To fix this problem, we need only add a repeating region. (Repeating regions were discussed at length in Chapter 25, "Working with Dynamic Data.") To add a repeating region to your results page, follow these steps:

1.	Add a line break after the product cost field on the results page and highlight the section of text, including all the dynamic data.
2.	Select the Repeat Region server behavior by clicking the Add (+) button within the Server Behaviors panel. The Repeat Region Server Behavior dialog appears.
3.	Select the rsSearch/dsSearch option from the Recordset/Dataset menu and select the Show All Records radio button.
4.	Click OK. The Repeat Region invisible element wraps your captions and dynamic text.

Save your work and test the search page in the browser by pressing the F12 key. Now type a value such as shirt in the text box and click Search. Figure 27.16 shows the four records that are displayed in the results page.

Figure 27.16. The Repeat Region server behavior opens the door for your users by allowing them to search for data in the database that might have more than one result.

Displaying Alternative Text

Currently, as users search for data in the database, they are presented with results in a structured format. But suppose that users enter a value to search by and the recordset/DataSet cannot return a match? Unless you're using ASP (which allows you to display a record count), the page returns as blank. You can see how counterintuitive this would be to the user, not to mention that it isn't very user friendly.

Using the Show Region server behavior set, you can present users with an alternative text message, alerting them of the failed search result and allowing them to link back to the search page to try their search again. To insert the alternative text into your search result page, follow these steps:

1.	Open search_results.asp, search_results.aspx, search_results.cfm, or search_results.php (depending the server model you're using) and place your cursor just after the last dynamic element that exists on the page. Add a line break.
2.	Insert the text Sorry, no results match that search and create a link for the text TRy Again. Make the Try Again text link back to the search.asp, search.aspx, search.cfm, or search.php page, depending on the server model you're using.
3.	Highlight the Sorry text and the Try Again link and select the Show Region If Recordset Is Empty (the option is called Show If DataSet Is Empty in ASP.NET) server behavior from the Show Region submenu in the Server Behaviors panel. The Show Region If Recordset Is Empty (Show If DataSet Is Empty in ASP.NET) dialog appears.
4.	Choose the rsSearch/dsSearch option from the Recordset/DataSet menu and click OK. The Show If invisible element wraps the text and the link.

Save your work and test the result in the browser by pressing F12. In the search page, type something you know the search will not produce a result for, such as Dorknozzle Paperclip, and click Search. Your Sorry message will appear along with the Try Again link that allows you to link back to the search page, as shown in Figure 27.17.

Figure 27.17. If the recordset/DataSet comes up empty, you are presented with the custom message along with a link that allows you to link back to the search page.

Globalizing the Search Functionality

Now that most of the search functionality has been added to the Dorknozzle site, you're ready to globalize it. What exactly does globalizing mean? Globalization is the term given to items or functionality that can be accessed from anywhere at any time. Currently, the search functionality can be accessed only from the search.asp, search.aspx, search.cfm, or search.php page. What you want is for that search functionality to be accessible from every page; that way, when a user has an immediate need to search for a particular item, it can be done no matter what page the user is on. Accomplishing this task is as simple as emulating the form objects you inserted on the search page in the main template located in the Templates folder of the defined site. Modifying the template provides access to the search functionality on every page in the Dorknozzle site. To globalize the search functionality, follow these steps:

1.	Open the template file that currently resides in the Templates folder for your defined site.
2.	Add two new rows to the navigation table located on the left side of the page. You can do this by placing your cursor in the Admin cell and pressing the Tab key four times.
3.	Highlight the two cells in the second row and merge them. You can do this by selecting the small Merge Cells icon located in the bottom-left corner of the Properties Inspector. CAUTION If you're working in the ASP.NET server model, do not proceed with the following steps. Instead, you should add a new Search link that points to the search.aspx page. The reason for this caveat is simple: ASP.NET supports only one form per page. Adding a new form to the template (which is what we'll do next) results in duplicate forms on pages such as register.aspx, myaccount.aspx, and so on. When you run such pages in the browser, you'll get errors. If you're working in ASP, ColdFusion, or PHP, feel free to proceed.
4.	Insert a new form into the merged row by choosing Insert, Form, Form.
5.	Select the form and make sure that the Action value in the Property Inspector is set to ../search_results.asp, ../search_results.cfm, or ../search_results.php depending on the server model you're using.
6.	Change the name of the form to formSearch and change the Method to GET.
7.	Add a new Text Field form object to the form by selecting Insert, Form, Text Field. Name the text field txtSearch and set the Char Width value to 10.
8.	Insert a new Button form object by choosing Insert, Form, Button. Change the value of the button to Search. The design should resemble Figure 27.18. Figure 27.18. Add a global search component to the template.
9.	Save the page. You should be asked to update all the pages that use the template. Click Yes.
10.	The Update Pages dialog appears, alerting you of the files that Dreamweaver updated.
11.	Click Close.

Now close the template and open any page based on that template. Preview the page in the browser by pressing the F12 key. Figure 27.19 shows that, no matter what page you visit, the search field appears just under the navigation menu.

Figure 27.19. The search form appears on every page.

Try typing the value Shirt and clicking the Search button. The results should appear the same as they did when you initiated the search from the search.asp, search.cfm, or search.php pages.