Click to Sort Records
Get the Code for this Tutorial
View a Working Example of This Code
Comment on this Tutorial
Organizing data is generally important to most of us. This tutorial will help to keep data organized by sorting it specifically how we want. We'll do this by making up and down links that will move each record up or down one line.
Often, I find myself simply using a text input form to manually type in a number that represents the position of the current row of data. This works great for me, and people that are used to this sort of thing, but to expect a everyday client that is busy with their own worries to have to do this is just nuts. Clients like to click, not think. Not because they can't but because the shouldn't have to think about things like manually settings sort orders for database records. It' just not their job. It yours, the developers.
The Code
For this tutorial, I am going to put all the functionality in one template. Let's call it index.cfm. You can break it out to a separate display and action page if you want.
The field 'position' is a integer field. A position value of 1 would be the topmost record to be displayed. Next would be 2, 3 etc...
First off, check if the url parameter called move exists. If it does, we will process the rearrangement of the records.
<cfif structKeyExists(url,"move")>
Get the position value and max position value from our sample database. Make sure you have the current version of the sample database. The first version did not have the position field.
Since we are using the MAX function in mySQL, we need to also use GROUP BY. We will use this query whether we move the record up or down.
<cfquery datasource="#dsn#" name="getPosition">
SELECT position,(SELECT MAX(position) FROM cfml_example_data) AS maxPosition
FROM cfml_example_data
WHERE id = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#url.id#">
GROUP BY position
</cfquery>
If the url parameter 'move' exists, we check to see if the value of url.move is 'up' or 'dwn'
First let's process the 'up' code. This will move the record up one line.
<cfif url.move IS "up">
If the current records position is already at position 1, there is no way to go any further up. However, if the record position is greater than 1, we need to move it up.
<cfif getPosition.position GT 1>
The first query updates the position of the record immediatly above the record that we are moving to +1
<cfquery datasource="#dsn#" >
UPDATE cfml_example_data
SET position = position + 1
WHERE position = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#getPosition.position - 1#">
</cfquery>
The next query updates the position of the record that we are moving to -1
Between the two queries, we simple took two rows - the one we want to move up and the row above it - and swapped their values.
<cfquery datasource="#dsn#">
UPDATE cfml_example_data
SET position = position - 1
WHERE id = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#url.id#">
</cfquery>
</cfif>
If url.move is 'dwn', then we need to run two queries very similar to the previous two. Some slight differences though.
<cfelseif url.move IS "dwn">
Here we will use the MAX value from our first query. If the record that we want to move down is already at the bottom, do nothing.
<cfif getPosition.position LT getPosition.maxPosition>
Otherwise, we are going to swap positions with the record we are moving and the one directly below it.
The first query takes the record immediatly above the one we are moving and moves it up by 1. Remember, to move a record up, we need to decrement the position value. Number 1 is at the top.
<cfquery datasource="#dsn#">
UPDATE cfml_example_data
SET position = position - 1
WHERE position = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#getPosition.position + 1#">
</cfquery>
The second takes the record we are moving and moves it down by 1. That is, we add one to the position value.
<cfquery datasource="#dsn#">
UPDATE cfml_example_data
SET position = position + 1
WHERE id = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#url.id#">
</cfquery>
Finish the cfifs from above...
</cfif>
</cfif>
Now that we are all rearranged, we'll head back over to the display page. In this case, it is the same template, but with no url parameters, so we will skip everything we processed above.
<cflocation addtoken="No" url="index.cfm">
</cfif>
If there are no url parameters that we need to process, let's display our data.
First the query, simple enough.
<cfquery datasource="#dsn#" name="get_data">
SELECT id,names,ages,position
FROM cfml_example_data
ORDER BY position
</cfquery>
And the output, pretty easy. Make the table and headers like we always would...
<table>
<tr>
<td>First Name</td>
<td>Age</td>
<td>Sort Position</td>
<td>Sort Order</td>
</tr>
And output the query
<cfoutput query="get_data">
<tr>
<td>#names#</td>
<td>#ages#</td>
<td>#position#</td>
<td>
These are the links that we can click to move a record up or down.
Both have the id of the record we want to move, and both have a value for the 'move' parameter - 'up' or 'dwn'
<a href="index.cfm?id=#id#&move=up">Up</a>
<a href="index.cfm?id=#id#&move=dwn">Down</a>
Finish off the output and the table.
</td>
</tr>
</cfoutput>
</table>
Notes
You might want to look into a way to prevent search engines and spiders from following the up and down links. If this code is used in a restricted are that requires authentication it is no big deal, if it is in a public area of the site, make sure that spiders can't follow it. One possibility would be to use a form with buttons for up and down, and / or javascript to trigger the event. This script is mainly intended for management areas that would be password protected, and impossible for a spider or bot to get to.
For larger recordset's you could create another link to move the record by 5 rows, or straight to the top or bottom.
You will also want to note, that for this technique to work smoothly you need to set a 'position' value for every new record inserted. You could use a drop down to choose which record to insert the new record before or after, and update the table accordingly. Don't forget to handle the 'position' values on a record delete also.
Consider creating a cfc that you can call that will call up the entire recordset, loop through it, and reset the values of 'position' to be in sequential numeric order, so there are no records with the same 'position' value or gaps between position values. Run this every time a record is inserted or deleted. It may seem like too much work, but it makes a very impressive feature for a user friendly back end or master detail view.
____________
Tutorial Code
Download the Database<!--------------------------------------------
Kevin Sargent
December 13th 2005
http://www.lot-o-nothin.com/cfml/
Please Give Credit Where Used.
---------------------------------------------
index.cfm
--------------------------------------------->
<!---{ If the variable url.move is present, execute the sorting procedure }--->
<cfif structKeyExists(url,"move")>
<!---{ Get the sort value for the record we are moving, and get the max sort value in the table }--->
<cfquery datasource="#dsn#" name="getPosition">
SELECT position,(SELECT MAX(position) FROM cfml_example_data) AS maxPosition
FROM cfml_example_data
WHERE id = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#url.id#">
GROUP BY position
</cfquery>
<!---{ If url.move is "up" run the following queries to update the table and sort orders }--->
<cfif url.move IS "up">
<!---{ Position 1 is the top, so no need to move the record up if it is alread there... }--->
<cfif getPosition.position GT 1>
<cfquery datasource="#dsn#" name="getPosition">
UPDATE cfml_example_data
SET position = position + 1
WHERE position = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#getPosition.position - 1#">
</cfquery>
<cfquery datasource="#dsn#" name="getPosition">
UPDATE cfml_example_data
SET position = position - 1
WHERE id = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#url.id#">
</cfquery>
</cfif>
<cfelseif url.move IS "dwn">
<!---{ getPosition.maxPosition is the bottom, so only move it down if it is not already at the bottom }--->
<cfif getPosition.position LT getPosition.maxPosition>
<cfquery datasource="#dsn#" name="getPosition">
UPDATE cfml_example_data
SET position = position - 1
WHERE position = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#getPosition.position + 1#">
</cfquery>
<cfquery datasource="#dsn#" name="getPosition">
UPDATE cfml_example_data
SET position = position + 1
WHERE id = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#url.id#">
</cfquery>
</cfif>
</cfif>
<!---{ Loads this page again with no url parameters. }--->
<cflocation addtoken="No" url="index.cfm">
</cfif>
<!---{ Get the records to display }--->
<cfquery datasource="#dsn#" name="get_data">
SELECT id,names,ages,position
FROM cfml_example_data
ORDER BY position
</cfquery>
<table>
<tr>
<td>First Name</td>
<td>Age</td>
<td>Sort Position</td>
<td>Sort Order</td>
</tr>
<cfoutput query="get_data">
<tr>
<td>#names#</td>
<td>#ages#</td>
<td>#position#</td>
<td><a href="index.cfm?id=#id#&move=up">Up</a> <a href="index.cfm?id=#id#&move=dwn">Down</a></td>
</tr>
</cfoutput>
</table>
Comments
Webmaster
03/14/2006
If you are using a DBMS that does not allow subqueries, you can work around this with a JOIN to get the MAX(position) from the table.
