How can i find missing numbers?

[Exoon]

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

[Maq]

Compare it to the list of all the IDs.  Can you provide sample data?

[Exoon]

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.

[tomtimms]

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

 

[QuickOldCar]

You can always export then import them back in to eliminate any empty id's you had.

 

If using auto_increment.

[silkfire]

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