Home arrow ColdFusion arrow Page 4 - Databases and Dreamweaver MX 2004, concluded

Databases and Dreamweaver MX 2004, concluded

This article, the second of two parts, looks at the different database applications available for use with ColdFusion. It 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; ISBN: 1590592379).

Author Info:
By: Apress Publishing
Rating: 5 stars5 stars5 stars5 stars5 stars / 10
January 12, 2006
  1. · Databases and Dreamweaver MX 2004, concluded
  2. · Using Dreamweaver MX 2004 to Create Queries
  3. · Using Dreamweaver MX 2004’s Advanced Recordset Interface
  4. · Writing a SELECT Query in Advanced Mode
  5. · Filtering by Multiple Parameters

print this article

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:

    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.

blog comments powered by Disqus

- Adobe ColdFusion Just Got More RAD
- How to Access a SQL Anywhere Database with C...
- CFXML: Probing XMLDOM in ColdFusion
- Creating a Web Service with ColdFusion: the ...
- CFAjax: What it is and How to Use it
- Querying SQL 2000 Server from ColdFusion
- Introduction to ColdFusion Markup Language, ...
- Introduction to ColdFusion Markup Language
- Databases and Dreamweaver MX 2004, concluded
- Databases and Dreamweaver MX 2004
- Welcome to Coldfusion MX 6.1, concluded
- Welcome to Coldfusion MX 6.1
- What You Must Know About ColdFusion Flow-Con...
- What You Must Know About Operators in ColdFu...
- Everything You Must Know About ColdFusion Va...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 

Developer Shed Affiliates


© 2003-2019 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials