Searching For Products
In addition to allowing our users to find products by category, we want to give them the option of searching for a product by its name, description, or price range. Instead of making a separate page, let's go ahead and put it right on bazaar.asp for now, as in Listing 7.7.
Code Listing 7.7. Preparing to add the search form
The first thing that we've done is set up the form to make sure the format is correct. To set the categories and the form side-by-side, we've put them into a one-row table (see Figure 7.3). If we'd just left it at that, what'd have a very narrow table with everything squished together. So instead, we've set the width on the table to 75 percent of the page, and the width of the category cell to 30 percent of the table. That should space things out nicely.
Figure 7.3. Using an HTML table, we can place our elements side-by-side, but external formatting doesn't take effect within the table itself.
Remember how we centered the categories when we first created this page? We haven't gotten rid of the <CENTER></CENTER> tags, but you'll notice that the categories are no longer centered. Instead, the entire table is centered, and the categories are aligned to the left. This is because each cell is independent as far as formatting goes. We'd need to re-apply the center tags within the cell for us to see the change.
Aside from the formatting, there is one important programming issue to take notice of. We're opening catSet on line 15, before the table, but we're not closing it until line 38, after the table. We could have opened it, displayed the information, and closed it all inside the cell, but we're going to use that recordset again for the search formed, so why destroy it?
Now let's create the form we're going to use (see Listing 7.8).
Code Listing 7.8. Creating the search form
First we're going ahead and opening the form on line 33. Because bazaar_action.asp doesn't really describe what the action does, we're going to call the it baz_search_action.asp. We want to give the customer a chance to not only search by several different qualities, but also to pick and choose which should apply. We're going to do that with check boxes on lines 35, 38, and 47 (see Figure 7.4). If the check box is selected, we search on that criteria. If not, we don't.
Figure 7.4. The Search form.
So we create a check box and a text box for the text, and a check box and a pull-down menu for the price levels. Finally, we want a check box and a pull-down menu for the categories. This is why we didn't close the recordset after we used it the first time. Before we get to line 50, catSet is at the end of file, so we bring it back to the beginning using MoveFirst. We can then loop through it as usual, without the overhead of re-creating it. What we're doing when we loop through the categories on lines 51 through 57 is creating the options for our pull-down menu, specifying the cat_id as our value.
So now that we have our form, we can create the action to service it. Copy template.asp to baz_search_action.asp in the bazaar directory and add the new code in Listing 7.9.
Code Listing 7.9. Preparing to process the search form
On lines 4 throught 9 we're retrieving our form values, as usual, but before we even think about hitting the database, let's talk strategy. So far every time we've queried the database with a select statement, we've asked for information from just one table. In some cases, this has meant that we then had to go to a second table or a second recordset for more information, and we could do that this time as well.
What we're doing here, however, is called dynamic SQL. That means that until we get to the page, we don't know what we're looking for. So in this case, we're going to join the product and the product_categories tables together into a single batch of information that we can query all at once.
Let's dissect the SQL statement on lines 14 through 19. The first thing that we need to do is specify what information it is that we want. All we're going to do with the information is generate a list of links to product pages, so we need the prod_id (for the URL) and the prod_name (for the link). You may notice the products. in front of the names. That's to tell the database which table we're referring to, because there's a prod_id column in both of the tables we're selecting from. Because there's only one product_name column, we could have gotten away without specifying the table name there, but it's good practice to always include it.
Next, on lines 16 and 17, we're telling the database what tables we want to select from, in this case baz_products and baz_product_categories. Now, if we wanted to, we could leave it here, but that would be a bad thing. The reason is that this SQL statement, as it stands, would join every line in the baz_products table with every line in the baz_product_categories table. Our tables only have 7 and 4 lines, respectively, which would mean only 28 lines in the resulting set, but imagine if you have thousands of products and hundreds of categories. This condition is called a Cartesian Join, and you can bring an entire machine down with it, if you're not careful.
The way that we get around this is to add a join condition linking the appropriate rows together, on lines 18 and 19. This way, the only lines we get back for each product are those that list the categories to which it actually belongs.
We've still got some work to do on this SQL statement. After all, if we run it right now, it will return us all of our products, no matter what our search asked for! Let's do it, though, just to test things out and make sure they're working, as in Listing 7.10.
Code Listing 7.10. Joining two tables
Note
If this were virtually any other database, we could clean this up with table aliases, making the SQL statement:
select distinct(p.prod_id), p.prod_name from baz_products p, baz_product_categories c where p.prod_id = c.prod_id
Unfortunately, Access doesn't support table (or for that matter column) aliases, so we're stuck with the long ones. Feel free to use what works in your database, however.
Before we go about displaying any products, we need to make sure that we're going to get at most one listing for each product. Remember, with a join, if there are three categories that a product belongs to, it's going to show up in three rows. We eliminate that problem by telling the database that we want distinct product IDs, as noted on line 14. Then we create a simple recordset and display our product links.
Now we're ready to start narrowing things down based on our search criteria. Listing 7.11 shows how we create the dynamic where clause for the search.
Code Listing 7.11. Creating a dynamic SQL statement
What we're doing here is conditionally building an SQL statement. On lines 34 through 39, we've built the complete set of records. Now we want to narrow it down using the criteria from the form, which we are adding on line 40.
Let's look at how we're building those Where clauses. On lines 11 through 13, we're initializing the statements. If we don't have anything to add to them, they'll remain blank and won't affect the results.
On line 15, we decide whether to worry about the text search. If the user didn't click the check box, it doesn't matter what they entered in the text box; we're not going to search on it anyway. If they did, however, we need to compare it against two things: the product name and the product description. Let's take a look at what we actually receive. If I were to do a search and ask it to look for Rocket, line 18 would print out
p_textWhere = and (prod_name = 'Rocket' or prod_desc = 'Rocket')
This means that this part of the Where clause would be true if the product name or the product description was Rocket. If this were the only item I'd checked off, the complete Where clause would be
… where baz_products.prod_id = baz_product_categories.prod_id and (prod_name = 'Rocket' or prod_desc = 'Rocket')
Let's look at what this means. For every record that is returned by our two tables, the database is going to ignore every record that doesn't fit the following:
Condition 1 AND Condition 2 Condition 1 is just baz_products.prod_id = baz_product_categories.prod_id Condition 2 is (prod_name = 'Rocket' or prod_desc = 'Rocket')
So a record has to satisfy our Join clause, and it has to have Rocket in the name or the description. But what if we left out the parenthesis? In that case, we'd have
| Condition 1 | And | Condition 2 | Or | Condition 3 |
|---|---|---|---|---|
| baz_products. | prod_name = ' | prod_desc = 'Rocket' | ||
| prod_id = baz_ | Rocket' | |||
| product_ | ||||
| categories. | ||||
| prod_id |
In this case, a record would be included if the description was Rocket, even if it didn't satisfy any of the other conditions—like the join condition.
Fortunately for us, our other situations are a bit more straightforward. On line 21, we're going to decide whether or not to search on the price. Not only do we need for users to have clicked the check box, we need for them to have chosen a price level. After all, if they don't care what the price is, there's no point searching on it, now is there! We also saved ourselves a bit of time when we chose the values for our pull-down menu. We could have set the values as Level 1, Level 2, and so on, but then we'd have to translate that into actual prices here. Instead we set it as the actual price we need to come in under, so we can put it right into the SQL statement.
Finally, we set the category, if the user picked it. Just to make sure everything's working the way that we expect, we are also printing out our Where clauses and the final SQL statement. This way we can see what's actually making it to the form, and if we get strange results, we can see exactly where they're coming from (see Figure 7.5).
Figure 7.5. By printing our Where clauses and the final SQL statement to the page, we can see why products are being selected or excluded.
We still have one problem. The chances are that the user is not going to enter the exact name of a product, let alone the description. We need to take that into account, and put in wildcards, as in Listing 7.12.
Code Listing 7.12. Adding wildcards to the search
With these changes, we're accounting for both wild cards and case-sensitivity. On line 6 we're converting the string that our customer is searching on to all uppercase letters. Then we also convert the text in the database to all uppercase letters, so no matter what the user enters the case will match. To take care of the wildcards, we're using the like operator on line 18 instead of the equal sign, and adding percent signs, which act as wildcards.
Note
The best way to accomplish case insensitivity for your search is going to depend on what database you're using. For instance, if we were using Oracle or another more advanced database, we could have just said
p_textWhere = " and (upper(prod_name) like upper('%" &p_text& "%')"_&" or upper(prod_desc) like upper('%" & p_text & "%'))"
but Access won't allow you to use conversion functions with the like operator.
If we remove our debugging statements, as we do in Listing 7.13, we have a fully functional (although fairly basic) search engine for our catalog.
Code Listing 7.13. Completing the search engine
Now that we have this thing, it's a shame that we can only get to it from bazaar.asp. It would be very handy for our customers if they could do a search from anywhere in the catalog. Fortunately for us, anywhere in the catalog consists of basically three ASP pages: bazaar.asp, baz_category_products.asp, and baz_product.asp. From a maintainability standpoint, it'd be a nightmare to have the form on all three pages, though. Every time we made a change, we'd have to make sure it was made on all pages. Or would we? We could have it on all three pages, but in only one file, if we used Server Side Includes. The include file needs just the actual form itself (see Listing 7.14).
Code Listing 7.14. Creating a file that can be included on other pages
1: <H3>Search our catalog</H3> 2: <FORM ACTION="baz_search_action.asp" METHOD="post"> 3: 4: <INPUT TYPE="checkbox" NAME="p_usetext" VALUE="yes"> 5: Look for: 6: <INPUT TYPE="text" NAME="p_text"> 7: <BR> 8: <INPUT TYPE="checkbox" NAME="p_useprice" VALUE="yes"> 9: Price Range: 10: <SELECT NAME="p_price"> 11: <OPTION VALUE="all">All Price Levels 12: <OPTION VALUE="100">Under $100 13: <OPTION VALUE="1000"> Under $1000 14: <OPTION VALUE="10000"> Under $10,000 15: <OPTION VALUE="100000"> Under $100,000 16: </SELECT> 17: <BR> 18: <INPUT TYPE="checkbox" NAME="p_usecat" VALUE="yes"> 19: In Category: 20: <SELECT NAME="p_cat"> 21: <% 22: set catSet = outpostDB.Execute("select * from " & _ 23: baz_categories") 24: while not catSet.EOF 25: %> 26: <OPTION VALUE="<%= catSet("cat_id") %>"><% 27: Response.Write catSet("cat_name")%> 28: <% 29: catSet.MoveNext 30: wend 31: catSet.Close 32: set catSet = Nothing 33: %> 34: </SELECT> 35: <P> 36: <INPUT TYPE="submit"> 37: </FORM> |
We don't need any of the declarations or page headers and footers because we're going to be including this file in pages that already have them, but because we can't count on having the category recordset, we need to create and destroy it within this section, on lines 22, 23, 31 and 32. Copy this from bazaar.asp into the file baz_search.inc and add the recordset information. We've removed catSet.MoveFirst because it is no longer necessary, but it wouldn't have hurt anything to leave it there.
Now we can include this form anywhere we want. Add the include file to bazaar.asp as shown in Listing 7.15.
Code Listing 7.15. Adding the include file
Because we are going to have to create and destroy the category recordset within the include, we'll go ahead and destroy the first instance of it when we're done with it on lines 29 and 30. Yes, we'll be hitting the database one more time than we have to, but we're trading off for flexibility. We'll add the search include file to our other pages as we go along.