The recipe db continues...
categories
cid | category_name
1 | desserts
2 | cakes
3 | biscuits
recipes
id | recipe_name
1 | black forest cake
2 | angel cake
3 | melting moments
4 | croquembouche
5 | crepes suzette
ingredients
iid | ingredient_code | ingredient_name | ingredient_brand
1 | abc201 | self-raising flour | white wings
2 | abc202 | milk | pura
3 | abc203 | chocolate | plaistock
4 | abc204 | baking powder | mackenzie
5 | abc205 | plain flour | white wings
6 | abc206 | eggs | free range
recipe_categories
recipe_id | category_id
1 | 1
4 | 1
5 | 1
1 | 2
2 | 2
3 | 3
4 | 3
recipe_ingredients
recipe_id | ingredient_id
1 | 1
2 | 1
4 | 1
1 | 2
2 | 2
3 | 2
5 | 2
1 | 3
2 | 3
1 | 4
3 | 5
4 | 5
As suggested by ozatomic, I'm using the following query:
SELECT A.recipe_name, GROUP_CONCAT(ingredient_name) AS ingredient_names
FROM recipes A
LEFT JOIN recipe_ingredients B ON A.id = B.recipe_id
LEFT JOIN ingredients C ON B.ingredient_id = C.iid
LEFT JOIN recipe_categories D ON A.id = D.recipe_id
LEFT JOIN categories E ON D.category_id = E.cid
WHERE category_id = <search_id>
GROUP BY id
Problem is, I've realised I'm going to need additional fields in the ingredients table, such as ingredient_code and ingredient_brand, which will mean that I'm unable to use GROUP_CONCAT to list ingredients as I need the following outputted to web (still by category):
black forest cake:
abc201 white wings self-raising flour
abc202 pura milk
abc203 plaistock chocolate
croquembouche:
abc201 white wings self-raising flour
abc204 white wings plain flour
crepes suzette:
abc202 pura milk
abc205 white wings plain flour
abc206 free range eggs
I get the feeling that I'm either going to have to use two queries to achieve this, or use a foreach loop. Which would be best and how would I go about it?
edit: I also need to be able to format the results, using html tags or putting in divs or whatever.
