PHP & MySQL & forms -- flexible search? (ex: X ingredients can make Y recipes)

Question

I'm just getting started with PHP/MySQL, so please forgive the rather simplistic question:

As a practice example, I want to create a small dbase of recipes. Each recipe has X different ingredients. The table is simple enough I think - each row represents a recipe and each column after the first(primary key) is an ingredient with a TRUE/FALSE value (needs/doesn't need).

But how do I craft a checkbox-based form that can search that table such that if I check 5 different ingredients (carrots, pork, rice, butter, potatoes) the query will return every recipe that I can make with only those 5 ingredients?

So a recipe that uses those 5 and something else WOULD NOT be returned. But a recipe that uses only 4 of those 5 WOULD be returned.

With my limited knowledge, the query string I'm envisioning seems crammed with IFs and awfully unwieldy... and that's only for a few ingredients, what if I had over a hundred? Yikes.

Thank you for any insight you can provide towards helping me on my way towards PHP/MySQL literacy!

Answer 1

You should design your table differently:

• Recipes - ID,Name
• Ingredients - ID, name

• RecipesIngredients - recipeID, ingredientID,amount

  Recipes
  ID name
  1 scalloped potato
  2 cheesy carrots
  3 saucy potato
  
  
  Ingredients
  ID name
  1 potato
  2 sauce
  3 cheese
  4 carrots
  
  
  RecipesIngredients
  recipesID ingredientID    amount
  1          1               5 lbs
  1          2               2 cups
  1          3               1 cup
  2          3               1/2 cup
  2          4               2 whole
  3          2               a smidge
  3          1               1 whole red
  

To get all recipes that have cheese and potato:

SELECT * from recipes r 
INNER JOIN RecipesIngredients ri on r.id=ri.recipeID
INNER JOIN Ingredients i on i.id = ri.ingredientID
WHERE i.name in ('cheese','potato')