Creating a system to "move" records up or down in atable

[rowbeast]

Hey All,

I have a simple database table of info that I would like to be able to change
the output order of when queried, using a sort column.
the tables columns are such:
page_id(pk), page_name, page_content, sort_id

So I output the list of records with a simple query:
<cfquery datasource="#####" name="get_order">
SELECT page_id, page_name, page_content, sort_id FROM pages ORDER BY sort_id
</cfquery>

What i would like to have happen is have a link that would run a query to
change the value of the sort order for a certain record. (hence moving it up or
down in the list)
Where i run into trouble is figuring out how the syntax and concept for making
sure what amount i need to add to the current records sort_id value to make it
higher(or lower) than the next higher(or lower) record. After that, wouldn't i
need to increment or decrement the other records in the table so there were no
records with the same sort_id value?

I have included some concept code below, but i am having a hard time wrapping
my brain around what I need to do to get this working, so any help would be
greatly appreciated. Or perhaps is there any easier way to do this?

thanks!


<!-- Set starting sort number-->
<cfset basenum = #url.sort_id#>

<!-- based on a url variable, look for a number above or below the starting
sort numer-->
<cfif url.upordown = "up">
<cfquery datasource="#####" name="get_closest">
SELECT page_id, sort_id FROM pages WHERE sort_id > #basenum#
</cfquery>
<cfelse>
<cfquery datasource="#####" name="get_closest">
SELECT page_id, sort_id FROM pages WHERE sort_id < #basenum#
</cfquery>
</cfif>
<!-- Determine the value of the sort number closest to the the basenum -->
<cfquery datasource="#######" name="get_closest_num" dbtype="query">
SELECT MAX(sort_id) as closest_num FROM sort_id
</cfquery>

<!-- package the result into a variable -->
<cfset nextclosest = get_closest_num.closest_num>
<cfloop query="get_order">

<cfif order_id GT #basenum#>
hmm this is not making sense anymore...
<cfelseif order_id LT #basenum# >

<cfelse>

</cfif>

</cfloop>

[Dan Bracuk]

I think you might be able to do it with javascript, arrays, and a div tag. But it would be hard.

Hard for me anyhow.

[Kronin555]

<cftransaction>
<cfset sort_id_to_move = basenum>
<cfquery datasource="#####" name="moveToTempSortID">
update pages set sort_id = -1 where sort_id = #sort_id_to_move#
</cfquery>
<cfif url.upordown = "up">
<cfquery datasource="#####" name="makeRoom">
update pages set sort_id = sort_id + 1 where sort_id = #sort_id_to_move# - 1
</cfquery>
<cfquery datasource="#####" name="moveToNewSortID">
update pages set sort_id = #sort_id_to_move# - 1 where sort_id = -1
</cfquery>
<cfelse>
<cfquery datasource="#####" name="makeRoom">
update pages set sort_id = sort_id - 1 where sort_id = #sort_id_to_move# + 1
</cfquery>
<cfquery datasource="#####" name="moveToNewSortID">
update pages set sort_id = #sort_id_to_move# + 1 where sort_id = -1
</cfquery>
</cfif>
</cftransaction>

[rowbeast]

Kronin555, thanks for the code. I think i understand what you are doing here. I will have a go at it and re-post to this thread if I run into problems.

thanks!

[rowbeast]

Kronin555,

So that code works like a charm for incrementing and decrementing the sort_id,
thanks so much.
I thought i would make a note here for anyone else who searches for this kind
of thing that in order for your numbering scheme to not get out of sequence
(e.g. in a 3 record set the sort numbers could be 1,2,4 if you clicked down on
the 3rd record) you need to put some trapping in place to not allow the numbers
to move out of sequential order. (1,2,3) because if they to (as in example one)
it would take 2 clicks to move the third record above the second. So test for
first record and last record and if the user is trying to move them, and dont
let them!

Thanks for the help!