ColdFusion Tips and Tutorials

I. Comments:

Check out how you can get 3 free CFDJ magazines below.
I really like Teratech and all they have done to support the CF Community.

I am still working FULL time for those who were wondering.
Enough said!

Keep Coding,
Nathan Stanford
http://www.cftipsplus.com

If you have suggestions for articles send them to us.
If you would like to write for cftipsplus.com
send us an email to:

NathanS<at>nsnd.com

IF YOU WANT TO BE AN AUTHOR SEND IN YOUR COLDFUSION TIPS.

Remember this is a great way to get your name known in the
ColdFusion Community.

TeraTech has been nominated for best consulting and best training
ColdFusion company at CFDJ magazine. Please vote at:

http://www.sys-con.com/coldfusion/readerschoice2002/

********************************
* After you vote you can get a *
* 3-month free subscription to *
* CFDJ magazine! *
********************************

TeraTech has supported the ColdFusion community since 1998! They have organized over 10 ColdFusion conferences (including CFUN-02, CF_Underground and the the first Fusebox Conference), they have helped several local CFUGs get started, they help run MDCFUG, they run the community sites CFConf.org, CFBugHunt.org and publish helpful articles on ColdFusion in Fusion Authority, MDCFUG article site and CFDJ magazine.

Other community nominees includes House of Fusion, iMS mail system, Fusebox books.

No, you're not going to join Mr. Peabody in the Wayback machine. However, suppose you want to know what the price of an item was three months ago. "Time travel" was dropped from the capabilities of a popular research database years ago. However, ColdFusion can help.

Consider Your Needs
Whenever someone changes the price, you don't really want to do an update. You want a new row to be added to the table. However, you don't want all these old rows showing up when you run an item list. Therefore, you could use a Current field that would be set to 1 for only the Current row and 0 for non-current rows. This means that an edit would consist of two database accesses: one to mark all existing rows for this item as non-current and one to insert the new row (marked current). There are other nuances to consider, but this is enough to indicate what a likely data structure might look like.

Make a Table
Make a table ItemPrice with fields Item, Price, Current, and AsOf where Item is text, Price is a number, AsOf is a date, and Current is a number (or bit if you like).

List Items
The list, list.cfm, will let the user go to an add/change form to add an item. It will also let the user select an existing item with the intention of changing it or deleting it. Include a couple of files to support this later. Query only the current rows. Build a table that lists the result and includes appropriate edit and delete links. The links use urlEncodedFormat for the Item to accommodate spaces, etc.

<!--- Update or delete from database as needed --->
<cfinclude template="update.cfm">
<cfinclude template="delete.cfm">

<!--- Fill and display the list and controls --->
<cfquery name="ItemList" datasource="context">
select * from ItemPrice
where Current = 1
order by Item
</cfquery>
<a href="change.cfm?Mode=Add">Add</a>
<table border="1">
<tr><td>Edit</td><td>Item</td><td>Price</td>
<td>Delete</td></tr>
<cfoutput query="ItemList">
<tr><td>
<a href="change.cfm?Mode=Edit&Item=#urlEncodedFormat(Item)#">
Edit</a></td>
<td>#Item#</td><td>#Price#</td>
<td>
<a href="list.cfm?Mode=Delete&Item=#urlEncodedFormat(Item)#">
Delete</a></td></tr>
</cfoutput>
</table>

Delete Items
Here's delete.cfm. Deletion is easy. If the Mode parameter passed in the URL is "Delete", just delete every matching row whose item matches the Item in the URL. This could of course be made part of list.cfm instead of being included. Because urlEncodedFormat was used in passing the Item, urlDecode is used to retrieve it.

<cfparam name="Mode" default="">
<cfif Mode is "Delete">
<cfquery name="deleteItem" datasource="context">
delete * from ItemPrice
where Item = '#urlDecode(url.Item)#'
</cfquery>
</cfif>

Build an Add and Edit Form
Build change.cfm to add and edit individual items. It is submitted to itself by default; so, set up code that includes the list and stops loading this portion of the page when the user wants to get there. Because the form calls itself for "add", it needs a way to tell if there is anything to add yet. You could set Item empty and test it, or you can use a hidden field as is done here. The code to actually perform the add could be "inline", but it's included from another file here for simplicity.

<!--- Go back to list if asked --->
<cfparam Name="Back" default="">
<cfif len(trim(Back))>
<cfinclude template="list.cfm">
<cfabort>
</cfif>

<!--- Set defaults --->
<cfparam Name="Mode" default="Add">
<cfparam Name="Go" default="">

<!--- Add to database if requested --->
<cfinclude template="add.cfm">

Fill the form with the real current row if editing; otherwise, use an empty row. Set the form action to the list if editing (so it will return to the list after the change); otherwise, set the form action to this page. You don't want users editing the time directly; so, it's displayed as a label. You want to know if the user has changed the actual item name; so, keep track of the original name (OldItem) in a hidden field for later comparison. The normal submit button (Go) does double duty. Not only will it have an appropriate name (Edit or Add), but the presence of its value is used to tell the page that a new row must be added. This form has two submit buttons. If the Back button (labeled List) is pressed, earlier code will direct the user to the list.

<!--- Fill and display the form appropriately --->
<cfif Mode is "Edit">
<cfquery name="ItemGet" datasource="context">
select * from ItemPrice
where Item = '#Item#'
and Current = 1
</cfquery>
<cfset MyAction="list.cfm">
<cfelse>
<!--- Mode is assumed to be "Add" --->
<cfquery name="ItemGet" datasource="context">
select * from ItemPrice
where 1 = 0
</cfquery>
<cfset dummy=queryAddRow(ItemGet)>
<cfset MyAction="change.cfm">
</cfif>
<cfoutput>
<form name="Change" action="#MyAction#" method="post">
Item: <input name="Item" type="text"
maxlength="50" size="55" value="#ItemGet.Item#"><br>
Price as of #dateFormat(ItemGet.AsOf)#
#timeFormat(ItemGet.AsOf)#:
<input name="Price" type="text" maxLength="10"
size="12" value="#ItemGet.Price#">
<input name="OldItem" type="hidden"
value="#ItemGet.Item#">
<input name="Go" type="submit" value="#Mode#">
<input name="Back" type="submit" value="List">
</form>
</cfoutput>

If the item being edited has a history (non-current rows), show the history below the form.

<cfif Mode is "Edit">
<cfquery name="ItemOldList" datasource="context">
select * from ItemPrice
where Item = '#Item#'
and Current <> 1
</cfquery>
<cfif ItemOldList.recordcount>
<table border="1"><tr><td>ITEM</td>
<td>PRICE</td><td>LAST CHANGED</td></tr>
<cfoutput query="ItemOldList">
<tr><td>#ItemOldList.Item#</td>
<td>#ItemOldList.Price#</td>
<td>#dateFormat(ItemOldList.AsOf)#
#timeFormat(ItemOldList.AsOf)#</td></tr>
</cfoutput>
</table>
</cfif>
</cfif>

Add
This code, add.cfm, could be part of change.cfm. When the form has been submitted in Add mode, it returns to change.cfm, which includes this page. When the Go button the value of "Add", the user has asked to add a record. Check, and tell the user if the item already exists. Otherwise, add the new record, taking the current time as the time.

<cfif Go is "Add">
<cfquery name="ItemGet" datasource="context">
select * from ItemPrice
where Item = '#form.Item#'
</cfquery>
<cfif ItemGet.recordCount>
The item already exists in the database;
no action was taken.
<cfelse>
<cfquery name="ItemAdd" datasource="context">
insert into ItemPrice
(Item, Price, Current, AsOf)
values
('#form.Item#', #form.Price#, 1, #createODBCDateTime(now())#)
</cfquery>
<cfoutput>#form.Item#</cfoutput>
has been sent to the database
</cfif>
</cfif>

Update
Change.cfm sends edit data to list.cfm for processing, which includes this code: update.cfm. The response is not a simple update. If the user has submitted an edit, start by checking the item name. If the item name has changed, change it globally. Then, set Current in the current row for this item to zero. Finally, insert a new row instead of actually performing an update. Include the ODBC version of the current time.

<cfparam name="Go" default="">
<cfif Go is "Edit">
<!--- Globally change item name if different --->
<cfif form.Item is not form.OldItem>
<cfquery name="NameChange" datasource="context">
update ItemPrice set
Item = '#form.Item#'
where Item = '#form.OldItem#'
</cfquery>
</cfif>
<!--- Globally mark item not current --->
<cfquery name="ItemAge" datasource="context">
update ItemPrice set
Current = 0
where Item = '#form.Item#'
and Current = 1
</cfquery>
<!--- Add new row for item and mark it current --->
<cfquery name="ItemReplace" datasource="context">
insert into ItemPrice
(Item, Price, Current, AsOf)
values
('#form.Item#', #form.Price#, 1, #createODBCDateTime(now())#)
</cfquery>
</cfif>

Travel in Time
Browse list.cfm. Add rows, edit some, and delete some. Notice as you mark your items up and down that you really can travel back in time (or at least look back).

=Marty=