ColdFusion Tips and Tutorials

I. Comments:

ColdFusion is still growing. Even with the problems around the globe.

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:

admin@cftipsplus.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.

One problem you'll encounter in getting data from a database table into this configuration is that the query may not fully populate every column in every row. Some fields in the table may be empty. ColdFusion (and other languages) will try not to append empty elements to a list, and when reading data from a list that has gaps, will skip an empty element, reading data from the NEXT element as if it had come from the empty column. This has the logical impact of mixing data across fields and will also cause an error when it becomes time to read the last element in the row. To avoid this, you need to populate every column and then ignore the placeholder you use for this purpose when it comes time to display the data stored there.

Begin with Data
Create a table named Family that will be joined with itself to solve a practical problem. Some families help others (HELPS=1); some need visits (NEEDS=1); most fall in both categories. The numeric equivalent of their X and Y map coordinates (to be used in computing distance between families) are XNr and YNr. Call this table Family. Give it at least these columns:

FAMILY,HELPS,NEEDS,XNr,YNr
Fenwick,0,1,13,19
Roberts,0,1,13,21
Quales,1,1,14,18
Vance,0,1,15,19
Crowley,0,1,16,15
Dobbs,1,1,16,21
Jameson,0,1,18,10
Mack,1,0,18,18
Lubbock,0,1,18,19
Eggbert,0,1,18,21
Hicks,1,1,19,18
Thompson,1,1,19,18
Stanford,0,1,19,20
Jones,1,1,20,20

Perform the Join
Place all code in matrix.cfm. To join a table with itself, use an alias. The following query uses alias "a" to refer to families in their helper role and "b" to refer to families in the role of receiving a visit. The Distance construct uses the Pythagorean theorem to determine the distance between the helper and the family being visited. (Subtract the square of the difference of the X coordinates from the square of the distance between the Y coordinates and then take the square root of the result.) Each row will wind up containing the following fields: Helper, its X and Y coordinates (HelperX, HelperY), Family (receiving a visit), its X and Y coordinates (FamilyX, FamilyY), and the Distance between them.

<!--- Determine the distance between each useful pair
and sequence by shortest distance first --->
<cfquery name="tryAll" datasource="context">
select a.family as Helper,
a.XNr as HelperX, a.YNr as HelperY,
b.Family as Family,
b.XNr as FamilyX, b.YNr as FamilyY,
((a.XNr-b.XNr)^2+(a.YNr-b.YNr)^2)^.5 as Distance
from Family a, Family b
where a.helps = TRUE
and b.needs = TRUE
and a.Family <> b.Family
and ((a.XNr-b.XNr)^2+(a.YNr-b.YNr)^2)^.5 <> NULL
order by ((a.XNr-b.XNr)^2+(a.YNr-b.YNr)^2)^.5
</cfquery>

Store the data
For a large set of data, writing each distance back to a database in order to manipulate the data on disk would be slower than manipulating it in memory. Load the data into a matrix.

The backbone of this matrix is an array. For each row in the query, append every element in the row to a list inside the current element of the array. In case you want to use the Distance later as a string for sorting purposes, format the number with leading zeroes. Because some of the values may have been empty in the database, tack an extra character onto every element as you append it to the list. The reason the tilde is chosen here is that omitting it from data should not cause a hardship and its influence on manipulation is minimized because it falls after other characters in sort sequence.

<!--- Load the matrix --->
<cfset Matrix=arrayNew(1)>
<cfloop query="tryAll">
<cfset TempList="">
<cfset TempList=listAppend(TempList,"#Helper#~",";")>
<cfset TempList=listAppend(TempList,"#HelperX#~",";")>
<cfset TempList=listAppend(TempList,"#HelperY#~",";")>
<cfset TempList=listAppend(TempList,"#Family#~",";")>
<cfset TempList=listAppend(TempList,"#FamilyX#~",";")>
<cfset TempList=listAppend(TempList,"#FamilyY#~",";")>
<cfset TempList=
listAppend(TempList,"#numberFormat(Distance,0000)#~",";")>
<cfset Matrix[currentrow]=TempList>
<cfoutput>#Matrix[currentrow]#<br></cfoutput>
</cfloop>

Display the Matrix
To demonstrate that the data has been correctly stored, create a simple slash-delimited display. The replace function works readily to remove the tildes.

<!--- Simply display the matrix --->
<cfloop from=1 to="#arrayLen(Matrix)#" index="Row">
<cfoutput>
#replace(listGetAt(Matrix[Row],1,";"),"~","")#/
#replace(listGetAt(Matrix[Row],2,";"),"~","")#/
#replace(listGetAt(Matrix[Row],3,";"),"~","")#/
#replace(listGetAt(Matrix[Row],4,";"),"~","")#/
#replace(listGetAt(Matrix[Row],5,";"),"~","")#/
#replace(listGetAt(Matrix[Row],6,";"),"~","")#/
#replace(listGetAt(Matrix[Row],7,";"),"~","")#/
</cfoutput><br>
</cfloop>
<p>

Manipulate the Matrix
To demonstrate that the matrix can be manipulated without losing its integrity, sort it. In this example, the sequence of columns lends itself to a useful sort. (If desired, you could also rearrange columns, introduce new ones, and remove columns prior to sorting.)
Many similar functions return a changed object at the left side of the assignment. However, the arraySort function merely returns "Yes" (if the sort is successful, not a sorted array). It sorts the array in place. Therefore, when using this function, a dummy assignment (or no assignment at all) is appropriate. The "textnocase" parameter causes the case of text to be ignored in the sort. Bear in mind that the entire string is being sorted: all columns.

<!--- Sort the matrix --->
<cfset Dummy=ArraySort(Matrix,"textnocase","asc")>

Display it Again
For proof that the sort worked properly, copy the preceding display code and use it again:

<!--- Simply display the matrix --->
<cfloop from=1 to="#arrayLen(Matrix)#" index="Row">
<cfoutput>
#replace(listGetAt(Matrix[Row],1,";"),"~","")#/
#replace(listGetAt(Matrix[Row],2,";"),"~","")#/
#replace(listGetAt(Matrix[Row],3,";"),"~","")#/
#replace(listGetAt(Matrix[Row],4,";"),"~","")#/
#replace(listGetAt(Matrix[Row],5,";"),"~","")#/
#replace(listGetAt(Matrix[Row],6,";"),"~","")#/
#replace(listGetAt(Matrix[Row],7,";"),"~","")#/
</cfoutput><br>
</cfloop>
<p>

Discussion
Browse matrix.cfm. Remove some of the data from the table to prove that the code won't crash when data is missing. This technique gives you some of the advantages of database manipulation while letting you work entirely in memory.
=Marty=

Advanced, Intensive ColdFusion Training!
Visit this site. If you have plans to get training here is a company
that provides Advanced, Intensive ColdFusion Training. Check them out.

http://www.coldfusiontraining.com/index.cfm?ref=cftipsplus