Delete image in DB, reorder remaining images

[DrumAt5280]

I have an Image Table in my DB which holds the names of the images.

ImageId | imageName | imageOrder
1 | dsc0001.jpg | 1
2 | dsc0002.jpg | 2
3 | dsc0003.jpg | 3
4 | dsc0004.jpg | 4

When a user deletes an image, let's say the first one dsc001.jpg, I would like this:

ImageId | imageName | imageOrder
2 | dsc0002.jpg | 2
3 | dsc0003.jpg | 3
4 | dsc0004.jpg | 4

To reorder in to this:

ImageId | imageName | imageOrder
2 | dsc0002.jpg | 1
3 | dsc0003.jpg | 2
4 | dsc0004.jpg | 3

I am guessing there is two way to accomplish this, the first way would be to:

<Delete the desired row from the DB, in this case #1>
<Query the same table to return the recordset, order by ImageId>
<Loop and UPDATE query to renumber the imageOrder column based on the the recordset from the last query>

Or by some MySQL function like ALTER TABLE command that does this for you that I don't know how to use.

Any ideas on the best way to accomplish this?

 

[johnwm]

I would set the table up with the imageOrder field as Primary key Auto_Increment. When there has been an edit then drop the imageOrder column

Alter table tblImage
Drop imageOrder

then re-add the column

alter table tblImage
add column
imageOrder INT NOT NULL AUTO_INCREMENT primary key
after imagename

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[r937]

i wouldn't reorder for deletes

the purpose of a sortorder column is to indicate the order of the rows, and by deleting a row, you aren't changing the order of the remaining rows, are you

as for inserting a new row in between two existing rows, this is why it's a good idea to set the increment for the sortorder column values by 100s or 1000s, which means after the new row is inserted, you don't have to re-number all the sortorder values above the insertion point

r937.com | rudy.ca

 

[DrumAt5280]

John and Rudy thanks for your replies.

Rudy got me thinking...

I am allowing 15 photos for each listing (it is a real estate type site), but I am not adding a feature where the user can change the position of the images using an up or down arrow interface (i might do this later), so when it comes down to it all i have to do is sort by the imageId and i am done!

Thanks.