SitePoint Sponsor

User Tag List

Results 1 to 17 of 17

Thread: design question

  1. #1
    SitePoint Zealot
    Join Date
    Sep 2005
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    design question

    Hi Guys,

    I'm new to MYSQL and have a design question. I'm working on a recipe site search feature and need to figure out how to store the data so that it can easily be searched based on ingredients.

    I figured I would need three tables

    Recipes, Ingredients and Ingredient_types

    I'd submit regular recipe data to the recipe table and then submit a record to the ingredients tables for every ingredient needed.

    Each record in the ingredients table would have at least the following fields: id, ingredient_type_id and recipe_id

    How would I search for recipes based on ingredient?

    Say I want to search for recipes that have potatoes, basil, chicken and tomatoes and order them based on how many of the search ingredients they contain.

    Thanks for any help with this. Anything you can offer to turn me on to the answer would be wonderful.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,398
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Code:
    SELECT 
      recipe,
      COUNT(*) as number_of_ingredients
    FROM Ingredient_types AS a
    INNER JOIN Ingredients AS b
    ON a.ingredient_type_id = b.ingredient_type_id
    INNER JOIN Recipes AS c
    ON a.ingredient_type_id = c.ingredient_type_id
    WHERE a.ingredient_type IN ('potatoes', 'basil', 'chicken', 'tomatoes')
    ORDER BY 2 DESC

  3. #3
    SitePoint Zealot
    Join Date
    Sep 2005
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    thanks for the answer. I do not understand it yet but I'm working on it. It doesnt work yet though. I get Error Number: 1064

    To be clear, my database is as follows but it can change if there is a better design for this purpose:

    recipes (id, name, user_id, prep_time) //data for recipe
    ingredients (id, recipe_id, ingredient_type_id) //the ingredient records for recipes
    ingredient_type (id, type) //directory of ingredient types

    Studying your response now, but any quick fix answers welcomed.

  4. #4
    SitePoint Zealot
    Join Date
    Sep 2005
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry I am unable to make sense of it. I'm still trying however. i haven't been able to get it to work either unfortunately. Any help would be appreciated. Thanks

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,398
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Sorry, I messed the fields and tables up a bit, and also forgot the group by. I rewrote the query using the table and field names you provided. Hope this works better
    Code:
    SELECT 
      c.name,
      COUNT(*) as number_of_ingredients
    FROM ingredient_type AS a
    INNER JOIN ingredients AS b
    ON a.id = b.ingredient_type_id
    INNER JOIN recipes AS c
    ON b.recipe_id = c.id
    WHERE a.type IN ('potatoes', 'basil', 'chicken', 'tomatoes')
    GROUP BY c.name
    ORDER BY 2 DESC

  6. #6
    SitePoint Zealot
    Join Date
    Sep 2005
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Thank you so much. It appears to be working now. I'm going to study it carefully and fully understand it. Very grateful. many thanks.

  7. #7
    SitePoint Zealot
    Join Date
    Sep 2005
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So what would be the query for how to select recipes by name to be ordered by the number of ingredients?


    I still haven't figured out the mysql. I'm going to go take a long look at the documentation.

    Thanks for any help.

  8. #8
    SitePoint Zealot
    Join Date
    Sep 2005
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    What is happening in 'COUNT(*) as number_of_ingredients' ? Does it return a number somehow? How do I access it?

    Also, what is 2 in 'ORDER BY 2 DESC'?

    Also, how would I change it to use MATCH instead of IN?

    Thanks for any help. I'm really at a loss.

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,398
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by grafenberg View Post
    Hi,

    What is happening in 'COUNT(*) as number_of_ingredients' ? Does it return a number somehow?
    http://dev.mysql.com/doc/refman/5.1/...ting-rows.html
    COUNT(*) counts the number of rows that correspond (in this query) to each value of name (which is specified in the GROUP BY clause).
    So, if a recipe contains two ingredients, the join before GROUPING by the recipe name would contain two rows for that recipe (one for each ingredient). Since you're not interested in the ingredient details, but only in the number of ingredients per recipe, the query only selects the recipe name, and counts the number of rows the join found for each recipe.
    How do I access it?
    After you run the query, the number is returned as any other field you put in the SELECT clause, and its name is number_of_ingredients (the AS in the query gives another name to the field, or table).
    Also, what is 2 in 'ORDER BY 2 DESC'?
    2 indicates you want to order on the second field specified in the SELECT clause (in this case the number of ingredients). There might be another way as well in MySQL to say you want to order the values of the COUNT(*), but in the database I work with, this is the only way.

  10. #10
    SitePoint Zealot
    Join Date
    Sep 2005
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow I just can't get it to work.

    I'm looking for a way to select recipes from a database and order them based on how many of the recipe's ingredients match the ingredients of the user's pantry table.

    I have four tables in my database

    recipes
    id, name, prep_time

    ingredient_types (static list of all ingredients in the world)
    id, type

    ingredients (for each recipe posted a record is created for the ingredient needed)
    id, ingredient_type_id, recipe_id

    pantry (a table containing records of each ingredient a user has at home)
    id, ingredient_type_id


    I was hoping to pull recipe data and in the same query check how many matches each ingredient of the recipe has to the user's pantry table and then order the output of the recipe data based on the number of ingredient matches.

    Is this possible with mysql or do I have to resort to a complex and possibly slow php sorting of the data?

    Thank you for any help with this query, or suggestions for other methods of doing it.

  11. #11
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,398
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    I might write you the entire query again, but why don't you post what you've come up with so far. That way we can point out what you're doing wrong, and you can learn from that

  12. #12
    SitePoint Zealot
    Join Date
    Sep 2005
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi guido2004,

    I'm not sure if this is correct, but after pounding through a mysql book last night, logically it looks like it should work:



    Code:
    $sql = "
            SELECT 
                recipes.*
                , top3.ingr_count
                , top2.ingr_have_count
            FROM recipes
           
            
            /* add on count of recipe ingredients */
            LEFT JOIN ( 
                SELECT recipe_id, COUNT(*) as ingr_count
                FROM 'ingredients'
                GROUP BY id
            ) as top3
            ON recipes.id = top3.recipe_id
                  
            /* ingredients i have count */
    
            LEFT JOIN (
            ";
    
        					$sql .= "
        					  SELECT id, count(epantry.user_id) as ingr_have_count FROM recipes
        					  LEFT JOIN ingredients ON recipes.id = ingredients.recipe_id
        					  LEFT JOIN epantry ON epantry.ingredient_type_id = ingredients.ingredient_type_id AND
        					  epantry.user_id = '".$userid."' GROUP BY id
        					";
        
            $sql .= "
            ) as top2
            ON recipes.id = top2.recipe_id
            
            WHERE (1 = 1)
            AND name LIKE '%$safe_recipe_title%'
        ";
    It throws a database error at the moment. I'm at a loss.

    Am I onto the right thing?

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by grafenberg View Post
    It throws a database error at the moment. I'm at a loss.
    so are we

    try testing your query outside of php first

    that way you'll be sure to get the correct error message

    which we can't see unless you show it to us

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Zealot
    Join Date
    Sep 2005
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    HI,

    Thanks for the tip on doing it outside of php.

    Will phpmyadmin return the proper errors?

    I get:
    Code:
    Error
    
    SQL query: Documentation
    
    SELECT id, name, prep_time, top3.ingr_count, top2.ingr_have_count
    FROM recipes
    LEFT JOIN (
    
    SELECT recipe_id, COUNT( * ) AS ingr_count
    FROM 'ingredients'
    GROUP BY id
    ) AS top3 ON recipes.id = top3.recipe_id
    LEFT JOIN (
    
    SELECT id, count( epantry.user_id ) AS ingr_have_count
    FROM recipes
    LEFT JOIN ingredients ON recipes.id = ingredients.recipe_id
    LEFT JOIN epantry ON epantry.ingredient_type_id = ingredients.ingredient_type_id
    AND epantry.user_id = '".$userid."'
    GROUP BY id
    ) AS top2 ON recipes.id = top2.recipe_id
    WHERE ( 1 =1 )
    AND name LIKE '%$safe_recipe_title%'
    LIMIT 0 , 30
    
    MySQL said: Documentation
    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''ingredients'
                GROUP BY id
            ) as top3
            ON recipes.id = ' at line 10

  15. #15
    SitePoint Zealot
    Join Date
    Sep 2005
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My tables:

    recipes
    Code:
    CREATE TABLE IF NOT EXISTS `recipes` (
      `id` mediumint(9) NOT NULL auto_increment,
      `user_id` mediumint(9) NOT NULL,
      `name` text NOT NULL,
      `prep_time` varchar(50) NOT NULL,
      `cook_time` varchar(50) NOT NULL,
      `directions` text NOT NULL,
      `photo` varchar(255) default NULL,
      `submitted_time` int(10) NOT NULL,
      `category1` varchar(255) NOT NULL,
      `category2` varchar(255) NOT NULL,
      `category3` varchar(255) NOT NULL,
      `category4` varchar(255) NOT NULL,
      `category5` varchar(255) NOT NULL,
      PRIMARY KEY  (`id`),
      FULLTEXT KEY `name` (`name`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
    
    --
    -- Dumping data for table `recipes`
    --
    
    INSERT INTO `recipes` (`id`, `user_id`, `name`, `prep_time`, `cook_time`, `directions`, `photo`, `submitted_time`, `category1`, `category2`, `category3`, `category4`, `category5`) VALUES
    (1, 26, 'Roasted Potatoes with Golden Garlic Sauce ', '15 minutes', '10 minutes', '1. Preheat oven to 425 degrees. Put 3 tablespoons of the olive oil in the bottom of a 9 x 13-inch baking dish and heat in oven for 5 minutes. Add potatoes, tossing to coat them. Roast the potatoes, turning occasionally, for 30 to 35 minutes or until tender.<br><br>\r\n\r\n2. While the potatoes are baking, prepare garlic sauce: In a small skillet, heat remaining 3 Tablespoons olive oil, add cloves of garlic. Cover and cook over very low heat for 7 to 10 minutes, or until garlic is tender. Sprinkle with sugar and stir until garlic is caramelized. Add balsamic vinegar, white wine, basil and simmer 2 minutes. Spoon sauce over the roasted potatoes. Sprinkle with the fresh ground pepper and parsley and enjoy!', NULL, 1227420425, '1', '2', '3', '4', '5'),
    (2, 26, 'Pork Steak Burritos', '10 minutes', '20 minutes', '1. Heat the oil in a skillet over medium-high heat. Place pork in the skillet, and cook until evenly brown. Pour in the salsa, and continue cooking 5 minutes, until heated through.<br><br>2. Place tortillas 1 or 2 at a time on a microwave-safe dish. Cook in the microwave 1 minute on High, until warm. Place equal amounts of pork strips and salsa in the center of each warm tortilla, and roll. Top with sour cream and garnish with green onions to serve.', NULL, 0, '', '', '', '', ''),
    (3, 26, 'Vegetarian Lasagna', '20 minutes', 'About a half hour', '1. Bring a large pot of lightly salted water to a boil. Cook lasagna pasta in boiling water for 8 to 10 minutes, or until al dente. Drain, rinse with cold water, and place on wax paper to cool.\r\n\r\n\r\n\r\n2. Cook bell peppers and onion in olive oil in a large sauce pan until onions are translucent. Stir in diced tomatoes, tomato paste, water, and red pepper flakes. More red pepper flakes can be added if spicier sauce is preferred. Simmer for 30 minutes.\r\n\r\n\r\n\r\n3. Preheat oven to 375 degrees F (190 degrees C). In a medium bowl, combine Parmesan cheese, ricotta cheese, mozzarella cheese, eggs, black pepper, and oregano.\r\n\r\n\r\n\r\n4. Place a small amount of sauce in the bottom of a 9x13 inch baking dish. Reserve 1/2 cup of the sauce. Place three lasagna noodles lengthwise in pan. Layer some of the cheese mixture and the vegetable sauce on top of noodles. Repeat layering with remaining ingredients, ending with noodles. Spread reserved sauce over top of noodles. Sprinkle with grated Parmesan cheese, if desired.\r\n\r\n\r\n\r\n5. Cover dish with foil, and bake for 40 minutes or until bubbly. Remove foil during last 10 minutes of baking. ', NULL, 0, '', '', '', '', ''),
    (4, 26, 'chicken sandwhich', '5 minutes', '0 minutes', 'put bread on counter. Place one slice on one side ad the other on the other. Go to the refrigerator and pull out the chicken breast. put on bread. Put lettuce on bread. ', NULL, 0, '', '', '', '', ''),
    (5, 26, 'Chicken Salad', '20 minutes', '0 minutes', 'chop chicken up and mix with salad. Sprinkle some sprinkles over top if you like that and then move from salad bowl to plate and eat. ', NULL, 0, '', '', '', '', '');
    ingredient_types
    Code:
    CREATE TABLE IF NOT EXISTS `ingredient_type` (
      `id` mediumint(9) NOT NULL auto_increment,
      `type` varchar(255) NOT NULL,
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;
    
    --
    -- Dumping data for table `ingredient_type`
    --
    
    INSERT INTO `ingredient_type` (`id`, `type`) VALUES
    (1, 'chicken'),
    (2, 'basil'),
    (3, 'tomato'),
    (4, 'potato'),
    (5, 'rice'),
    (6, 'onion'),
    (7, 'pork'),
    (8, 'beef'),
    (9, 'lamb'),
    (10, 'turkey'),
    (11, 'tortilla');
    ingredients
    Code:
    CREATE TABLE IF NOT EXISTS `ingredients` (
      `id` mediumint(9) NOT NULL auto_increment,
      `recipe_id` mediumint(9) NOT NULL,
      `ingredient_type_id` mediumint(9) NOT NULL,
      `amount` varchar(255) NOT NULL,
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;
    
    --
    -- Dumping data for table `ingredients`
    --
    
    INSERT INTO `ingredients` (`id`, `recipe_id`, `ingredient_type_id`, `amount`) VALUES
    (8, 5, 1, ''),
    (7, 4, 2, ''),
    (6, 4, 1, ''),
    (5, 1, 4, ''),
    (9, 2, 7, ''),
    (10, 2, 11, '');
    epantry
    Code:
    CREATE TABLE IF NOT EXISTS `epantry` (
      `id` mediumint(9) NOT NULL auto_increment,
      `user_id` mediumint(9) NOT NULL,
      `ingredient_id` mediumint(9) NOT NULL,
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
    
    --
    -- Dumping data for table `epantry`
    --
    
    INSERT INTO `epantry` (`id`, `user_id`, `ingredient_id`) VALUES
    (1, 26, 1),
    (2, 26, 4),
    (3, 26, 7);
    Am I on the right track with the query?

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yes you are on the right track but you have to be very precise

    you have -- FROM 'ingredients'

    that's trying to return rows from a string

    you want -- FROM ingredients

    that will return rows from a table

    see the difference? the error message told you exactly where to look, too

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Zealot
    Join Date
    Sep 2005
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for all the help guys. A user on phpfreaks gave me a great start query to play with and my initial tests show it's working.

    the query:
    Code:
    SELECT r.id, r.user_id, r.name, r.prep_time, r.cook_time, r.directions, r.photo, r.submitted_time, it.type,it.id,COUNT(p.ingredient_id) as 'have' FROM recipes r
    				JOIN ingredients i ON(r.id=i.recipe_id)
    				JOIN ingredient_type it ON(i.ingredient_type_id=it.id)
    				JOIN epantry p ON(i.ingredient_type_id =p.ingredient_id)
    				WHERE MATCH (r.name) AGAINST('$search_terms')
    				GROUP BY r.name
    				ORDER BY COUNT(p.ingredient_id) DESC
    Very grateful.


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
  •