ColdFusion Class Notes for 11/8/1999

 

General Notes About Queries and Loops

 

      The following are properties generated from the <CFQUERY> statement, which can then be used in later code or displayed:

 

                  queryname.recordcount - total number of records returned

                  queryname.currentrow - current row number during

CFOUTPUT loop

                  queryname.columnlist - list of column names in query

                  queryname.executiontime - time in seconds for SQL to run

 

      One can limit the number of records to be displayed or queried in a <CFOUTPUT> or <CFLOOP> statement respectively by setting the following tag options:

 

                  maxrows – the maximum number of rows to be displayed or queried

startrow – the row in the table which will be treated as the starting point for the <CFOUTPUT> or <CFLOOP> statement

endrow – the row in the table which will be set as the ending point for the <CFLOOP> statement

 

Another way to limit number of records processed in a <CFLOOP> statement is to use a <CFBREAK> tag which stops the query when user-defined criteria becomes true.  For example:

           

<CFQUERY datasource=”databasename” name=”queryname”>

SQL statement

</CFQUERY>

 

<CFLOOP query=”queryname”>

<CFIF fieldname {is, gte, lte, gt, lt} criteria>

<CFBREAK>

</CFIF>

                        </CFLOOP>

           

 

Inserting Data Into a Database

 

      Use an INSERT INTO structured query language (SQL) statement within a <CFQUERY> tag.  The basic format is as follows:

 

                  <CFQUERY datasource = “databasename” name = “queryname”>

                              INSERT INTO tablename (fieldname1, fieldname2. . . fieldnameN)

                                          VALUES (value1, value2 . . . valueN)

                  </CFQUERY>

 

NOTE:  the Access query builder uses the alternative format:

INSERT INTO tablename (fieldname1, fieldname2. . . fieldnameN)

                                          SELECT value1, value2 . . . valueN

 

 

Updating Data In a Database

 

      Use an UPDATE structured query language (SQL) statement within a <CFQUERY> tag.  The basic format is as follows:

 

                  <CFQUERY datasource = “databasename” name = “queryname”>

                              UPDATE tablename

SET (fieldname1 = value1, fieldname2 = value2,

valueN. . . fieldnameN)

                              WHERE condition

                  </CFQUERY>

 

Often the condition is to select just one record based on the primary key, e.g.,

WHERE member_id = 3

 

NOTE: If you forget the WHERE part of this statement ALL records will be updated to the same value!!

 

 

Deleting Data From a Database

 

      Use a DELETE structured query language (SQL) statement within a <CFQUERY> tag.  The basic format is as follows:

 

                  <CFQUERY datasource = “databasename” name = “queryname”>

                              DELETE  * FROM tablename WHERE condition

                  </CFQUERY>

 

NOTE: If you forget the WHERE part of this statement ALL records will be deleted!!

 

      One can use the URL in order to determine which record is deleted from a database as well.  For example, if the URL of the page were http://www.mypage/deleterecord?varname=2 the corresponding code would be:

 

 

 

 

                  <CFQUERY datasource = “databasename” name = “queryname”>

                              DELETE  * FROM tablename

WHERE fieldname = #url.varname#

                  </CFQUERY>

 

NOTE:  to pass multiple variables on the URL line use the ampersand to separate the additional variables:

http://www.mypage/deleterecord?varname1=2&var2=ABC

 

Notice that no special characters such as space, quote, period etc are allowed in URL.