Topic: Update the position of a record when a new entry is given that position

[Fleur]

I'm building a CMS website where 5 images are displayed per page and the user can upload new images and select the position of the image / change the position after uploaded. I want to make it so that if you select a position (eg 2) that is already in use then the record that you are updating/adding will keep the position you have given it (ie 2), but the record that had that position before must be shifted down by 1 position (to 3) and the record that was 3 becomes 4 etc. I have no idea where to start with this. I'm using PHP 5.3.0 and MySql 5.1.36. Please let me know if you have any suggestions.

[ajlisowski]

UPDATE `images` SET `position`=`position`+1 WHERE `position`>=2

[Fleur]

Thanks ajlisowski!
Now my problem is that if I move an image to a lower position then I need the other images to move positions up automatically so that you always have positions 1-5 and no blanks.
Does anyone have a solution?