Exoon Posted May 4, 2011 Share Posted May 4, 2011 Hi, I have a list of 5000 ID's but there are about 150 missing i want to find out what ones are missing, How can i do with this PHP? Thanks Quote Link to comment Share on other sites More sharing options...
Maq Posted May 4, 2011 Share Posted May 4, 2011 Compare it to the list of all the IDs. Can you provide sample data? Quote Link to comment Share on other sites More sharing options...
Exoon Posted May 4, 2011 Author Share Posted May 4, 2011 Hi, I just have a database with numbers 1-5000 but when i was adding them i missed a few out and cant figure out which ones for example 4255 might be missing. I can export the database but it will just be numbers from 1 ending in 5000. Quote Link to comment Share on other sites More sharing options...
tomtimms Posted May 4, 2011 Share Posted May 4, 2011 Why not just get all your current db numbers into an array, and then create an array of 1-5000 numbers and just find the difference? http://php.net/manual/en/function.array-diff.php Quote Link to comment Share on other sites More sharing options...
QuickOldCar Posted May 4, 2011 Share Posted May 4, 2011 You can always export then import them back in to eliminate any empty id's you had. If using auto_increment. Quote Link to comment Share on other sites More sharing options...
silkfire Posted May 4, 2011 Share Posted May 4, 2011 Run one of these queries mate, assuming your id column is named 'id', also change table_name to the name of your table: SELECT a.id + 1 AS 'missing' FROM users_test AS a WHERE NOT EXISTS (SELECT b.id FROM table_name AS b WHERE a.id + 1 = b.id) AND id < (SELECT MAX(id) FROM table_name) or SELECT l.id +1 AS START , MIN( fr.id ) -1 AS STOP FROM table_name AS l LEFT OUTER JOIN table_name AS r ON l.id = r.id -1 LEFT OUTER JOIN table_name AS fr ON l.id < fr.id WHERE r.id IS NULL AND fr.id IS NOT NULL GROUP BY l.id, r.id LIMIT 0 , 30 Quote Link to comment 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.