Querying a Query

I l @ ve RuBoard

Another way to help reduce load on a database and increase performance is to query an already existing recordset. This saves a trip to the database because the specific information from the original query is already in memory. Imagine, for example, being able to query your product catalog once, save the results to memory, and then let users perform searches on the query results rather than the database. Here, you are going to create a simple example of this feature in action.

NOTE

Typically, you would store your original query to an application variable to keep it available. However, since we are not going to cover application variables in this book, you will see the querying of queries in action but not actually save your initial query to memory.


You should still have dynamic2.cfm open from the previous task.

  1. Below your original query, add the following <cfquery name ="queryquery" dbtype ="query"> .

    Your code should now read:

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

    Once again, you begin with the <cfquery> tag, and then specify a name for your query. The big difference here is that you are not specifying the name of a data source, but rather stating in the dbtype attribute that you are going to query a query.

  2. Below the <cfquery> statement, type SELECT *

    Your code should now read:

     FROM productsearch  <cfquery name="queryquery" dbtype="query"> SELECT * FROM productsearch 

    In the previous task, you specified a table from which to select data, using FROM. In this task, you are specifying a queryproductsearchfrom which you want to retrieve data. You will end up with the same information in the second query that you had in the first. But here you are not making another call to the database to retrieve the information. You may have noticed in the second query that you are selecting the wildcard (*), or all the records of the first query. You should also take note that the SQL is different for the second querynow you don't have to specify WHERE or LIKE clauses to get the same information.

  3. Type </cfquery>.

     <cfquery name="queryquery" dbtype="query">  SELECT * FROM productsearch </cfquery> 

    The </cfquery> tag closes the statement.

    You now have another recordset containing information from the previous query. This information is identical to the information in your previous query because you selected all the information from that query, but you didn't have to use the WHERE clause to get the results you wanted.

  4. Change your <cfoutput> tag to the following:

     <cfoutput query="queryquery"> 

    By changing the query name in your <cfoutput> tag you are now referencing your second query. If you view the results in a browser they will be identical to the results of your initial query.

  5. Save the dynamic2.cfm file again, in the C:\Inetpub\ wwwroot \Lesson5 folder.

    This will overwrite your original dynamic2.cfm. This is OK.

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

    This opens the same search window as you saw before.

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

    Once again, you are performing a search on the product name "clip." You should see the exact same results as before, the only difference being that your results are being displayed from the second query.

I l @ ve RuBoard


Macromedia ColdFusion 5. Training from the Source
Macromedia ColdFusion 5 Training from the Source (With CD-ROM)
ISBN: 0201758474
EAN: 2147483647
Year: 2002
Pages: 99
Authors: Kevin Schmidt

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