Page 3 — SQL Statements

SQL is a database query language that is relatively standard among most database applications, although Microsoft in its own inimitable way has added proprietary statements (see your Help file if you’re using MS Access). In short, it’s a way to select certain records from database tables, using criteria you choose. For example, let’s say that you wanted to select records from the Contents table in our hypothetical database contents_of_my_pocket.mdb. For our purposes, we’ll assume that you want to select only items that were acquired on 12 December 1998. Your template’s SQL statement would look something like this:

<CFQUERY NAME= "pocket" DATASOURCE= "contents_of_my_pocket">
SELECT * FROM contents WHERE acquired IS "12 December 1998"
</CFQUERY>

This tells ColdFusion to select all the records (*) from the table Contents where the field Acquired exactly matches the text “12 December 1998.” If you were to use this as your top statement in the template we outlined in the last section, you’d get a list of only three items.

In general, SQL uses the term IS to match text fields and “=” to match numbers. Let’s say you want to be less selective and see all of the items acquired in the month of December:

<CFQUERY NAME= "pocket" DATASOURCE= "contents_of_my_pocket">
SELECT * FROM contents WHERE acquired LIKE "December%"
</CFQUERY>

The LIKE term tells ColdFusion to look for records where the Acquired field starts with the text “December” and is followed by any old day and date. The percent sign is SQL’s wildcard character; it means that we don’t care what follows the word December. LIKE is almost always used in conjunction with a wildcard character. Similarly, you could select all items acquired in 1998 by using the following:

<CFQUERY NAME= "pocket" DATASOURCE= "contents_of_my_pocket">
SELECT * FROM contents WHERE acquired LIKE "%1998"
</CFQUERY>

Or if you want to select only items that have the letter “b” in them, you could use a statement like this one:

<CFQUERY NAME= "pocket" DATASOURCE= "contents_of_my_pocket">
SELECT * FROM contents WHERE item LIKE "%b%"
</CFQUERY>

But I don’t know why you’d do that. Note that we changed the field name after WHERE from Acquire to Item.

The examples above will cover most basic SQL statements. If you find that you need more control in selecting records, try the tutorial listed above, because I’m really pressed for space here and have a lot of ground to cover. Now on to a more detailed look at the <CFOUTPUT> tag.