3

What i'm looking for, if its even possible, is a SQL Query that replaces this method so i don't have to do a query for each value in the array:

$array = array(1,2,3);
foreach ($array as $product) {
            $stmt = $dbh->prepare("SELECT EXISTS(SELECT 1 FROM products WHERE product_id = :value LIMIT 1)");
            $stmt->bindParam(':value', $product);
            $stmt->execute();
            if($row = $stmt->fetch())
            {
                ...
            }
        }

Syntax can be wrong but something like this (basically is to return the products id from the array that doesn't have a record in the table):

$products = array(1,2,3);
$array = join(',', array_fill(0, count($products), '?'));   
SELECT product_id WHERE product_id IN ($array) NOT EXISTS (SELECT 1 FROM products WHERE product_id IN ($array))

Is there a way to get this or i still need to use the loop?

Improve this question
4
  • Where does the $array came from? It is from an input or another sql ? Commented Aug 13, 2015 at 2:07
  • @JorgeCampos Edited :) Commented Aug 13, 2015 at 2:16
  • Well, and where does $products came from ? Commented Aug 13, 2015 at 2:21
  • @JorgeCampos forgot that one sorry :) Commented Aug 13, 2015 at 2:23

2 Answers 2

Reset to default
1

It sounds like you’re looking for an anti-join. If you can insert $products into a temp table, you can use not exists to get product ids that are not in the table:

select * from temp_product_ids t
where not exists (
    select 1 from products p
    where p.id = t.product_id
)

Another approach is to get all the product ids that do exist

select id from products where id in ( $my_products )

and to use array_diff to see which values are missing

$missing_products = array_diff($my_products,$database_products);
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

It looks like you could use the "in" query to solve this question. You would need to turn your array into a comma-delimited list (we'll call it productString), which would make your SQL statement look like:

"SELECT EXISTS(SELECT 1 FROM products WHERE product_id in (:productString))"

where product string looks like "product_id_1, product_id_2, product_id_7, etc." This would give you one query with a variable resultSet based on the list of product_id's which you provided.

Improve this answer

Comments