PHP/MySQL 中的食谱查找器允许按成分过滤

发布于 2024-12-06 05:25:07 字数 531 浏览 5 评论 0原文

我有一个 MySQL 数据库，如下所示：

Recipes (rid, name)
Ingredients (iid, name)
Relationship (rid, iid)

我有一个前端网页，它在网格中显示成分。当用户单击第一个成分时，将返回包含该成分的所有食谱。

第二次单击时，食谱将被过滤为仅包含第一种成分，以及这个新成分。第三次单击时，将应用相同的过滤系统。

想象一下以下场景（即使数据库看起来不像下面这样）

食谱 (1,2,3,4,5,6) 和成分 (A,B,C,D,E,F)

1 ABC

2 CDF

3 ABE

4 ADE

5 BCE

6 DEF

第一次点击：(A) 返回 --> 1, 3, 4

第二次点击：(B) 返回 --> 1、3

第三次点击：(C) 返回 --> 1

我如何使用 PHP 和 MySQL 来做到这一点？

原文

I have a MySQL database which looks like this:

Recipes (rid, name)
Ingredients (iid, name)
Relationship (rid, iid)

And I have a front end web page which displays the ingredients in a grid. When a user clicks on the first ingredient, all recipes which contain that ingredient are returned.

On the second click, the recipes are filtered to include only the first ingredient, and this new one. On the third click, the same filter system applies.

Imagine the following scenario (even though the database doesn't look like below)

Recipes (1,2,3,4,5,6) and Ingredients (A,B,C,D,E,F)

1 A B C

2 C D F

3 A B E

4 A D E

5 B C E

6 D E F

First Click: (A) returns --> 1, 3, 4

Second Click: (B) returns --> 1, 3

Third Click: (C) returns --> 1

How would I do this using PHP and MySQL?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

怎会甘心 2024-12-13 05:25:07

SELECT re.* 
FROM recipes re
WHERE 
(
      SELECT COUNT(1) 
      FROM relationships sh 
      WHERE 
            sh.rid = re.rid 
            AND sh.iid IN (A, B, C)
) >= 3

这个解决方案对我有用。然而，我正在开发的应用程序的性质需要存储数百甚至数千个食谱才能正常运行，即。而不返回零结果。这是一个相当简单的概念，但要正确执行却很困难。

SELECT re.* 
FROM recipes re
WHERE 
(
      SELECT COUNT(1) 
      FROM relationships sh 
      WHERE 
            sh.rid = re.rid 
            AND sh.iid IN (A, B, C)
) >= 3

This solution works for me. However the nature of the application I am developing requires hundreds if not thousands of recipes to be stored for it to run nicely, ie. without returning zero results. Quite a simple concept, but tricky to get right.

回复收藏 0 原文