Example: I have a books table in my database that has an auto incrementing id field, and a name field. I sort them by name automatically when posting to the browser. What is the best way to add a sort option for the entered data in the backend to be able to sort the books by moving them up or down?

So I would be able to control what book would show first, second, third, and so on, in the list of books shown to the public for viewing.

I have seen this feature in Mambo where you have the choice to re-number the items for sort order, and they typically have the up and down arrows that you can click as well.

Would I just add another field called sort_order and store a number in that box? Or is there a better/easier way to do it?

Thanks for any feedback and guidance.

Jonzy

    Yep...add another column that will become a sorting key...

    You'll need to make some mechanisms, obviously, to reorder the list of things but I've found that this is not really as hard as it needs to be...if you simply reorder the records one at a time it's very little work. If it's for a client though and they need more...you might consider getting out some heavy javascript to help you along (but you could certainly get much more complicated with a straight PHP solution as well - but it's not as user friendly).

      Thanks for the reply. If the sorting in the new column gets moved around, and then another item is added, what is the best way to have the next number available added in the sorting column? Do you go by the id?

        You could certainly use the id as the initial sort value, just remember that you will have to always add new items at the end of the list since the id always increments. After that you could alter them as you like with no drawbacks at all.

        Side note: I would make the sort column an index to keep your query time under control. You could also use a unique constraint to help keep any possible errors out of your table, but at that point you would have to wipe all of the sort values you wanted to change and THEN write in the new values one at a time...depending on how you're handling the array data on the rows themselves that might not be too convenient.

          Write a Reply...