ZeroError Posted November 30, 2009 Share Posted November 30, 2009 Alright, so I have a table with 26 entries in it (id, filename, caption) for my image randomiser (http://zeroerror.co.uk/community_img.php) which is working fine, technically, but I have a slight problem. The primary key (id) is set to AUTO_INCREMENT and I have recently deleted a row, so it counts 1, 2, 3, 4, 5, 6, 8, 9, etc. I'd like to know the easiest method of fixing this, which I can build into the deletion script I have written for the administration panel. Any thoughts? Thanks in advance. Quote Link to comment https://forums.phpfreaks.com/topic/183404-mysql-primary-key-gap/ Share on other sites More sharing options...
JonnoTheDev Posted November 30, 2009 Share Posted November 30, 2009 Why should this matter? Quote Link to comment https://forums.phpfreaks.com/topic/183404-mysql-primary-key-gap/#findComment-968069 Share on other sites More sharing options...
ZeroError Posted November 30, 2009 Author Share Posted November 30, 2009 Ah yes, sorry, well the PHP script which finds a random filename from the table counts the number of rows then picks an id between 1 and the number of rows, meaning if I my last entry has an ID of 27, it will be left off, as the randomiser will only count 26 rows. It also looks untidy. Quote Link to comment https://forums.phpfreaks.com/topic/183404-mysql-primary-key-gap/#findComment-968071 Share on other sites More sharing options...
oni-kun Posted November 30, 2009 Share Posted November 30, 2009 Ah yes, sorry, well the PHP script which finds a random filename from the table counts the number of rows then picks an id between 1 and the number of rows, meaning if I my last entry has an ID of 27, it will be left off, as the randomiser will only count 26 rows. It also looks untidy. With your random selection, check if the ID exists before calling it? Seems very simple, as those deleted entries, nothing you can do with them. Quote Link to comment https://forums.phpfreaks.com/topic/183404-mysql-primary-key-gap/#findComment-968075 Share on other sites More sharing options...
Alex Posted November 30, 2009 Share Posted November 30, 2009 This isn't really a problem with the ID skipping. If every time you deleted a row all the unique ids shifted down that would kind of defeat the purpose, wouldn't it? It wouldn't be a constant anymore. If this gap is giving you a problem then it's likely it's a design flaw in your program. If you want to select a random record from a database you can use ORDER BY RAND() LIMIT 1. This doesn't scale well, so if you're dealing with a large database you might want to look into some of the alternatives, there's a sticky for this in the MySQL forum. Quote Link to comment https://forums.phpfreaks.com/topic/183404-mysql-primary-key-gap/#findComment-968077 Share on other sites More sharing options...
JonnoTheDev Posted November 30, 2009 Share Posted November 30, 2009 This is poor script design, nothing to do with the database. A primary key is simply a unique identifier and not to be changed. It is used to construct relationships with other tables meaning if a PK where to change all associated records in union tables would have to be updated. If you have hundreds of tables with millions of records then can you imagine the level of additional queries that you have to implement into a system making it massively error prone. Quote Link to comment https://forums.phpfreaks.com/topic/183404-mysql-primary-key-gap/#findComment-968078 Share on other sites More sharing options...
ZeroError Posted November 30, 2009 Author Share Posted November 30, 2009 OK, thank you. My website isn't amazingly popular, I doubt that will cause much of a problem. I'll check out that sticky, anyway. Quote Link to comment https://forums.phpfreaks.com/topic/183404-mysql-primary-key-gap/#findComment-968079 Share on other sites More sharing options...
Mchl Posted November 30, 2009 Share Posted November 30, 2009 I have found an interesting solution here: http://net.tutsplus.com/tutorials/other/top-20-mysql-best-practices/ (section 6) Quote Link to comment https://forums.phpfreaks.com/topic/183404-mysql-primary-key-gap/#findComment-968086 Share on other sites More sharing options...
ZeroError Posted November 30, 2009 Author Share Posted November 30, 2009 Thanks, Mchl, that saves me having problems when it gets bigger. Very nice article, too! Problem solved, methinks. Quote Link to comment https://forums.phpfreaks.com/topic/183404-mysql-primary-key-gap/#findComment-968089 Share on other sites More sharing options...
Mchl Posted November 30, 2009 Share Posted November 30, 2009 There's a green button on the left side at the bottom to mark topic as solved. Quote Link to comment https://forums.phpfreaks.com/topic/183404-mysql-primary-key-gap/#findComment-968096 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.