SitePoint Sponsor |
|
User Tag List
Results 1 to 17 of 17
Thread: design question
-
Nov 24, 2008, 02:18 #1
- 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.
-
Nov 24, 2008, 02:53 #2Code:
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
Guido - Community Team Leader
The Votes Are In: The Winners of the 2013 Community Awards are...
Blog - Free Flash Slideshow Widget
-
Nov 24, 2008, 03:36 #3
- 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.
-
Nov 24, 2008, 04:10 #4
- 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
-
Nov 24, 2008, 05:05 #5
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
Guido - Community Team Leader
The Votes Are In: The Winners of the 2013 Community Awards are...
Blog - Free Flash Slideshow Widget
-
Nov 24, 2008, 18:50 #6
- 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.
-
Nov 24, 2008, 22:50 #7
- 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.
-
Nov 25, 2008, 01:16 #8
- 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.
-
Nov 25, 2008, 05:16 #9
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?
Also, what is 2 in 'ORDER BY 2 DESC'?Guido - Community Team Leader
The Votes Are In: The Winners of the 2013 Community Awards are...
Blog - Free Flash Slideshow Widget
-
Nov 25, 2008, 22:18 #10
- 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.
-
Nov 26, 2008, 02:45 #11
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
Guido - Community Team Leader
The Votes Are In: The Winners of the 2013 Community Awards are...
Blog - Free Flash Slideshow Widget
-
Nov 26, 2008, 13:42 #12
- 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%' ";
Am I onto the right thing?
-
Nov 26, 2008, 14:00 #13
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Nov 26, 2008, 15:02 #14
- 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
-
Nov 26, 2008, 15:06 #15
- 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, '', '', '', '', '');
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');
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, '');
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);
-
Nov 26, 2008, 18:14 #16
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 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
-
Nov 26, 2008, 19:01 #17
- 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
Bookmarks