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
need to dynamically re-sort
Moderator: General Moderators
- Ambush Commander
- DevNet Master
- Posts: 3698
- Joined: Mon Oct 25, 2004 9:29 pm
- Location: New Jersey, US
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.
Urp - It's = not ==. Thanks Jcart.
Code: Select all
UPDATE data SET sortOrder=sortOrder-1 WHERE id=500
UPDATE data SET sortOrder=sortOrder+1 WHERE id=200
Last edited by Ambush Commander on Thu Jul 21, 2005 4:57 pm, edited 1 time in total.
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
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.
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.