PHP MySQL recipe search based on what ingredients are provided

Question

Say I have a database of recipes, and I want to search through them based on what ingredients I have.

There should be 3 tables:

Recipes (rid, rname),
Ingredients (iid, iname),
Relationship (rid, iid)

Next, lets imagine I have a recipe for 'toast' and a recipe for 'bread and butter pudding' - Toast has 2 ingredients (bread and butter) - The pudding might have bread and butter, plus flour, eggs and water - so thats 5 in total.

My problem is, structuring an SQL query based on what ingredients are provided in a search. If I submit 3 ingredients in my search - bread, butter and eggs - then (from the 2 recipes discussed) only one result should be produced - Toast! - because there aren't enough ingredients to make the pudding!

So what does such a sql query actually look like? I have tried everything, googled everything and now my brain cannot deal with it anymore.

score 1 · Accepted Answer · answered Sep 01 '11 at 21:43

Don't know if this is the best way, but for the example you gave it will work:

SELECT
    *
FROM
    recipes
WHERE
    rid NOT IN (
        /* exclude all recipes with other ingredients */
        SELECT rid FROM relationship WHERE iid NOT IN (1, 2, 4)
    )

Sorry for my english =)

Edit: the ingredients' ids are in the same order that they're in your question, so 1 is Bread, 2 is Butter, 4 is Eggs:

"iid";"iname" "1";"Bread" "2";"Butter" "3";"Flour" "4";"Eggs" "5";"Water"

score 0 · Answer 2 · answered Sep 01 '11 at 21:34

0

Presuming the list of iids is given (1,2,3), you might try selecting all from relationship where the iid is in (1,2,3) and the rid is in (the selection of rids in relationship having a count<=number of iids in list)

answered Sep 01 '11 at 21:34

horatio

1,426
8
7

score 0 · Answer 3 · answered Sep 01 '11 at 21:35

0

Suppose 'ids' holds searching ingredients ids.

SELECT *
FROM Recipes
WHREE rid NOT IN (SELECT DISTINCT rid FROM Relationship WHERE iid NOT IN (ids))

answered Sep 01 '11 at 21:35

Richard Zhang

350
1
4

JM4 · Answer 4 · 2011-09-01T22:10:53.113

My answer assumes you are using PDO but you modify to your needs if using basic mysql or mysqli calls:

$ingredient1 = $_POST['ingredient1'];
$ingredient2 = $_POST['ingredient2'];
$ingredient3 = $_POST['ingredient3'];

$sql = '
SELECT
 c.rid,
 c.rname
FROM ingredients a
JOIN relationship b ON (a.iid = b.iid)
JOIN recipes c ON (b.rid = c.rid)
WHERE
     (a.iname LIKE ? AND a.iname LIKE ? AND a.iname LIKE ?)
';

$stmt = $db->prepare($sql)
$stmt->execute(array('%'.$ingredient1.'%', '%'.$ingredient2.'%', '%'.$ingredient3.'%'));

My answer assumes of course your statement will always include three recipes and you are using post variables but can be modified to accomodate variability as well. The benefits of this type of query as well contrasted to others given is that you don't have to use subqueries, which are traditionally less efficient.

edit

forgot to add '%' symbols in the array values. This will match any ingredient name you have in the corresponding table. If it must match, simply remove 'LIKE' and '%' symbols and place '= ?'.

PHP MySQL recipe search based on what ingredients are provided

4 Answers4

edit