Hi there,

What would be the most efficient way to have items displayed on a webpage
sorted in the order a CMS administrator wishes? So for instance, lets say I
have a table employee:

CREATE TABLE `employee` (
`id` int(10) unsigned NOT NULL auto_increment,
`image` varchar(255) NOT NULL default '',
`name` varchar(100) NOT NULL default '',
`age` int(10) unsigned default NULL,
`description` text,
PRIMARY KEY (`id`)
)

The employees get displayed on a webpage, and the CMS administrator has the
ability to move employees up and or down on that page in the CMS. How would
I go about doing that? I'm hoping there is some solution in which I don't
have to iterate through all employees and set a sort number. I have this
faint memory of some mathemetic formula of trick that would only need one
item to be changed (i.e. the item that is moved). Can't recall it though.
Maybe my mind is playing tricks in that there is no such thing anyway. ;-)

Thank you for any advice.

Cheers

amygdala wrote:
If there is some encoding schema that allows you to update only one row,
I think it will be to expensive to decode the sortorder during selection.

One idea is to leave gaps in the sortorder so that it is possible to
insert a new number between two items. Another idea is to use a first
and secondary sortorder. Both ways will force you to renumber the
sortorder every once and a while.

/Lennart

[...]

"Lennart" <erik.lennart.jonsson (AT) gmail (DOT) com> schreef in bericht
news:f0t1vh$fi5$1 (AT) registered (DOT) motzarella.org...
Thanks for the response. I working on a solution now in which I at max
change two items when an item is being moved up or down: The item moved and
the item next to the one being moves. Which seems pretty obvious once I
started coding it. Off course when I delete an item I still need to iterate
through all items behind it in the sortorder to correct their sort numbers,
because I'ld rather not create gaps in the sort numbers.

Anyway, I'm having a lot of trouble of coding the SQL that should move the
item up or down. I posted a sort of follow up in comp.databases.mysql, since
this groups doesn't seem to be that active.

Cheers