changing the order of items in a database

[Giddy Rob]

Hi,

 

I have a client that wants to be able to change the order of their items, which are held in a mysql database. Is there an easy way to do this with PHP?

 

The database is currently set so it displays in order of the primary key which is auto incremented with an int every time a new records is created.

 

Cheers in advance

 

Rob

[jonsjava]

ORDER BY `id` DESC;

ORDER BY `id`;

?

I'm betting that's not what you meant, though.

[The Little Guy]

You could just change the primary key, but I don't know how much that could mess up your database. depends on what depends on that primary key, and how.

 

Otherwise, you will need to make another table, that holds the order of all the items.

[phpzone]

I assume they want to specify the order uniquely for a each item rather than just saying DESCending or ASCending, if you just

want to do ascending/descending ordering, then the previous posts explain.

 

But to sequence in a custom manner.. we usually place a 'displayseq' field in the same table.    Then modify the displayseq for the items based on user action (move up / move down), this requires renumbering the items in the category each time.

 

To do this we start the first items displayseq at 4, then each item goes up by 2 in sequence

 

ITEM 1    = display_seq = 4

ITEM 2    = display_seq = 6

ITEM 3    = display_seq = 8

 

If you want to move ITEM 1 down, you add 3 to it's display_seq, giving:

 

ITEM 2 = display_seq = 6

ITEM 1 = display_seq = 7

ITEM 3 = display_seq = 8

 

Then you call a routine to select them by order of display_seq ASC and update from 4 incrementing in steps of 2 again, so

you are back to:

 

ITEM 2 = display_seq = 4

ITEM 1 = display_seq = 6

ITEM 3 = display_seq = 8

 

If anyone knows a better way, I'd really like to hear it because there probably is a better way :-)