Inserting new rows after?

[RPG_2004]

I'm a real newbie here, so please forgive me if this is a stupid question.

I'm putting a 'Gallery' table together for my gallery in phpmyadmin.

My first field of seven is image_ID. It is set as a primary key with auto increment. The other fields are image_Description, thumb_URL, hires_URL etc..

I've entered thirty entries so far, and have now decided that I want to put a new entry in after image_ID 23, shifting the existing 24-30 up one(Does that make sense?).

If I try to insert an entry I get the duplicate entry error, which is understandable, but I cannot figure out a way around this.

In the structure options, under 'Add new fields' there's the option to add a field 'After ....', is there not a way of doing something similar for rows in tables. Maybe in SQL.

As it is a gallery, there will be new images created and I will need to swap the order around from time to time, so finding a solution to this, would be very helpful. My goal in the end is to put together a CMS to handle this, but I would like to figure something out at this stage.

Thank you in advance for any help.

RPG

[RPG_2004]

Well to answer my own newbie question, and I can't believe how long it's taken me to figure out such a simple op, here is the SQL.

UPDATE `gallery` SET image_ID = image_ID + 1 WHERE image_ID > 23 ORDER BY image_ID DESC

The ORDER BY .... DESC starts with the last value first and gets around the duplicate entry error.

What a mission?

RPG

[swirlee]

RPG_2004's advice is correct. Basically you have to add one to the id of every record after the one in question. And you have to do it backwards (hence the DESC).

Note that you should not write a script that does this frequently, because it's a very slow operation.

[RPG_2004]

Swirlee,

I was wandering last night about your comments on the efficiency of using this particular script.

As you know it's for a gallery, so the order is a matter of taste, and therefore has to be edited manually.

Currently I am using the image_ID to determine the order. That is my primary key with auto increment.

If I create a seperate field, such as image_Order, without auto increment and use this instead, would this be the efficient solution?

I could then access the images using something along the lines of ORDER by image_Order, image_ID DESC.

Therefore if 2 or 3 images share the same image_Order number, the last entry will be displayed first.

I guess what I would like to know is, am I on the right track?

Cheers

RPG