Re: Ordering rows in a table

On Tue, 2003-02-18 at 03:04, David Härdeman wrote:
> Hi,
>
> the subject of this message may be a bit misleading.
>
> I have the following scenario...I have a menu on a webpage that points
> to different pages, the table looks something like this:
>
> id SERIAL, title varchar(64), url varchar(256)
>
> Now I'd like to add an admin page where the order of the menu items
> can be changed. Specifically commands for moving an entry to the top
> or bottom of the list would be good. But I have no idea how to do
> this.
>
> The ideas I've had som far...
>
> adding an extra sort field (SERIAL), this would allow the order to be
> swapped between two rows by swapping the value of their sort
> fields....this is not exactly what I want tough.
>
> I've also considered reading the entire table and calculating new
> "sort" values for each row but this seems very ineffective and not
> very "elegant".
>
> So how would I do this in a "neat" way?

As you believe, I think adding a new field for sorting will be
essential.

In doing this myself I usually display the whole table, with those sort
fields, in the admin page and just let people edit the numbers.  That
way they can re-order the whole lot in one submit.  Users don't seem to
find this a difficult exercise to understand.

Swapping can be a frustrating exercise for the user if there is lots of
ordering to change.

If the list is very long, you can allow update on a single row, where
you allow people to specify a position in the list, and then renumber
all entries to allow that position.  This is fairly heavy on update
though.  You can refine it by making the renumbering create gaps in the
sequence, or use a float, and only do the renumbering when it is
absolutely necessary.

Hope this is some help,
                    Andrew.
--
---------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St,  Wellington
WEB: http://catalyst.net.nz/         PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201     MOB: +64(21)635-694    OFFICE: +64(4)499-2267
           Survey for nothing with http://survey.net.nz/
---------------------------------------------------------------------