views:

35

answers:

1

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?

MTIA!

edit: I also need to be able to format the results, using html tags or putting in divs or whatever.

A: 

The answer I gave when you asked this yesterday still applies. :)

You are indeed going to have to put together a list and loop over it to pull out the exact data you're looking for.

Charles
Thanks Charles :-) But when I get to the step before "With me so far?", the output is recipe_name repeated 3 times, so I end up with:black forest cakeblack forest cakecrepes suzettecrepes suzettecrepes suzettecroquembouchecroquembouchecroquembouche
circey
Also, I'm not familiar with PDO so was hoping to avoid more mental strain!
circey
SQL deals with rows and columns. Each recipe has multiple rows of ingredients. When joining together the tables, certain columns will repeat if there is more than one matching row in a later table. This is why I manually grouped together recipe information in the loop at the end.
Charles
Also, in regards to PDO, the general concepts presented (prepare, execute, fetch) should be the same regardless of which database interface you're using. You *should* be using PDO or mysqli in favor of mysql.
Charles
I'm using phpmyadmin. I don't want to create an array for the results - I want to use the results individually, if that makes sense. I want to be able to use html tags for each ingredients, either a class or a href to a separate page dedicated to that ingredient.I've never used mysql before 2 weeks ago. This is a horribly steep learning curve.
circey
Ah, wait, so you aren't writing any PHP code? You're just issuing queries against the database and working with the results manually?
Charles
Yes, that's pretty much it. The query goes into the php file and then I'm trying to work with the results manually as you said. Obviously the db is not about recipes, but it's the closest/most basic example that I can come up with. Fields like ing_code will be used to provide ids for divs and a href. Other fields like recipe_name will be used for img src. I had a freelance guy in India trying to set up the db for me, but he seems to have misunderstood my needs, so I've wasted money and time and have had to resort to trying to do this myself.
circey
Given that information, I think that a somewhat better solution for you will be *not* fetching all of the ingredients for all of the recipes, but all of the ingredients for one recipe at a time. The answer given here by OMG Ponies would work well for that. You'd just have to repeat the query for every recipe.
Charles