PHP / MySQL Grouping issue.

[nickharper]

I have 4 fields in a row

Ingred1
Ingred2
Ingred3
Ingred4

These could be for ingredients for food, let's say flour, egg, butter and milk.

When somebody submits a recipe it may look something like this:

Ingred 1 = Butter
Ingred 2 = Milk
Ingred 3 = Egg
Ingred 4 = Flour

This is all well and good, but if somebody submits a recipe like this:

Ingred 1 = Butter
Ingred 2 = Egg
Ingred 3 = Milk
Ingred 4 = Flour

It now means that Egg is in both fields Ingred 3 and 2.

I want to run a list of all the ingredients of my site off.

I could use DISTINCT but this would only show me distinct for ingred1 and not overall between the 4 different ingred field.

I would want it to just list the ingredients without repeating like:

Butter
Egg
Flour
Milk

How would I go about this?

Thanks

[Jake Arkinstall]

What you should do is have two tables. One containing the id, name and details. The other table would look like this
id | parent_id | ingredient

For each ingredient, have a new record in the second table. The field "Parent_id" is the id of the recipy.

Then you can use a simple "distinct query" to fetch every ingredient used, and you can use as many ingredients as you want for each recipy.

[logic_earth]

Actually you would need three tables. To me this is a many-to-many relationship. A recipe can have many different ingredients. And ingredients can be part of many recipes.

So need a recipe table, ingredients table, and a table to bind them together.

[nickharper]

How would I go about binding them?

I was just going to get a recipe page to select all of the ingredients using its ID in the parent_id field so it selects everything with the parent id of it and displaying it?

[Jake Arkinstall]

yep - that's how to do it:

<?
$q1 = mysql_query("SELECT * FROM `recipies` WHERE `name` = 'somecake'");
$row = mysql_fetch_array($q1);
printf("<h1>%s</h1>", $row['name']);
$q2sql = sprintf("SELECT * FROM `ingredients` WHERE `id` = '%s'", $row['id']);
$q2 = mysql_query($q2sql);
if(mysql_num_rows($q2)){
   echo "<ul>";
   while($row2 = mysql_fetch_array($q2)){
      printf("<li>%s</li>", $row2['name']);
   }
   echo "</ul>";
}

[nickharper]

In the form when I add a Recipe, how do I get the id of the recipe to go into the parent id of the Ingredients as this is added at the database rather than the form?

[Jake Arkinstall]

calling mysql_insert_id() after the first insert query will get the last ID inserted into mysql.

[Kon-Tiki]

Table structures:
Recipes
id
name
preparation

Ingredients
id
name

rec_and_ingr
id
recipe_id
ingredient_id

Linking them is with a query that spans multiple tables, like this:

SELECT recipes.*,  ingredients.* FROM recipes, ingredients, rec_and_ingr 
WHERE recipes.name = input_here 
AND recipes.id = rec_and_ingr.recipe_id
AND ingredients.id = rec_and_ingr.ingredient_id