Rearranging records with code

[gmahler5th]

I am working on a data driven application where I need to be able to rearrange
the ordering of records through a Web interface. (Perhaps similar to how you
rearrange movies in your queue at Netflix.com) There are about 200 records of
eLearning content, and I would like a courseware designer to be able to
rearrange what order the pages will be displayed by the Web application.

Does anyone have any CF techniques that will enable me to do this easily?

[gmahler5th]

For example, I see a record at position 100 and I want this record to be put at
position 90, and the previous record 90 to become 91, 91 to become 92, and so
on, rearranging the remainder of the records by one position.

I am thinking of some kind of loop, but not sure how to do the loop until the
end of a recordset.

[NateNielsen]

to answer your question on this specific issues, if you had records that were
ordered sequentially in the database in a column named order_number from 1-150,
and you wanted to move record 100 to record 40, rather than a loop you would do
something similar to this :

<<attaching code>>

-Nate Nielsen
[url]http://www.webclarity.com[/url]

<cfset currentOrderNumber = 100>
<cfset newOrderNumber = 40>

<cfquery datasource="yourDSN" name="qReorder">
-- set the record we want to move to the zero position
update sections set order_number = 0 where order_number =
#currentOrderNumber#;

-- set all records at [currentOrderNumber] or below to +1 the current value
update sections set order_number = order_number + 1 where order_number >=
#newOrderNumber#;

-- set the record we want to move into position
update sections set order_number = #newOrderNumber# where order_number = 0;
</cfquery>