CPCUG Monitor
Creating dynamic websites with ColdFusion
Part 6: Handling ID fields
by Michael Smith
What is ColdFusion?
In this article we continue to look at what ColdFusion is and
how you can use it for dynamic website creation. We cover ID field creation,
using include files to save coding time, and how to write a deletion
confirmation page to prevent accidents!
In case you missed previous articles that introduced
ColdFusion, let me explain what it is. ColdFusion is a programming language
based on standard HTML (Hyper Text Markup Language) that is used to write
dynamic web pages. It lets you create pages on the fly that differ depending on
user input, database lookups, time of day or what ever other criteria you dream
up! ColdFusion pages consist of standard HTML tags such as <FONT
SIZE="+2"> together with CFML
(ColdFusion Markup Language) tags such as <CFSEARCH>, <CFIF>
and <CFLOOP>.
ColdFusion was introduced by Allaire in 1996 and is currently on version 4.
Why ID
Usually
database tables contain an ID field that is used to refer to each record
uniquely. The Ids are of four main types of ID:
1.
Autonumber
- sequential numbers 1,2,3 etc
2.
Timestamp
- unique date/time of record creation.
3.
Unique
physical key - e.g. Social Security Number
4.
Random
number
The
problem we have is how to know what the ID value is for a new record. The SQL
INSERT command does not return this information to us. For the physical key and
random number methods, we can determine the ID before running the insert so
this is ok. But for the autonumber and timestamp methods, we only know the next
ID after inserting a new record. A catch 22!! Below we show you how to
solve this problem for the autonumber - the timestamp code is almost identical.
Incrementing
Unique ID’s after Inserting Data into a Database
1.
Using
an AUTONUMBER field for the ID. This
data type allows the ID to be automatically assigned or incremented when a new
record is inserted into that particular table.
Therefore, one can simply insert the new values for the other fields in
the record to be added using an INSERT INTO query as follows:
<CFQUERY datasource = “databasename” name = “queryname”>
INSERT INTO tablename (fieldname1, fieldname2. . . fieldnameN)
VALUES (value1, value2 . . . valueN)
</CFQUERY>
Although the ID in this case will not be
explicitly assigned, Access will automatically assign a new ID to this
additional record. The main problem
with using autonumber fields for ColdFusion applications (and database
applications in general) is that it is hard to find the value of the new ID
which is automatically assigned to the new record(s). It can be located using
<CFQUERY datasource = “databasename” name = “queryname”>
SELECT MAX(idfield) AS newid FROM tablename
</CFQUERY>
But this query
can be slow to run and has problems when two users add records at the same
time. Therefore, it is generally better to use the approach outlined in number
2 below when assigning ID values to new records.
2.
Using
a Number field for the ID. In this case
you must explicitly assign a new, unique ID to the added record.
<CFQUERY datasource = “databasename” name = “queryname”>
SELECT newid FROM NextID
</CFQUERY>
<CFQUERY datasource = “databasename” name = “queryname”>
UPDATE NextID SET NextID = NextID + 1
</CFQUERY>
<CFQUERY datasource = “databasename” name = “queryname”>
INSERT INTO tablename (idfield, fieldname1, . . . fieldnameN)
VALUES (#queryname.newid#, value1, . . . valueN)
</CFQUERY>
This example selects the current next ID from a
simple one row, one column table called NextID and we set the value of the new
ID to one greater than that maximum.
The second query then inserts this new record with the newly assigned ID
value into the table.
Alternatively, you can use the same SELECT max idea
as before but add one to the result:
<CFQUERY datasource = “databasename” name = “queryname”>
SELECT MAX(idfield) + 1 AS newid FROM
tablename
</CFQUERY>
The problem with this scenario is that it fails when
no records exist in the table initially.
In such a case, the maximum value for the ID field would be Null, and
hence the new ID value would likewise be set to Null, rendering the INSERT
query impossible. To avoid this
problem, a CFIF statement can be used to check the maximum ID value, and to set
it to zero if there are no records in the table. This is shown in the code below:
<CFQUERY datasource = “databasename” name = “queryname”>
SELECT MAX(idfield) + 1 AS newid FROM
tablename
</CFQUERY>
<CFIF queryname.recordcount
is 0>
<CFSET newid2 = 0>
<CFELSE>
<CFSET newid2 = queryname.newid >
</CFIF>
<CFQUERY datasource = “databasename” name = “queryname”>
INSERT INTO tablename (idfield, fieldname1, . . . fieldnameN)
VALUES (#newid2#, value1, . . . valueN)
</CFQUERY>
In a production site you may also want to use
<CFTRANSACTION> around the above code or put both SQL statements in a
stored procedure.
Using the
<CFINCLUDE> Tag
The CFINCLUDE tag references another .html, .txt, or .cfm file
which contains text, bitmaps, or other HTML code that will be incorporated in
the current page. It is advantageous to
use such a tag to reference a file, which contains items that will be included on
multiple pages within a web site. For
example, the ID creation code above might be used in many places in your site.
Putting it in one place makes improving the code simpler, as you only have to
edit it one time and not twenty!
Often an include template file
will contains header, footer, bitmap, or background information which needs to
appear on multiple pages. Therefore, if
the background or company logo needs to be updated or modified, it can be done
only once in the template file and then all the pages referencing the file in a
<CFINCLUDE> tag will automatically be updated. An example of using this tag is shown below, where “header.cfm” is a file containing header
information to be contained on all the web pages for a particular project, and
similarly, “footer.cfm“ contains
footer information to be carried across a group of pages.
<HTML>
<BODY>
<CFINCLUDE TEMPLATE = “header.cfm”>
additional
HTML code for the contents of this page . . .
<CFINCLUDE TEMPLATE = “footer.cfm“>
</BODY>
</HTML>
Using a Delete
Confirmation Page
This page is used to confirm that a user wants to delete a
certain record, which they have selected from another page, before actually
performing the delete. It acts
similarly to a dialogue box that asks the user to confirm their decision to
delete a file, except in straight HTML you can use pop up dialog boxes (you can
use JavaScript, but that is beyond this article). In this case, rather than having a link from a select page which
directly points to another page that performs a DELETE query, the link from the
select page points to the confirmation page, which then displays the record the
user has chosen to delete. The user can
then confirm their decision by clicking on a link, which goes to a page where
the DELETE query is performed, or they can cancel their decision and return to
the select page.
The following code
demonstrates how this works:
Select Page – displays records in table and a link to a
delete confirmation page for each record
<CFQUERY datasource="databasename" name="queryname">
SELECT IDfieldname, fieldname1, fieldname2,
… fieldnameN
FROM tablename;
</CFQUERY>
<CFOUTPUT>
<center> The tablename contains the following
records: </center>
<br><br>
<table
align="center">
<tr>
<td> fieldname1 </td>
<td> fieldname2 </td>
….
<td> fieldnameN </td>
<td> Delete Link
</td></tr>
</table
<br><br>
<CFLOOP query="queryname">
<table
align="center">
<tr>
<td
align="left"># fieldname1#</td>
<td align="left"># fieldname2#</td>
….
<td
align="left">#fieldnameN#</td>
<td> <a HREF = ”deleteconfirmationpage.cfm?linkvariablename=#IDfieldname#"> Delete:
Click Here to Delete this Record </a></td>
</tr>
</table>
</CFLOOP>
</CFOUTPUT>
Delete Confirmation Page – displays record from select
page which the user chose to delete
Are you sure you want to delete the record shown
below?
<CFQUERY datasource="databasename" name="queryname">
SELECT IDfieldname, fieldname1, fieldname2,
… fieldnameN
FROM tablename
WHERE IDfieldname=#url.linkvariablname#";
</CFQUERY>
<CFOUTPUT>
Field1: #queryname.fieldname1#
Field2: #queryname.fieldname2#
….
FieldN: #queryname.fieldnameN#
<br><br>
<a href="deletepage.cfm?linkvariablename=#queryname.IDfieldname#"> Yes </a> <br>
<a href="selectpage.cfm">
No </a>
</CFOUTPUT>
Delete Confirmation Page – deletes the record
<CFQUERY datasource = “databasename” name = “queryname”>
DELETE
* FROM tablename
WHERE IDfieldname=#url.linkvariablname#";
</CFQUERY>
Note: For this intermediate page to work correctly it is
necessary to both pass the unique id of the record to be deleted to the page
and for the intermediate page to in turn pass this id to the action page that
does the SQL delete on the record. This is because web pages are stateless and
don't know variables, unless you pass them in!
Summary
In this article we learned how to control Identity field
creation, how to save time with CFINCLUDE and how to control deletion in web
sites.
To Learn More
You can download a free 30 day-evaluation version of
ColdFusion from Allaire or request a free eval CD-ROM from the Allaire website http://www.allaire.com/
Allaire Corporation
1 Alewife Center
Cambridge, MA 02140
Tel: 617.761.2000 voice
Fax: 617.761.2001 fax
Toll Free: 888.939.2545
Email: [email protected]
Web: www.allaire.com
ColdFusion Resources
Allaire also maintains an extensive knowledge base and
tech support forums on their website.
CPCUG and TeraTech ColdFusion Conference http://www.cfconf.org/
TeraTech maintains a ColdFusion code cuttings called
ColdCuts at http://www.teratech.com/ColdCuts/.
This page also has links to about a dozen ColdFusion white papers in the CF
Info Center.
The Maryland ColdFusion User Group meets the second
Tuesday of each month at Backstreets Cafe, 12352 Wilkins Avenue, Rockville. See http://www.cfug-md.org/ for details and
directions.
The DC ColdFusion User Group meets the first Wednesday
each month at Figleaf , 16th and P St NW, Washington DC. See the
DCCFUG page on http://www.figleaf.com/
for details and directions.
Bio
Michael
Smith is president of TeraTech; an eleven-year-old Rockville Maryland based
consulting company that specializes in ColdFusion, Database and Visual Basic
development. You can reach Michael at [email protected]
or 301-424-3903.