In-addition to the fieldnames that you specify in your SELECTstatement, Coldfusion returns several other query-related variables. They can be used in the same manner as all other returned query variables. The CFQUERYtag standard variables are:
• recordcourrt-provides the total number of records returned in a query result set. The number may be misleading if you are grouping your output.
• coloumlist-Provides a comma-delimited list of all field names returned in a’ query result set. You can use List functiona.on this variable.
• currentrow-Stores the value of the current row number ~ the returned recordset.
• CFQUERYE.xecuti onT; me-Displays the query processing time in milliseconds. (Do not preface this variable with the query name.)
Another way that you can output query varialUes is by prefixing the variable with the quet.ynameas in query-name. vart able_name. This would be done inside of a CFOUTPUT tag that does not have the QUERYattribute set. In order to output the recordcount or currentrow variables without having to worry &bout CFOUTPUTQUERYattributes, follow the example in Listing 10.8. The code in Listing 1’0.8 also demonstrates a way to account for zero results in your query recordset.
Displaying CFQUERY Variables’
Adding Records to the Database
Now that you have an understanding of how to retrieve and display data from a database, you may find it useful to modify data in a database. This section will discuss how to add records to tables in a database.
Using the Cosby Speakers Bureau example, you will learn how to add new speakers to the speakers table. Most of the time, records are added to a database based upon submitted form data. Submitting form data to a database will be covered in Chapter 15, “Forms.” In this chapter, you will submit hardcoded datato the database tables. The . final section of this chapter discusses more about submitting dynamic data. In order to add records to a di’tabase, you must use the SQL INSERTstatement inside . of a CFQUERYtag. In the INSERTstatement, you must tell ColdFusion which table and fields to add data to.
INSERT INTO ‘
The fjist part of the INSERTstatement is where you specify the table and fields to which you,want to add data. This is a required part of the INSERTstatement. You begin the INSERT.stctement with the INSERT INtO keywords followed by the name of the table and then the fields that you are adding data to. The format is INSERT INTO tab naine· (field 1, field2, field 3). Table fields do not need to be included in the INS~RTfield . list unless the field requirements specify that it must not be NULL (meaning it must not be empty, it must contain a value). Then it must be presentin your field list. This .includes primary keys. AutoNumbered ‘fields should not be included in the field list, as the database will automatically generate’ a unique value .
The second part of the INSERTstatement is where you define the values of the fields that you would like to insert. You begin this clause with theVALijES.l<eywordfollowed by the values you are inserting. The order and number of values h~ve to exactly match the order . and number of fields listed in the first section of your INSERTstatement, The format is . INSERT INTO table_name· (fieldl, field2, field!)·VALUES (integer_valtte, ‘text_value’, ‘memo_value’). If the value you are inserting is a text value, then it should be surrounded in single quotes (never use double quotes). If it is an integer, then do not put any quotes around it. The value data type must match the field data typespedtied in the database.
When an INSERT query is run, there are no results available for out putin the template. You will know the query was unsuccessful if a Coldfusion error is returned. If you want . to catch errors so they are not displayed to the user, please refer to the discussion on CFTY and CFCATCHtags in Chapter 18, “Implementing Error ControL”. An example of an INSERT query is shown in the next section under.
Retrieving the Last Inserted Record
When a record is inserted into a database, it is extremely wise to also add a field, known as a primary key. The primary key is used to give the record a unique value, This makes it easier to differentiate between records and prevent duplication. The primary key can be automatically generated (depending on your database) or ” you, the developer, can specify it. If you define the primary’ key, then you must add it to your field list in the INSERT statement. Its value must be unique and not match any other primary key value in the table. While this is not as convenient as having the database’ ‘. generate the number, it does make it easier to retrieve the newly inserted record from the database. Because.you know the primary key value, you will easily qe able to use; SELECTstatement to fino and return the record,
On the other hand, if the databasegenerates the primary key value, you. have no way of knowing what that is. There are several methods for finding out, You can query tabase and select the MAX() of the primary,key field. This aggregate function will give you the maximum.value ~f the primary, key field. This requires that the primary key field be numeric. There is a slight possibility, though, that User B could have inserted a record immediately following your insert and previous to your query for the maximum 10. This would result in you retrieving UserB’s record. To prevent this, you can surround your two queries with a CFTRANSACUONtag (see Listing 10.9 and Figure 10.9). If your database accepts it, yo.u should set the isolation level to the value of serial is zable. Otherwise, the default isolation level is acceptable in most cases. Consult your database documentation for further details.
Retrieving the last inserted record ID
Stored Procedures can be created to return the primary key value of the last inserted record as long as they are supported by your database. Defusion has several very good articles on how to do this. You can visit Defusion at www.defusion.com. You can also retrieve the last inserted record from an Oracle or SQLServer 7.0 database by creating a trigger for all INSERT queries. This trigger would be coded as follows:
SELECT OO10ENT1TY FROM table_name
Please refer to your database documentation for more detailed information on how to do this.