A community in which webmasters can ask for help with topics such as PHP coding , MySQL , IT jobs, web design, IT security.
Current location:homephp forumphp talk in 2008 yearHow to find missing data either in array or in mySQL table? - page 1
User InfoPosts
How to find missing data either in array or in mySQL table?#1
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

posted date: 2008-12-12 18:02:00


Re: How to find missing data either in array or in mySQL table?#2
I had made out the solution of this problem. click to view my topic...

hope that hepls.

posted date: 2008-12-12 18:02:01


Re: How to find missing data either in array or in mySQL table?#3
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

posted date: 2008-12-12 18:09:00


Re: How to find missing data either in array or in mySQL table?#4
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

posted date: 2008-12-12 18:28:00


Re: How to find missing data either in array or in mySQL table?#5
your sql statement makes no sense? It would just return all the ids between the max and min? It wouldn't find the missing values.

posted date: 2008-12-12 18:29:00


Re: How to find missing data either in array or in mySQL table?#6
How can you 'find' missing values... he retrieves the existing values and subtract those from the full range. should work

posted date: 2008-12-12 18:33:00


Re: How to find missing data either in array or in mySQL table?#7
The sql query finds all values in the database that are in between the high and low key. That's what I understood your question to mean. If you want to find the keys that are not in the database, I think all solutions would involve a temporary table (implicit or explicit) and a join.

posted date: 2008-12-13 04:45:00


Re: How to find missing data either in array or in mySQL table?#8
You could just use array_values for his solution. You didn't specify that keys should be reset, just a list of missing id's. But this solution is like Id make it myself.

posted date: 2008-12-15 07:10:00


Re: How to find missing data either in array or in mySQL table?#9
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)asbegin 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 returnend

posted date: 2010-02-22 15:03:00


select page: « 1 »
Copyright ©2008-2017 www.momige.com, all rights reserved.