Reputation: 1009
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
Upvotes: 0
Views: 1552
Reputation: 3823
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
Upvotes: 1
Reputation: 1009
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
Upvotes: 1
Reputation: 78518
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
Upvotes: 1