design question

[grafenberg]

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.

[guido2004]

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

[grafenberg]

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.

[grafenberg]

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

[guido2004]

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

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

[grafenberg]

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.

[grafenberg]

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.

[grafenberg]

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.

[guido2004]

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.

[grafenberg]

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.

[guido2004]

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

[grafenberg]

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:

$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?

[r937]

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

[grafenberg]

HI,

Thanks for the tip on doing it outside of php.

Will phpmyadmin return the proper errors?

I get:

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

[grafenberg]

My tables:

recipes

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

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

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

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?

[r937]

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

[grafenberg]

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:

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.