CODEWITHSUNDEEP
phpmysql
Harrika
Harrika

Reputation: 11

PHP How to query missing values between two numbers?

Hello i have mysql table which named numbers like this;

ID Number
1 3002
2 3004
2000 7545

When i need to insert a new data products table i have to find first number which is not on this table between(3000 to 35000). I mean i need to find first number between this numbers not on numbers table. How can i find it?

$statement = $con->prepare("SELECT * FROM numbers");
$statement->execute();

$result = $statement->fetchAll(PDO::FETCH_ASSOC);
foreach($result as $row)

{
    $arr1[] = $row['numbers'];

}
$arr2 = range(3000,35000);                                                    
$missing_numbers = array_diff($arr2,$arr1);
print_r($missing_numbers);

i tried array_diff but i gives results with different keys. When i write $missing_numbers[0] i want to see first missing number.

Upvotes: 1

Views: 130

Answers (4)

Adamszsz
Adamszsz

Reputation: 581

IF you want do it only by using MySQL query and later get the result in PHP you need to do something like this :

Create temporray table with your example :

create table test123(id integer, Number varchar(100))
        #insert into test123 (ID, Number) values (1, '3002');
        #insert into test123 (ID, Number) values (2, '3004');
        #insert into test123 (ID, Number) values (2000, '7545');

CREATE TEMPORARY TABLE WITH RANGE 3000 - 35000

CREATE TEMPORARY TABLE IF NOT EXISTS tableTest AS (
        
SELECT @row := @row + 1 AS row FROM 
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2, 
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3, 
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4, 
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t5,
(SELECT @row:=3000) numbers WHERE @row  < 35000

)

AND FINALLY you can just select numbers that's not inside your table with data

select * from tableTest t where t.row not in ( select Number from test123)

The output will be select all numbers as you wanted. enter image description here

Upvotes: 0

PlayerKillerYKT
PlayerKillerYKT

Reputation: 294

Easy, just need reset keys after array_diff:

...
$missing_numbers = array_values(missing_numbers);
print_r($missing_numbers);

Upvotes: 0

Sibi Kandathil
Sibi Kandathil

Reputation: 126

I think your solution will work only thing missing is reordering of the indexes which can be done by using the array_values() function. Update your last lines of code as -

$missing_numbers = array_values(array_diff($arr2,$arr1));
print_r($missing_numbers);

so on printing $missing_numbers[0] you should get the first missing number.

Upvotes: 0

Nigel Ren
Nigel Ren

Reputation: 57121

If you just want the first missing number, it may be easier to read the rows in number order and check for the first one which isn't in the sequence you are after. So a query (with the start and end points as parameters) order by the number and a counter which is what it's expecting to get on each row...

$startPoint = 3000;
$endPoint = 35000;
$statement = $db->prepare("SELECT number
        FROM numbers
        WHERE number >= :start
            and number <= :end
        order by number");
$statement->execute([
        'start' => $startPoint,
        'end' => $endPoint
]);
$expected = $startPoint;
while ($row = $statement->fetch())  {
    if ( $row['number'] != $expected )  {
        echo "Missing=" . $expected;
        break;
    }
    $expected++;
}

Upvotes: 1

Related Questions