Constructing a Dynamic Query

I l @ ve RuBoard

Being able to get information from a database is very powerful, but being able to get information from a database based on user input is even more powerful. For example, showing stock quotes is great, but showing only the stock quotes that you're interested in is much better! Searching a database based on criteria you select is accomplished using HTML, ColdFusion, and SQL. The customized search process uses HTML to gather and format information, ColdFusion to connect to the database and output the information returned, and SQL to get the information from the database.

1. Choose File > and select dynamic1.cfm from the Lesson5\Start folder on the CD-ROM.

   An HTML form has already been set up for you. This is the form into which you will enter the information to search the database. It contains one field for the product name , with which you will be searching your database. We're going to open another file, but do not close this file yet.

   

2. Open dynamic2.cfm. After the FROM statement in the SQL, type:

    WHERE ProductName LIKE '%#form.search#%' 

   The initial query that selects all of the information from the product database has already been set up for you. The asterisk (*) in the SQL is the wildcard that selects every record in the database. The SQL keyword WHERE tells the database that you only want to see records that match a certain criterion, the specifics of which will follow. This criterion could use one of several keywords, such as "is." In this instance, you're using the LIKE keyword. The LIKE keyword will try to match any part of any product name with the phrase that was entered in the search field. For example, if you had a phone clip, a belt clip, and a small travelling clip as items in your product database, they would all come up as matches if you were to enter the word clip into the search field. For this type of search, a flexible keyword such as LIKE is a good bet, since people don't always know exactly what they're searching for, or its specific name, and may not be able to match the entire entry.

   Notice that the variable is enclosed in single quotes. In SQL, text strings must be enclosed in single quotes.Whether or not quotes are necessary for other variables is determined by the requirements of the database itself. When a database is set up, you can determine the type of information that will go in certain fields. You can specify a date/time value if you want only a date/time variable to be stored in that field. You can also specify yes/no fields, number fields, text fields, and memo fields. In Microsoft Access, single quotes must be around the value in a text field, regardless of the value. If you had the number 2 in a text field, it would actually need to be '2' in your query. However, if the number were in an integer field, it would not need quotes.

3. After the WHERE clause, add ORDER BY ProductName .

   Your code should now read:

    <cfquery name="productsearch" datasource="products">  SELECT * FROM tblProducts WHERE ProductName LIKE '%#form.search#%' ORDER BY ProductName </cfquery> 

   By adding the ORDER BY clause to your SQL, the records will be returned to you in alphabetical order by product name. You can order your results by any of the fields in the database (product name, product price, and so on). You can also reverse the order (from the default, ASC, which stands for ascending ) by specifying DESC, for descending, after your ORDER BY statement. Then the records would start at Z and end with A.

   You now have a recordset to output, based on the information you supplied in the form.

4. In the output area of the dynamic2 template, add the following code:

    <font size="2">Product Information:</font><br>  <cfoutput query="productsearch"> <strong>#ProductName#<strong><br> #ProductDesc#<br> Price: #DollarFormat(ProductPrice)# <br><br> </cfoutput> 

   

   This is the same way the information was formatted in your initial Firstquery template. The difference this time is that rather than just outputting all the records in the database, you are now searching and outputting records based on the criteria entered into the search form.

5. Save both dynamic1.cfm and dynamic2.cfm in the C:\Inetpub\ wwwroot \Lesson5\folder.

   NOTE

   These files should be saved in the same folder because the dynamic1.cfm file looks to the dynamic2.cfm file to process user requests . The action attribute (covered in Lesson 4) specifies the location of the dynamic2.cfm template. You could store it in another folder, but you would have to make sure the action attribute points to the correct folder.

6. Open a Web browser and enter the following URL into the address window: http://127.0.0.1/Lesson5/dynamic1.cfm .

   You should see the form that was already constructed for you.

7. In the Search field, type the product name clip and click the Search button.

   

   Once you have clicked Search, you should see a screen similar to your original query, but this time with only one product. The phone clip appears, matching the search term "clip" that was entered in the search field.

   

   You now have a form that allows you to search your database for products that match the description you entered in the form. The concept is the same for creating catalog searches or for any other type of search against a database: get a search criterion from the user, run a query on a specific field to see if it matches what the user entered, and, if it does, return the matching record(s).

   Do not close the dynamic2.cfm file yet, as you are going to use it in the next task.

I l @ ve RuBoard