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.
You should design your table differently:
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')