Selecting ALL records when condition is met for ALL

Question

I'm having a bit of trouble with getting a select statement to work correctly.

I have the following tables:

recipe	recipe_ingredient	pantry
recipe1	ingredient1	Ingredient1
recipe1	ingredient2	Ingredient2
recipe2	ingredient3

I want a select statement that returns recipes when the user's pantry has all ingredients for that recipe.

if ($res = $mysqli->query('
SELECT * 
FROM recipe 
WHERE NOT EXISTS (
    SELECT recipe_id 
    FROM recipe_ingredient 
    WHERE NOT EXISTS (
        SELECT ingredient_id 
        FROM pantry 
        WHERE recipe_ingredient.ingredient_id = pantry.ingredient_id 
          AND iduser = ' . $_COOKIE["idUser"] . '
    )
)
')) {

My code works when only one recipe in the db but when muliple recipes are added the code no longer works. Using the above table as an example it should return recipe1 but it returns nothing unless the pantry has ingredient3 then both recipes are returned.

How do I amend my select statement to fix this?

Answer 1

you can do following correlated subquery with exists, which will test if all ingredients are filled up, which woul be when all recipe_ingredient ingridents_id have a value that is Not NULL

But you should be aware that your code is vulnerable to sql injection and so you should only use prepared statements with parameters see How can I prevent SQL injection in PHP? for more information

SELECT * from recipe r
WHERE NOT EXISTS (SELECT 
                    1 
                FROM recipe_ingredient rp LEFT JOIN pantry ON rp.ingredient_id 
                = p.ingredient_id 
                AND p.iduser= ? 
                WHERE rp.recipe_id = r.recipe_id AND p.ingredient_id IS NULL)