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 — C3roe
– C3roe, Commented Mar 10, 2020 at 7:55

Nigel Ren · Accepted Answer · 2020-03-10 08:00:45Z

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 )

u_mulder · Accepted Answer · 2020-03-10 08:01:03Z

$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));

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related