CODEWITHSUNDEEP
phpmysql
simme
simme

Reputation: 1554

Managing unique IDs on deletion in MySQL

My title may be a little off, but I'm not sure how to explain it.

I'm currently working on a photo portfolio where the user will be able to upload images from a web interface. When this is done, the following happens:

The image is uploaded via a HTML-form -> PHP. A SQL-record is created holding the following information:

ID (Autoincrement) - Image title - Image Desc - Filepath

The user is also able to administer their uploads via another page in the web interface. When this happens, the SQL-record is deleted together with the file. However, when the user then adds a new file, the next ID number is used, leaving the database with an empty space where the deleted record used to be.

I understand that this is because autoincrement is not to be used for IDs displayed to the user, but for other purposes. However, as I'm ftmb using this information to display the photo number in the image viewer, i'd like a more suitable approach. What I need is essentially a MySQL-column containing ints that will fill the gaps created when deleting records. For example:

1 - 2 - 3 - 4 - 5 - 6 - 7 - 8

Upon deleting record 5, I need the IDs to fill the gaps so I instead of

1 - 2 - 3 - 4 -   - 6 - 7 - 8

Get 1 - 2 - 3 - 4 - 5 - 6 - 7

Any suggestions?

Thanks for your help. :-)

Upvotes: 0

Views: 201

Answers (2)

Jose Vega
Jose Vega

Reputation: 10258

Try:

SET @count = 0;
UPDATE `<table>` SET `<table>`.`<column>` = @count:= @count + 1;

The query above will loop and go through each row and update the <column> field with a sequential integer.

This should answer your question, but to my understanding your issue with the image viewer might be something completely different and not related to the auto increment problem.

Upvotes: -1

VoteyDisciple
VoteyDisciple

Reputation: 37803

MySQL doesn't have a good facility to do this automatically, but you can do it manually with something like:

SELECT sequence_number FROM photo WHERE photo_id = 729; -- This would be 5.
DELETE FROM photo WHERE photo_id=729;
UPDATE photo SET sequence_number = sequence_number - 1 WHERE sequence_number > 5;

The UPDATE query can include whatever other conditions (e.g., a matching album_id) would describe the set of pictures whose numbers are meant to be sequential.

However, having said that, if you're just always displaying a sequential number to the user, why does it need to be stored in the database at all? When your PHP code is iterating over the results, simply add the number there.

$sequence_number = 1;
while ($row = mysql_fetch_assoc($results)) {
    // ... Display everything ...
    $sequence_number++;
}

Upvotes: 2

Related Questions