update incrament value if new value is set

[bawaite]

what I would like to do is if a user submits a new article and they set the
order of the article to 1 then I would like a script to go through the table
and increment the current items so that there would not be to items with the
same order. but I would also like it to stop if there is a gap in the order.

example:
in the database there is
Item name Order Number
second 1
third 2
fourth 3
fifth 5

the user adds a new item with the order of one, the new list will appear like
this:
Item name Order Number
first 1
second 2
third 3
fourth 4
fifth 5

any suggestions?

right now I have it like this but it incumbents all item and does not stop if
there is a gap in the number, and will also increase all items even I set the
order to an unused item.





<cfquery name="get_current_order" datasource="#ds#">
SELECT qID, qOrder FROM question WHERE qOrder => #qOrder# AND qSurvey =
#qSurvey# ORDER BY qOrder
</cfquery>
<cfloop query="get_current_order">
<cfquery name="get_next_order" dbtype="query">
SELECT qID FROM get_current_order WHERE qOrder = evaluate(#qOrder#+1)
</cfquery>
<cfif get_next_order.recordcount gt 0>
<cfquery name="change_order" datasource="#ds#">
UPDATE question SET qOrder = (evaluate((SELECT qORder FROM question
WHERE qID = #qID#) + 1)) WHERE qID = #qID#
</cfquery>
</cfif>
</cfloop>

[blinkingCursor]

You could declare a variable outside the loop and store the id of the last item
changed there, and compare it to the next item in the loop. If currentID >
lastID + 1 then stop the loop. This only works if they are in order and you
only have one gap.