Re-sorting table order after deleting some entries?

[Chappers]

Hi all,

I've got a MySQL database set up in which I have a field set as primary key and named ID, and auto_increment. As I display the elements sorted by ID order, I have the number of each entry in the table in numerical order going down the page, just as I want. But, if I delete any of the entries in the table, the numbers set for each ID remain static, so I might end up with:

ID: 1 Name: blah
ID: 3 Name: blof
ID: 4 Name: blag

where ID: 2 is missing because it's been deleted. Is there a way I can make the table re-sort itself so after deleting the above entry ID:2, the remaining table would be:

ID: 1 Name: blah
ID: 2 Name: blof
ID: 3 Name: blag

Thanks,
James

[artacus]

Yes, but don't use your autoinc primary key like this. Use another field (I usually have a col named "sort_order")

[Chappers]

Could you explain how please? I'm still learning and not finding it terribly easy.

[fenway]

Add a column and store a numeric value for the desired sort order in that column -- don't try and exploit the PK.