How to find missing data either in array or in mySQL table?

Question

Hello, I have an array filled with values (twitter ids) and I would like to find the missing data between the lowest id and the highest id? Any care to share a simple function or idea on how to do this?

Also, I was wondering if I can do the same with mySQL? I have the key indexed. The table contains 250k rows right now, so a temporary table and then a join wouldn't be very fast or efficient. I could do a PHP loop to loop through the data, but that would also take a long time, and a great deal of memory. Is there a specific mysql query I can run? or can I somehow use the function from above with this?

Thanks, James Hartig http://twittertrend.net

Answer 1

Do you mean sequential ID's?

In that case

$new_ids = range($lowid, $highid, 1);
$ids = array_merge($ids, $new_ids);
$ids = array_unique($ids);
sort($ids);

And in SQL (with placeholders)

SELECT key, other_data from `table` WHERE key > :low_id AND key < :high_id

Answer 2

Your range() gave me a good idea, your code didn't work as unique preserves unique keys, so I was just left with the range functions result.

However, this worked:

$diff = array_values(array_diff(range(min($array), max($array), 1), $array)); //returns array of incomplete values

Answer 3

I had a similar requirement and wrote a function that would return a list of missing IDs.

---------------------------
create function dbo.FreeIDs ()
---------------------------
returns @tbl table (FreeID int)

as
begin

    declare @Max int
    declare @i int

    select @Max = MAX(ID) from [TheTable]
    set @i = 0

    while @i < @Max begin
          set @i = @i + 1
          if not exists (select * from [TheTable] where ID = @i) 
             insert into @tbl select @i
    end

    return

end