Listing data from mysql tables

Question

Hi. I have 3 tables:

recipe_category

cid | category
 1  | desserts
 2  | cakes
 3  | biscuits

recipe_name

id | recipe_name       | cid | iid
 1 | black forest cake | 1,2 | 1,2,3,4
 2 | angel cake        | 2   | 1,2,4
 3 | melting moments   | 3   | 2,5
 4 | croquembouche     | 1,3 | 1,5

ingredients

iid | ingredient_name
 1  | self-raising flour
 2  | milk
 3  | chocolate
 4  | baking powder
 5  | plain flour

I am able to query the DB using cid to pull certain recipes, ie. desserts:

SELECT * FROM recipe_name WHERE cid='1'

But then how do I create a list of ingredients like the below where ingredients are listed with <br>?

Black Forest Cake:
Self-Raising Flour
Milk
Chocolate
Baking Powder

I'm new to this, so please forgive any stupid questions!

Answer 1

First off you should be using an intersect table. In this table you will have a list of keys for recipe_names and a key for each of the ingredients. So for black forest cake you will have 4 rows in the intersect table. This allows you to build queries much quicker and easier. You don't have to mess around with key arrays.

The table may look like this for black forest cake:

recipe_name_id  ingredients_id
1                 1
1                 2
1                 3
1                 4

A query would look like this:

SELECT ingredient_name
  FROM recipe_name
     , ingredients
     , recipe_ingredients --intersect table
 WHERE recipe_name.id=1
   AND recipe_name.id = recipe_ingredients.recipe_name_id
   AND ingredients.id = recipe_ingredients.ingredients_id

Answer 2

The fact that you're asking about <br>tells me that you want to display these in a web page. My advice is to separate querying for the data from displaying it.

Since a recipe can have many ingredients, and an ingredient can appear in many recipes, you'll need a many to many join table between recipe and ingredient. It'll have two columns - one for the recipe primary key and another for the ingredient primary key.

Your comma-separated values breaks normalization rules. You'll want to know what those are.

Answer 3

Storing multivalued attributes in a single comma separated field is almost always a bad idea. It makes everything very difficult to query.

Instead, you may want to consider refactoring your schema, using two new intersection tables.

These two tables remain as they are (just changed name of recipe_category to categories in order not to clash with the intersection table):

CREATE TABLE categories (
   cid int NOT NULL PRIMARY KEY,
   category_name varchar(50)
) ENGINE=INNODB;

CREATE TABLE ingredients (
   iid int NOT NULL PRIMARY KEY,
   ingredient_name varchar(50)
) ENGINE=INNODB;

Modify the recipe_name table as follows, removing the cid and iid fields:

CREATE TABLE recipe_name (
    id int NOT NULL PRIMARY KEY,
    recipe_name varchar(50)
) ENGINE=INNODB;

Then you can define your multivalued relationships using the following two intersection tables:

CREATE TABLE recipe_ingredients (
    recipe_id int NOT NULL,
    ingredient_id int NOT NULL,
    PRIMARY KEY (recipe_id, ingredient_id),
    FOREIGN KEY (recipe_id) REFERENCES recipe_name (id),
    FOREIGN KEY (ingredient_id) REFERENCES ingredients (iid)
) ENGINE=INNODB;

CREATE TABLE recipe_categories (
    recipe_id int NOT NULL,
    category_id int NOT NULL,
    PRIMARY KEY (recipe_id, category_id),
    FOREIGN KEY (recipe_id) REFERENCES recipe_name (id),
    FOREIGN KEY (category_id) REFERENCES categories (cid)
) ENGINE=INNODB;

Now let's populate these tables with your example data:

INSERT INTO categories VALUES (1, 'desserts');
INSERT INTO categories VALUES (2, 'cakes');
INSERT INTO categories VALUES (3, 'biscuits');

INSERT INTO ingredients VALUES(1, 'self-raising flour');
INSERT INTO ingredients VALUES(2, 'milk');
INSERT INTO ingredients VALUES(3, 'chocolate');
INSERT INTO ingredients VALUES(4, 'baking powder');
INSERT INTO ingredients VALUES(5, 'plain flour');

INSERT INTO recipe_name VALUES(1, 'black forest cake');
INSERT INTO recipe_name VALUES(2, 'angel cake');
INSERT INTO recipe_name VALUES(3, 'melting moments');
INSERT INTO recipe_name VALUES(4, 'croquembouche'); 

To define the relationships between the recipes and their ingredients and categories, you would need to fill up the intersection tables as follows:

INSERT INTO recipe_categories VALUES (1, 1);
INSERT INTO recipe_categories VALUES (1, 2);
INSERT INTO recipe_categories VALUES (2, 2);
INSERT INTO recipe_categories VALUES (3, 3);
INSERT INTO recipe_categories VALUES (4, 1);
INSERT INTO recipe_categories VALUES (4, 3);

INSERT INTO recipe_ingredients VALUES (1, 1);
INSERT INTO recipe_ingredients VALUES (1, 2);
INSERT INTO recipe_ingredients VALUES (1, 3);
INSERT INTO recipe_ingredients VALUES (1, 4);
INSERT INTO recipe_ingredients VALUES (2, 1);
INSERT INTO recipe_ingredients VALUES (2, 2);
INSERT INTO recipe_ingredients VALUES (2, 3);
INSERT INTO recipe_ingredients VALUES (3, 2);
INSERT INTO recipe_ingredients VALUES (3, 5);
INSERT INTO recipe_ingredients VALUES (4, 1);
INSERT INTO recipe_ingredients VALUES (4, 5);

Finally, building your query will be as easy as this:

SELECT i.ingredient_name
FROM   recipe_ingredients ri
JOIN   ingredients i ON (i.iid = ri.ingredient_id)
WHERE  ri.recipe_id = (SELECT id 
                       FROM   recipe_name 
                       WHERE  recipe_name = 'Black Forest Cake');

Result:

+--------------------+
| ingredient_name    |
+--------------------+
| self-raising flour |
| milk               |
| chocolate          |
| baking powder      |
+--------------------+
4 rows in set (0.00 sec)

You may then want to format that result set (adding the <br>s) in your application code instead of in SQL.

However if you really wish to do that in SQL, then MySQL supports the handy GROUP_CONCAT() function, which can be used as follows:

SELECT GROUP_CONCAT(i.ingredient_name separator '<BR>') output
FROM   recipe_ingredients ri
JOIN   ingredients i ON (i.iid = ri.ingredient_id)
WHERE  ri.recipe_id = (SELECT id 
                       FROM   recipe_name 
                       WHERE  recipe_name = 'Black Forest Cake');

Result:

+----------------------------------------------------------+
| output                                                   |
+----------------------------------------------------------+
| self-raising flour<BR>milk<BR>chocolate<BR>baking powder |
+----------------------------------------------------------+
1 row in set (0.00 sec)

Dump that into HTML, and you're good to go!

Answer 4

You definitely don't want to have a comma-separated list of foreign keys like you did with cid and iid in recipe_name. Instead, make tables that just handle those relationships:

recipe_name

id | recipe_name
 1 | black forest cake
 2 | angel cake
 3 | melting moments
 4 | croquembouche

recipe_categories

id | cid
 1 |  1
 1 |  2
 2 |  2
 3 |  3
 4 |  1
 4 |  3

recipe_ingredients

id | iid
 1 |  1
 1 |  2
 1 |  3
 1 |  4
 2 |  1
 2 |  2
 2 |  4
 3 |  2
 3 |  5
 4 |  1
 4 |  5

---

Now your query to select any desserts (your old one didn't work since it would miss rows with the cid 1,2, for example) looks like:

SELECT recipe_name.* FROM recipe_name, recipe_categories WHERE recipe_name.id = recipe_categories.id AND recipe_categories=cid = 1;

Once you've done that, you can write a query that joins the ingredients and recipe_ingredients tables to get you the results you want:

SELECT ingredients.* FROM ingredients, recipe_ingredients WHERE recipe_ingredients.id = 1 AND recipe_ingredients.iid = ingredients.iid;