CODEWITHSUNDEEP
phpmysqldatabaserecipe
digiwig
digiwig

Reputation: 621

Recipe finder in PHP/MySQL that allows filtering by ingredients

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?

Upvotes: 0

Views: 534

Answers (2)

Shameer
Shameer

Reputation: 3066

It's quite simple that you need to return the rid where rid in (list of rids in relations where iid= given iid).

Upvotes: 0

digiwig
digiwig

Reputation: 621

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.

Upvotes: 1

Related Questions