CA278 Teaching Notes:  SQL SELECT

 

  1. In order to get the information you want out of your database and on to your Web page, you need to perform two steps.
    1. Retrieve the information from the table – a SQL SELECT statement
    2. Print out the results on your Web page – modified CFOUTPUT, # signs, and a special query name scope for variables.
  2. SELECTing data from your database - enter the SQL SELECT statement.  The SQL SELECT statement allows you to retrieve records, or parts of records, from one or more tables.
    1. In its simplest form, the SELECT statement is comprised of two clauses.  Both of these clauses are required for a valid SELECT statement.

SELECT Field1, Field2, …FieldN

FROM TableName;

    1. There are actually four clauses that will focus on for now.

Clause

Required/Optional

Purpose

Example

SELECT

Required

Indicates the field(s) to be retrieved.

SELECT Name, Email

FROM

Required

Indicates the table(s) to retrieve the fields from

FROM tblEmployee

WHERE

Optional

Indicates a filter criteria

WHERE Email NOT NULL

ORDER BY

Optional

Indicates the sort fields and sort order (ascending is the default and descending is indicated by DESC)

ORDER BY Name;

or

ORDER BY Name DESC;
    1. Example SQL SELECT statement

                                                               i.      Command: SELECT

                                                             ii.      Format: SELECT Field1, Field2, …FieldN

FROM TableName

WHERE Criteria

ORDER BY Sortfield1, Sortfield2,…SortfieldN

                                                            iii.      Example: SELECT FName, LName, Email

FROM tblEmployee

WHERE LName = ‘Doe’

ORDER BY Name;

    1. Some important notes about SQL code:

1.      SQL statements are not case sensitive.  However, it is common practice to put SQL keywords in all capitals, as shown above.

2.      All white space is ignored in a SQL statement.  However, SQL statements are much easier to read when broken up over several lines.  Again, this is not required but it is a common practice among programmers.

3.      You can retrieve data from multiple tables by joining tables, specifying more than one table in the FROM clause, and scoping your field names with the appropriate table name.  For example,

 

SELECT tblEmployee.Name, tblRetire.RetireDate (scoped fields)

FROM tblEmployee, tblRetire (multiple tables)

WHERE tblEmployee.SSNo = tblRetire.SSNo; (table join)

 

4.      Perhaps the most important rule of all…the ORDER BY clause must ALWAYS be the last clause in the SQL statement, otherwise the clause is ignored entirely.

    1. Just as with an INSERT SQL statement we cannot just stick a SQL statement in our CF template and expect it to work.  CF wouldn’t know what to do with it.  So we must “wrap” our SQL statement in a <CFQUERY> tag like the following.

<CFQUERY DATASOURCE=”Employee” NAME=”qGetEmp”>

SELECT Name, Email

FROM tblEmployee

ORDER BY Name;

</CFQUERY>

                                                                     i.      Time Savers!

1.      Naming conventions- Make ‘em and Stick to ‘em!  Notice how I named the query.  q indicates that it is a query and GetEmp indicates that I am retrieving employee information.  Again, you are in no way required to name you queries in this manner, but establishing (and sticking to) naming conventions will save you a lot of time in the long run.  This convention is common among CF programmers.

2.      SELECT all – You can use a * (asterisks) wildcard in your SELECT clause to indicate you want all fields returned from your database.  However, use this sparingly because returning all records takes more processing time.  Here’s an example of the code.

SELECT *

FROM tblEmployee

ORDER BY Name;

    1. Exercise 1: Retrieve Data from Your Database

                                                                   ii.      Start a blank template named GetEmp.cfm and title it Get Employee Information.

                                                                  iii.      Write the code to retrieve all records from the employee database and sort the records by last name.

                                                                 iv.      How can you verify that your code works properly?  Try printing out the values of your newly retrieved variables.  Does it work?

  1. Displaying the Value of Variables Retrieved from a Database
    1. The rules for displaying the values of database variables are similar to the rules for regular variables, i.e.,

                                                               i.      We need <CFOUTPUT> tags to indicate we want to display the value of a variable and 

                                                             ii.      We need # signs to differentiate between variable names and regular text

    1. However, there are a couple of distinct differences.

                                                               i.      Query variables must be scoped with the query name (hint: that’s why it’s good to keep query names short and sweet).

<CFOUTPUT>

            <UL>#qGetEmp.Name#  #qGetEmp.Email#</UL>

</CFOUTPUT>

 

The above code works perfectly…if all you want to do is output the values of the first record.  But, what if you want to output the values of ALL the records retrieved?  Imagine that!  Somehow we need to tell CF to “loop” through the commands within the <CFOUTPUT> tags until all records have been printed.

                                                             ii.      We do this with the following modification to our <CFOUTPUT> tag.

<CFOUTPUT QUERY=”qGetEmp”>

<UL>#qGetEmp.Name#  #qGetEmp.Email#</UL>

</CFOUTPUT>

    1. Exercise 2: Display Records from a Database

                                                               i.      Modify your GetEmp.cfm template to display the FName, LName and Email fields in a bulleted list and sorted by Last Name. Add an <h3> title to the list; Bulleted Email Address List.

                                                             ii.      Just below that code, try to re-display your records again in the following format.  Give this table the title <h3>“Email Address Table.” </h3>

Name

Email

Jane Doe

jdoe@where.com

John Doe

jdoe@here.com

Jeannie Doe

jdoe@there.com

Hmm….Do you have to re-query the database?  Will your table tags go inside the <CFOUTPUT> tags or outside?   How about header tags, inside or outside <CFOUTPUT>?  And <TR> tags?