reordering list help or algo needed

tgh003@gmail.com wrote:
>
> So lets assume I have a list of tasks in db table (table looks like:
> ID, task, sort)
>
> And I want to reorder the task list without updating all the records
> in the table. I dont want to run a sql update stmt against every
> record to update the "sort" field.
>
> Any other easy way to do this?

Nope; UPDATE is simple enough. You may want to keep your task list
in an InnoBD table, so that reordering could be done as a transaction.
Say, you want to move the tenth task up to the second spot:

BEGIN;
UPDATE the_table SET sort=0 WHERE ID=XXX AND sort=10;
UPDATE the_table SET sort=sort+1 WHERE ID=XXX AND task>=2 AND task<10;
UPDATE the_table SET sort=2 WHERE ID=XXX AND sort=0;
COMMIT;

(I am assuming all records in the same task list have the same ID.)

> there has got to be a simple algo out there.

What's wrong with UPDATE queries? You only need three of them
regardless of the number of items in the list...

Cheers,
NC