Drew Drew - 2 months ago 5
MySQL Question

How to re-assign AUTO_INCREMENT column for every row in a MySQL table using PHP

I have an image gallery which website members can upload images to. When an image is uploaded, a MySQL row is written, containing various pieces of information about the image, member, etc. This row uses AUTO_INCREMENT to create its ID, so that

getimage.php?id=XX
can fetch the image to be displayed.

I loop through the IDs with a for-loop to display the images within the gallery.

If I delete the 5th row/image, for example, the AUTO_INCREMENT goes from
123456789
to
12346789
.

I would like to re-assign the ID to each row in the MySQL table, starting from the ground up. So 12346789 becomes 12345678. How would I achieve this?

Answer

I found this to work perfectly and quite quickly so here it is:

ALTER TABLE tablename DROP id

ALTER TABLE tablename ADD id INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id), AUTO_INCREMENT=1

I know this isn't the proper approach however for my specific situation this is exactly what was needed. There is nothing within the table I was using that is referred to either from or to another table.

Comments