Page 1 of 1

need to dynamically re-sort

Posted: Thu Jul 21, 2005 12:55 pm
by lilsavgrad01
I need to let the user dynamically change the order of items in a database. I have a sortOrder column (int) in the database which keeps track of the order that the items should be displayed. (my sql statement grabs the entries and sorts by sortOrder)

so essentially what I need is some clean code that can shift an item one space down or up.

I know I havent verbalized this very well, but can anyone help?

thanks ahead of time

Posted: Thu Jul 21, 2005 4:50 pm
by Ambush Commander
We want to move an item that's sorted 33 to 32. Table's called data. ID column is called id, and the ID of the object we're moving is 500 and the ID of the object that's sorted 32 is 200.

Code: Select all

UPDATE data SET sortOrder=sortOrder-1 WHERE id=500
UPDATE data SET sortOrder=sortOrder+1 WHERE id=200
Urp - It's = not ==. Thanks Jcart.

Posted: Thu Jul 21, 2005 4:53 pm
by John Cartwright
do not confuse the php comparison operator (==) and the SQL comparison operation (=)

Posted: Thu Jul 21, 2005 8:24 pm
by josh
Instead of changing the actual order of the rows, why not make a column named `order` that designates the order in which the items go, then when you want to change the order of something you just edit that column.

Then when you want to select the data in the right order you just say SELECT `whatever` FROM `table` WHERE `whatever` = 'whatever' ORDER BY `order`


Edit: oops I didn't read your post too well looks like you already have an `order` column.

For a shift up you'd just do this:
If X is the value of the item you want to shift up, you would say:
Y = X + 1
then you would swap the `order` for Y with the `order` value for X
In essence these two items have been swapped now, you could do Y = X + N where N would be the amount of shifts you want to take place, a negative value for N of course would be a shift down.

I know that's not code but I'm trying to explain the logic in the easiest way possible.