Clever Geek Handbook

How do I find missing data in an array or mySQL table?

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 concern about sharing a simple function or idea on how to do this?

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

Thanks James Hartig http://twittertrend.net

0


source to share


3 answers


I had a similar requirement and wrote a function that will return a list of missing identifiers.



---------------------------
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
+1


source


Do you mean sequential identifiers?

In this 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
+1


source


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

However, this worked:

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


source






More articles:


All Articles