MySQL record ordering

Question

In one of my current projects I have a dropdown (select) form element. I populate the options using an array (PHP):

$regions=array(
    1=>'North West',
    2=>'North East',
    3=>'South West',
    4=>'South East',
);

(array key=option value, array value=option text)

Now this offers me the convenience of being able to re-order the options as I wish.

The problem is now my client wants to be able to add/modify/re-order options from an admin interface. This means I now need to put these options in to a database table.

What that also means is that there is no easy way (not that I'm aware of) of putting the records in a custom order.

Let's suppose I create a regions table that includes a 'sort_order' field. Does anybody know of any solutions that will allow the client to re-order the records from an interface using simple up/down buttons?

Answer 1

Sorry - no.
You're correct about using a sort_order column...

Some might suggest using intervals like 10s - IE: 10, 20, 30... so you can insert in between. But at some point the scheme will have to be properly re-sequenced...

The "scorched earth" approach is to delete the existing rows, and assign the sort order based on the order in the UI for the newly inserted rows.

Answer 2

You need to store that custom order in the a table, along with the user specified sort order.

create table regions {
  id int primary key,
  name varchar(255) not null,
  sortorder int not null
};

To get the values in sort order you do select id,name from regions order by sortorder If you want the displayed order to be North East,South East,North West,South West your table would contain:

id    name         sortorder
1     North West   3
2     North East   1
3     South West   4
4     South East   2

Your GUI just updates the sortorder column based on the order the user arranges them.

Answer 3

The only way to represent ordering in a relational database is by designating a field that defines the order, as you suggested with the sort_order field.

In your admin interface, you could have your up/down buttons trigger a JavaScript function (assuming a web application) which would iterate through the newly ordered regions and assign an incrementing integer to each one. This integer can be assigned to a hidden field for each region, and posted with the full form data when changes are saved.

Upon posting the form, you can simply insert/update the records in the database, with the sort_order field as it was calculated on the client-side.

Getting an ordered result set from a database is usually quite straightforward:

SELECT * FROM your_table ORDER BY sort_order;

Answer 4

With only four options, I would use an HTML list box to show the four items, along with an up/down button that allows them to be reordered. An example of this can be found here.

When the items are reordered, update the value of a hidden field to hold the current order.

When the form is submitted, on the server, parse the value of the hidden field and update the sort_order column for each item.