Array of Values from SQL showing which exist and which don't

Question

I couldn't think of a title, I know it's not good.

Basically, I'm going to have an array of values POSTed to me. These values will be integers.

So, let's say it will be 1,2,3,4,5 etc.. I've already figured out how to get their respective values from the database like such

  $values = explode(",", $_GET['id']);
  $placeholders = str_repeat('?, ', count($values) - 1) . '?';
  $CheckQuery = $database->prepare("SELECT * FROM users WHERE the_id IN($placeholders)");
  $CheckQuery->execute($values);
  $Res = $CheckQuery->fetchAll(PDO::FETCH_ASSOC);

Now, this is great because given the IDs I want to be able to return:

ID1:0or1
ID2:0or1

I'm stuck trying to figure out how to return the IDs which do not exist in the database though. Any help here?

Does this answer your question? [SQL: find missing IDs in a table](https://stackoverflow.com/questions/1389605/sql-find-missing-ids-in-a-table) — CBroe, Mar 10 '20 at 07:55

score 0 · Accepted Answer · answered Mar 10 '20 at 08:00

If you want 1 or 0, you can use the results of the $values array and the data from the database $Res, create arrays keyed on these lists (1's for $Res and 0's for $values) then overwrite the 0's with the 1's found in the database...

$present = array_fill_keys(array_column($Res, 'the_id'), 1);
$allValues = array_fill_keys($values, 0);
$result = array_replace($allValues, $present);

with some test data...

$_GET['id'] = '1,2,3';
$values = explode(",", $_GET['id']);

$Res = [ ['the_id' => 1], ['the_id' => 3]];

$present = array_fill_keys(array_column($Res, 'the_id'), 1);
$allValues = array_fill_keys($values, 0);
$result = array_replace($allValues, $present);

print_r($result);

you get

Array
(
    [1] => 1
    [2] => 0
    [3] => 1
)

score 0 · Answer 2 · answered Mar 10 '20 at 08:01

$values = explode(",", $_GET['id']);
$placeholders = str_repeat('?, ', count($values) - 1) . '?';
// select `the_id` as you don't need other fields in select
$CheckQuery = $database->prepare("SELECT the_id FROM users WHERE the_id IN($placeholders)");
$CheckQuery->execute($values);

// Use `FETCH_COLUMN` to fetch ids as array:
$ids = $CheckQuery->fetchAll(PDO::FETCH_COLUMN, 0);
// Now you can use `array_diff` to get ids 
// that are in `$values` and not in `$ids`
print_r(array_diff($values, $ids));

Array of Values from SQL showing which exist and which don't

2 Answers2