Question 20480 in Mysql Question posted in
The official MySQL documentation can be found here.

Selecting ALL records when condition is met for ALL – Mysql

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?

Tags:

2

Answers


  1. 0

    You want recipes that your user has all ingredients for, which reads like a relational division problem.

    Starting from the tables and columns names of your original query, here is a query that gives you the recipe ids:

    select ri.recipe_id
from recipe_ingredient ri 
inner join pantry p on p.ingredient_id = ri.ingredient_id
where p.iduser = ?
group by ri.recipe_id
having count(*) = ( 
    select count(*) 
    from recipe_ingredient ri1 
    where ri1.recipe_id = r.recipe_id
)

    The idea is to filter the recipe ingredient table for ingredients that the user has, and then ensure that none misses in the same recipe, using the having clause.

    Side note: do not concatenate parameters as strings in your query, as it opens up your application to SQL injection; using bind parameters instead.

    Login or Signup to reply.
  2. 0

    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)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search