Databases and Dreamweaver MX 2004, concluded - Writing a SELECT Query in Advanced Mode

(Page 4 of 5 )

SELECT queries are the most common type you will encounter in CFML applications. While developing your applications, often you will need to create more complex queries than is possible in the Basic Mode, so a good knowledge of SQL is essential to building a robust application. By using Advanced Mode you can write custom queries to handle your specific needs.

1. Clear the SQL code from the SQL text field. Now highlight the Departmt table in the Database Items tree and click the SELECT button to the right of the tree. You should now see the following code added into the SQL text field:
   
   SELECT*
   FROM Departmt
   
   You can see that instead of selecting Dept_ID and Dept_Name, it has entered an asterisk (*). As mentioned before, this is SQL shorthand for “all columns,” so your SQL query will select all columns from the Departmt table. 
   
2. If you want to select only a few columns, expand the Departmt table, click on a specific column, and click the SELECT button. You can select only one column at a time through this interface. Let’s select both the Dept_ID and Dept_Name. Highlight Dept_ID and click SELECT, then highlight Dept_Name and click SELECT again. Click on Test to view the output. As you can see from Figure 2-15, it is slightly different from the previous example.
   
   Notice that it has attached the table name before each of the column names. While this isn’t necessary when you are selecting values from a single table, it will be important later on when you start joining data from separate tables.
   
   Removing the Departmt from the columns in theSELECTlist will not affect the query results if you are only dealing with a single table.
   Figure 2-15.  The table name is attached to the 
   column name. 
   
3. To filter the records according to location, highlight the Location column from the Database Items tree and clickWHERE. Dreamweaver addsWHERE Departmt.Locationto the end of the previous query, but you must finish the rest of the statement. To select only departments in Cambridge, you must modify theWHERE clause to say:
   
   WHERE Departmt.Location = 'Cambridge'
   
   TIP   You could use “Cambridge” or “cambridge” here, because most databases aren’t case-sensitive by default. Both spellings would return the same query results. Check your specific database configuration before making this assumption in the real world. 
   
4. Click the Test button. Notice that you now have only three results, as shown in Figure 2-16. The fourth record is hidden because its location is San Francisco, and so it did not meet the requirements of theWHEREclause.
   
   Figure 2-16.  Records filtered according to the 
   location Cambridge

Ordering the Query

Ordering the results is quite straightforward. You simply highlight the column you want to sort the results by and click ORDER BY. If you click the Dept_Name column, you will see the following code in the SQL text field:

SELECT Departmt.Dept_ID, Departmt.Dept_Name FROM Departmt
WHERE Departmt.Location = 'Cambridge'
ORDER BY Departmt.Dept_Name

We have managed to recreate the functionality of the original query we wrote in simple mode. Clear the SQL text field. You will now generate some queries with more complexity using the Employee table.

Next: Filtering by Multiple Parameters >>
 

More ColdFusion Articles
More By Apress Publishing

Comments On: Databases and Dreamweaver MX 2004, concluded

· This article is an excerpt from the book "ColdFusion Web Development with...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter two of the book ColdFusion Web Development with Dreamweaver MX 2004, written by Jen and Peter de Haan et al. (Apress, 2004; ISBN: 1590592379). Check it out today at your favorite bookstore. Buy this book now.