Using SQL Queries to Return Search Results


For sites that use data stored in a database, one of the most common methods of implementing search capabilities is to simply allow your visitors to query certain fields within your database. For instance, suppose you have a database table named tbInventory that contains the following fields:

  • InventoryID

  • Year

  • Make

  • Model

  • Color

  • Size

  • Condition

  • Description

With this table structure, you could allow your visitors to easily narrow their searches by providing them with a search form on which they could choose from the available years, makes, and models, like the one shown in Figure 20.2.

Figure 20.2. A search form like this enables visitors to easily limit their results.


For instance, suppose a visitor selected "2000" from the year field, selected "Honda" from the Make field, and selected "VT1100" from the Model field. When the Submit button was clicked, the visitor would effectively be submitting a query that says "Show me all the records in the tbInventory table where the Year field is equal to 2000, the Make field is equal to Honda, and the Model field is equal to VT1100." Luckily, we can translate this request into a SQL query that the ColdFusion application understands, and it would look something like this:

SELECT * FROM tbInventory WHERE Year = "2000" AND Make = "Honda" AND Model = "VT1100"


If there were any records that met all these criteria, those would be displayed for the visitor to review (see Figure 20.3).

Figure 20.3. Results from a simple SQL query search.


If no matching data was found, the visitor would be informed and might have the opportunity to refine the search (see Figure 20.4).

Figure 20.4. No data is returned by the query and the user has the opportunity to modify the search.


Creating this type of search application is quick and easy and requires no further configuration of the ColdFusion engine. Instead, it requires an understanding of developing web forms, database connections, recordsets, and results pages that we explore further in the Dreamweaver section of this book. For now, just be aware that through the use of web forms and database connectivity, ColdFusion is capable of allowing your visitors to easily search your database content.

A second type of query is one that searches the results of a search engine index or collection. This type of search is extremely useful when your site contains a large number of pages with static content or a large number of documents.



Special Edition Using Macromedia Studio 8
Special Edition Using Macromedia Studio 8
ISBN: 0789733854
EAN: 2147483647
Year: 2003
Pages: 337

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net