Search for number lost in array

[mart94]

Hi!

 

I need a little bit coding help here.

I have a table:

Numbers, Active

 

Under numbers can be 10000, 10001, 10002, 10003, 10004, 10005 and active can be 1 or 0, depends on is this row active or not.

Now, i need to check, is there any numbers lost, i mean, (for example) in table i have numbers 10002, 10000 and 10005 Can somebody make script, what finds those lost numbers, in this case those are: 10001, 10003 and 10004.

 

All the best:

Mart

[shlumph]

Someone can definitely make script for this. But we would rather help walk you through it. Do you have the set of possible values (10000, 10001, 10002, 10003, 10004, 10005, etc.) in the database?

[mart94]

Someone can definitely make script for this. But we would rather help walk you through it. Do you have the set of possible values (10000, 10001, 10002, 10003, 10004, 10005, etc.) in the database?

Well, the numbers can overlap, i mean, under numbers can be 10000 like 2 or more times, script reads that "active" row also, and only numbers with active set "1" must be different (10000, 10001, 10002, 10003, 10004, 10005)

 

Best regards:

Mart

[shlumph]

I'd start thinking about how to do this in such a way that the database does all the calculations, then. Start thinking venn diagram:

 

 

Think about the numbers you want and don't want as two sets. Then look into MySQL subselect statements (if that's the DB you are using). http://dev.mysql.com/doc/refman/5.0/en/subqueries.html

 

Hope this helps.

[shlumph]

In the above diagram, if you have AUB in the database already, and you're fetching back A, then B\A would be the numbers your looking for

[mart94]

Well, Thanks for trying to help, but i figured it out myself 

 

Did little bit research about arrays, and came up with this:

 

$query0 = mysql_query("Select * From numbers Where active = '1'");

$numbers = array("10000", "10001", "10002", "10004", "10005", "10006");

$numbers_used = array();
while($query1_result = mysql_fetch_assoc($query0)){
$numbers_used[] = $query1_result['number'];
}

$usable_numbers = array_diff($numbers, $numbers_used);
sort($usable_numbers);

echo $usable_numbers[0]; //Testing purposes only, echos  out first number in array

[shlumph]

That's one way to do it

 

If your $numbers array changes depending on what's in the DB, you might run into a problem, though. You could always fetch the $numbers array from the DB if this is the case. But then again, if the array grows to hundreds of numbers you may run into a performance problem. Just something to keep in mind

[Psycho]

No offense to shlumph, but I think in his attempt to impart the logic for solving such a problem it became overgeneralized.

 

My take: simply query the min and max from the database. Then create an array of all possible values from the min to max. Next, do a second query to find all the used values. Then simply remove the used values from the list of total possible values.

 

Sample code

//Query db for min/max and create array of total possible
$query = "SELECT MIN(number) as min, MAX(number) as MAX
          FROM TABLE
          GROUP BY number";
$result = mysql_query($query);
$record = mysql_fetch_assoc($result);
$all_numbers = range($record['min'], $record['max']);

//Query db for all used values and create array of used array
$query = "SELECT number
          FROM TABLE";
$result = mysql_query($query);
$used_numbers = array();
while($record = mysql_fetch_assoc($result))
{
    $used_numbers[] = $record['number'];
}

//Determine unused values
$unused_numbers = array_diff($all_numbers, $used_numbers);

 

If you have a VERY large number of records and/or a large possible range the performance could be affected.

 

Alternatively you could just query all the records sorted low to high and iterrate through each one to see if the next value is the incremented by 1 from the previous and determine the gaps that way. Not sure which would be more efficient.

[shlumph]

Heh, of course no offense taken 

 

I like to calculate sets of data on the DB. Finding and sorting numbers in PHP is an expensive process. Perhaps something similar like this:

<?php
//Pseudo query
$sql = "SELECT DISTINCT number FROM all_numbers_available WHERE number NOT IN (SELECT DISTINCT number FROM custom_numbers WHERE active = 1)";
$rs = mysql_query($sql);

while($row = mysql_fetch_array($rs))
{
    echo $row['number'];
}

[Psycho]

I find database operations more expensive than PHP operations. But, no matter.

 

In the code you just posted, where are you getting the table "all_numbers_available" from? If the OP is dealing with a fixed set of number that will never change I guess you could pre-populate the table. But, if the range of numbers is constantly changing you would have to create a process that exists somewhere to conastantly update the "all_numbers_available" table.

 

Personally, I would like to know the purpose and frequency for this process as that would detemine how much effort should be made to make this as efficient as possible.

[shlumph]

In the code you just posted, where are you getting the table "all_numbers_available" from?

 

I asked the OP if he had these values in my first response, but I didn't quite understand his answer. I didn't want to assume the available numbers are in sequential order. It could be that he just wanted something like this:

SELECT BETWEEN :min AND :max AS number WHERE number NOT IN (SELECT FROM numbers WHERE active = 1)

 

Or maybe even something like this:

SELECT * FROM numbers WHERE active = 0

 

But, it's kinda hard to pinpoint what he wants from the information given. Hope this helps.