need to dynamically re-sort

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
lilsavgrad01
Forum Newbie
Posts: 9
Joined: Thu Jun 09, 2005 9:50 am

need to dynamically re-sort

Post 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
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post 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.
Last edited by Ambush Commander on Thu Jul 21, 2005 4:57 pm, edited 1 time in total.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

do not confuse the php comparison operator (==) and the SQL comparison operation (=)
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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.
Post Reply