PHP MySQL配方搜索基于提供的成分

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:

应该有3个表:

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

食谱(摆脱,rname),成分(iid,iname),关系(摆脱,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.

接下来,让我们想象一下我有“吐司”的食谱和“面包和黄油布丁”的食谱 - 吐司有2种成分(面包和黄油) - 布丁可能有面包和黄油,还有面粉,鸡蛋和水 - 所以这就是总共5个。

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!

我的问题是,根据搜索中提供的成分构建SQL查询。如果我在我的搜索中提交3种成分 - 面包,黄油和鸡蛋 - 那么(从讨论的2个食谱中)只应生成一个结果 - 吐司! - 因为没有足够的成分来制作布丁!

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.

那么这样的SQL查询实际上是什么样的呢?我已经尝试了所有东西,谷歌搜索了一切,现在我的大脑再也无法应对了。

4 个解决方案

#1

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:

编辑:成分的ID与你问题中的顺序相同,所以1是面包,2是黄油,4是鸡蛋:

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

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

#2

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)

假设给出了iid列表(1,2,3),你可以尝试从iid所在的关系中选择所有(1,2,3)并且rid在(选择关系中的rids有一个count < =列表中的iids数)

#3

Suppose 'ids' holds searching ingredients ids.

假设'ids'持有搜索成分ID。

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

#4

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

我的回答是假设您正在使用PDO,但如果使用基本的mysql或mysqli调用,则可以根据需要进行修改:

$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 '= ?'.

忘了在数组值中添加'%'符号。这将匹配您在相应表格中的任何成分名称。如果必须匹配,只需删除'LIKE'和'%'符号并放置'=?'。