Sunday, September 14, 2008

Dynamic Sorting in coldfusion

<!--- First off we need to set a default sort order so for the purposes of this
tutorial we will use fname as our default. We set this using the cfparam tag --->

<cfparam name="sortBy" default="fname"></b>

<!--- Next thing we need to do is make our call to our database using the cfquery tag --->
<!--- In this example I have used the name of my company as the datasource name you would
of course use the datasource name you have setup --->
<b> <cfquery name="dynamicSort" datasource="rez_productions">
SELECT id, fname, lname
FROM sorting
<!--- Note: the field we are sorting by is a dynamic variable which we set with our cfparam tag --->
ORDER BY #sortBy# Asc
</cfquery>

<!--- It is easier for me to give you the entire page layout for formating purposes. --->
<html>
<head>
<title>Dynamic Sorting</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>

<table width="500" border="1" align="center">
<tr>
<td>
<div align="center"> <!--- Note the links all reference the page we are on, yet they are passing the field name
that we want our end-user to be able to click if they want to sort by this particular field.
All three links are basically the same the only change is in the value that they are passing --->
<a href="dynamic_sort.cfm?sortBy=ID">Id</a>
</div>
</td>
<td align="left">
<div align="left">
<a href="dynamic_sort.cfm?sortBy=fname">FirstName</a>
</div>
</td>
<td>
<div align="left">
<a href="dynamic_sort.cfm?sortBy=lname">Last Name</a>
</div>
</td>
</tr>
<!--- Now we do our output. It is important to place our cfoutput tag in the proper place
so that we can dynamically generate the needed rows for the amount of data we are displaying.
Because we do not know how many rows we need we place the cfoutput tag before the opening <tr> --->
<cfoutput query="dynamicSort">
<tr>
<td><div align="center">#dynamicSort.id#</div></td>
<td>#dynamicSort.fname#</td>
<td>#dynamicSort.lname#</td>
</tr>
<!--- Note that the closing cfoutput tag is placed after the closing </tr> tag this is part of
dynamically generating rows --->
</cfoutput>
</table>
</body>
</html>


________________________________________
Thats pretty well it for basic dynamic sorting. Here is the MySql Database I used to create
this tutorial.

CREATE TABLE sorting (
id int(10) unsigned NOT NULL auto_increment,
fname varchar(255) NOT NULL,
lname varchar(255) NOT NULL,
PRIMARY KEY (id)
);

#
# Dumping data for table 'sorting'
#

INSERT INTO sorting VALUES ( '1', 'Bugs', 'Bunny');
INSERT INTO sorting VALUES ( '2', 'Road', 'Runner');
INSERT INTO sorting VALUES ( '3', 'Daffy', 'Duck');

No comments: