SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member
    Join Date
    Jun 2007
    Location
    Coventry
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PHP / MySQL Grouping issue.

    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

  2. #2
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  3. #3
    . shoooo... silver trophy logic_earth's Avatar
    Join Date
    Oct 2005
    Location
    CA
    Posts
    9,013
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    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.
    Logic without the fatal effects.
    All code snippets are licensed under WTFPL.


  4. #4
    SitePoint Member
    Join Date
    Jun 2007
    Location
    Coventry
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  5. #5
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    yep - that's how to do it:
    PHP Code:
    <?
    $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>";
    }
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  6. #6
    SitePoint Member
    Join Date
    Jun 2007
    Location
    Coventry
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  7. #7
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    calling mysql_insert_id() after the first insert query will get the last ID inserted into mysql.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  8. #8
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    177
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •