[thelist] mySQL Sequencing

Liz Lawson lizlawson at charitycards.co.uk
Wed Dec 20 05:40:42 CST 2000

I wouldn't use the id unless you know that firstly you don't want the order
to change (you won't want to change your ids!) and secondly you want to
order the items in the same order as the ids, most likely the order they
were added.

To control the display order of records, I include a column called
'priority' and order my results by that.

You could enter a priority value as part of a new record, but this means
that either you will have two products with the same priority (not a problem
for the order by clause, but does mean that you won't have control over the
order of records with the same priority), or your add script will also have
to update all existing records with priority values higher than that of the
new product by adding 1 to the priority.

In my system all new records go in with priority 0, which puts them either
at the beginning or the end. I then have a separate page with a javascript
order sorter (based on an example posted to this list, thanks!) where the
user can select an item in a form menu and move it up or down the list. When
this page is generated (PHP), the results are pulled from the db in priority
order, and an html menu list is created in the form

<option value = 'id_number::priority_number'>name_label

The priority number here is *not* the one currently in the db, but assigned
in sequence as the script steps through the results. If any repeats of a
priority value do exist in the db, they will be eliminated whenever the
table is sorted.

Whenever an item is moved, the javascript splits the value at :: and
switches the id_number part and the name_label for the next above or below.
The priority number stays in the same place. When the form is submitted, the
PHP script again splits the values at :: and updates each record with its
new priority value.

I haven't put any code up 'cos there's quite a bit, plus whoever wrote the
javascript originally (many apologies, I forgot who...) might want to jump
in with it. Happy to send the code through if you want it though.



>Does anyone know of a way to "sequence" items listed in a mySQL db?
>Specifically to where when a client makes an addition to their db, the
>form will also allow them to set the order/position that the item would
>appear online....or is there a way to code this type of feature into our
>PHP document? Couldn't we do something of this sort using the id?

More information about the thelist mailing list