Reset ID(int) to last + 1?

[schart]

So I’m creating this sort of meme website as a project.

Say I upload this image, called [A].
[A] has an ID of 3. ( /img/3/ )
I want to delete [A], and upload instead
gets the ID of 4 ( /img/4 )

Now who has the ID of 3? No one, which is bad. Is there any way to auto reset the ID to the last “used” one, so that this wont happen?

[bungle]

Something like this (untested and off the top of my head)….

after deleting A you would need to run the following before inserting B

$sql=’SELECT MAX(id) AS max FROM imgtable’;
$query = mysql_query($sql);
$result = mysql_fetch_array($query);
$resetcount = $result+1;
$sql = ‘ALTER TABLE imgtable AUTO_INCREMENT=’.$resetcount;

That would set the auto id to the highest value in the table + 1

[bungle]

I am sure this could probably be simplified into a single query along the lines of

ALTER TABLE imgtable AUTO_INCREMENT = (( SELECT MAX(id) FROM imgtable ) + 1 )

but that is even more untested and off the top of my head ;)

[bungle]

You would only need to run it when deleting an image so

Put it in your php code as a query to be run after an image is deleted so somewhere in your delete_picture.php if you have one after the ‘DELETE from imgtable WHERE id = ‘ query is run.

Note that it will only work when the image deleted is the latest added image, i.e. if you have 20 images in the system and someone deletes 4 this won’t cause the auto increment to drop to 4, but if you delete 20 then it will reset to 20

[bungle]

Do you have a delete_picture.php or similar? Or if not where is the ‘DELETE from memos WHERE id = ‘ query that handles deletion?

[bungle]

no, you need to do first

mysql_query(‘DELETE from memes WHERE id = ‘.$idtoremove);

then follow it with

mysql_query(‘ALTER TABLE imgtable AUTO_INCREMENT = (( SELECT MAX(id) FROM imgtable ) + 1 )’;

which will reset your auto increment down if the image deleted was the last image.

[bungle]

yes that should work assuming that my query syntax works on your version of mysql, like i said its untested, YMMV etc. you need to run it and see if it works for you.

[bungle]

Try my first method so

mysql_query(‘DELETE from memes WHERE id = ‘.$img2del);
$sql=’SELECT MAX(id) AS max FROM memes’;
$query = mysql_query($sql);
$result = mysql_fetch_array($query);
$resetcount = $result+1;
$sql = ‘ALTER TABLE memes AUTO_INCREMENT=’.$resetcount;
$query = mysql_query($sql);

echo(“Image deleted”);
?>

[Author]

Posts