Hi richardlane,

Can you clarify - do you mean the user re-arranges the items on the page in some way (how?) then clicks update, or clicks up or down links on individual items that roundtrip to the server?

Now, if it was a list I could just save the list index as the new itemorder, but it isn't

I don't understand what you mean by this. Can you elaborate?

What I understand is that your site displays some items and the user is able to rearrange them on the screen. You want to persist the position by saving the indices in the database.

The only possible optimization is to avoid updating those records whose indices haven't changed.

Sorry for the delay in replying, I didn't get email notification that anyone had posted!

The scenario is in a content management system where users can upload images, which are then displayed initially in the order in which they were uploaded. The database stores the path to the image. Users can then delete any image, or add a new one to the list, but I also want to give them the facility to 'move up' and 'move down' each image therefore affecting the order they are displayed. I envisage 2 buttons labelled 'up' and 'down' next to each image which submit the form, the handler for each button should then update the table to save 'itemorder' appropriately - is that clear?

If the images were just rows in a list I know how to re-order items in a <select>, but this is more complex. I just wondered if there was any smart function in php or mysql that automatically re-assigns an integer sequence incrementing by 1.

for example, it would be easy if the 'up' button handler just took the imageid and did:
UPDATE items SET itemorder + 1 WHERE imageid = $id

but the code woud also have to update every other row and reset the itemorder, or else instead of:

itemid: 1 itemorder:1
itemid:10 itemorder:2
.
.
itemid:25 itemorder:10

you might have:
itemid:1 itemorder:1
itemid:10 itemorder:1 <--
.
.
itemid:25 itemorder:10

actually this might be a cool way of doing it: [phpriot.com...]

though i have just implemented a simpler method with a list box...

Hi Richard,
You don't have to update the itemorder of every item, just the two that are changing positions. So you could do something like:

eg Click "up" on item, passing itemid:

1. SELECT itemorder of of the clicked itemid
2. Decrement (or increment if moving down) this itemorder and use it to SELECT the itemid of the other affected record
3. Run UPDATE's on the two records changing place, something like (reversed if moving down) -
UPDATE items SET itemorder - 1 WHERE itemid = $id
UPDATE items SET itemorder + 1 WHERE itemid = $Otherid

HTH

thank mattur, you are quite right, i could do it with just the two updates... i had opted for displaying all the paths in a select box with multiple, that way i can shift and ctrl select items and move them any number of times, then onsubmit selectall the items and loop the resulting array.

but i'm going to have a play around with the drag/drop plus ajax and see if i can get it working this way, and your 2 update statements will be used.

cheers.