[SOLVED] Need help with a mysql query

[nathancobb]

Hello,

 

I've been searching for days and days for an answer to this problem.

 

I'm working on the search feature of a recipe site and I need to pull data and order it based on the relation of two other tables.

 

The objective is to be able to search for recipes by title using fulltext and then order the data based on how many of the required recipe ingredients the user has stored in a table named pantry.

 

I have four tables:

 

Recipes
id, name, prep_time

 

ingredients
id, ingredient_type_id,

 

ingredient_types
id, type

 

pantry
id, ingredient_type

 

When a user searches for "chicken pasta" for example he will see recipes with that in the title but they will be ordered by the number of ingredients he has in his pantry to make them.

 

is it even possible?

 

Thanks for any help.

 

[xtopolis]

Can you post a mysql dump of your structure and some sample data?  It's hard to tell from your pseudocode.

[Mchl]

I can see no connection relation between recipes and ingredients

[scarhand]

mchl is right, you have no connection between the tables

 

i.e. "recipes" should have a field called "ingredients", which could be an array of ingredient ID's

 

this way you could select from the "recipies" table where the ingredient ID would find a match in the ingredient field array

[Mchl]

i.e. "recipes" should have a field called "ingredients", which could be an array of ingredient ID's

 

Not really.

It's better to have a separate table for such relation.

[nathancobb]

sorry for the confusion, I just forgot to type the field when posting my tables.

 

I've been told on the mysql forums by several people basically to just change what the website does, but it's not my site and the owner likes what it does.

 

How do I do a database dump without a mysql prompt? I have phpmyadmin, can it be done with this?

 

Because I don't know a better way to present this, my tables are:

Recipes
id, name, prep_time

ingredients
id, recipe_id, ingredient_type_id

ingredient_types
id, type

pantry
id, ingredient_type_id

 

I'm scratching the walls trying to get past this. I'm reading two mysql books right now but it's no replacement for experience. I do have a snippet of code which does something similar but the database structure I think is different and basically I can't make sense of it. I'll append it below. Many thanks for any help.

 

$sql = "
        SELECT 
            recipes.*
            , CONCAT(SUBSTRING_INDEX(recipe_instr, ' ', 20), '...') as small_desc
            , (CASE WHEN recipe_id_review IS NULL THEN 0 ELSE 1 END) as has_reviews
            , t2.avgRate
            , top3.ingr_count
            , top2.ingr_have_count
        FROM recipes
        LEFT JOIN (
            SELECT recipe_id as recipe_id_review, AVG(rate) as avgRate
            FROM reviews
            GROUP BY recipe_id
        ) as t2
        ON recipes.recipe_id = t2.recipe_id_review
        
        /* add on count of recipe ingredients */
        LEFT JOIN ( 
            SELECT recipeid, COUNT(*) as ingr_count
            FROM `recipe_ingredients`
            GROUP BY recipeid
        ) as top3
        ON recipes.recipe_id = top3.recipeid
              
        /* ingredients i have count */

        LEFT JOIN (
        ";

    					$sql .= "
    					  SELECT recipeid, count(myepantry.userid) as ingr_have_count FROM recipes
    					  LEFT JOIN recipe_ingredients ON recipes.recipe_id = recipe_ingredients.recipeid
    					  LEFT JOIN myepantry ON myepantry.groceryid = recipe_ingredients.groceryid AND
    					  myepantry.userid = '".$userid."' WHERE verified = '1' GROUP BY recipe_id
    					";
    
        $sql .= "
        ) as top2
        ON recipes.recipe_id = top2.recipeid
        
        WHERE (1 = 1)
        AND recipe_name LIKE '%$safe_recipe_title%'
    ";

[xtopolis]

In PHPMYADMIN you select a table on the left, then go to the export tab.  It should default to:

Export: SQL, with Structure checked, add_auto increment checked, enclose in backticks, and Data checked, with use hex for binary fields checked.  This will output something that you can copy and paste into here; be sure to remove any sensitive info such as usernames/passwords!

 

I don't know if this will solve your problem, but it's a lot easier to see and mess with if you we have some dumps.

 

We need an export sql dump for each table in question. (4)

Along with some data from each to test, (meaning you can trim some data if there's a lot displayed)

 

Also: Your pseudo code changed from your first post.. apparently your tables are now different which definitely changes things.  That is why we need to see the true structure.  Might as well make it easy on us and give us something to copy and past into our own test sites.

[nathancobb]

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, '');

 

pantry

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);

 

 

I'm pouring over this script to see if I can make sense of it and try to apply it to the new database. This was from the same site though an old database. He had the site updated and the developers redid the sorting with php which is painfully slow. I wanted to go back to mysql because the sorting algorithms are written for the best performance, versus something joe the web developer came up with.

 

Any help with this would be a godsend, honestly.

 

let me know if you need any other info please. And I'm not asking to have a query written for me, though it would be great, but just some help or pointers as to what functionality to use (if not included in the sql query above). Or some sense made of the sql query so i can apply it to what I have.

 

thanks.

 

the sql query again:

 

$sql = "
        SELECT
            recipes.*
            , CONCAT(SUBSTRING_INDEX(recipe_instr, ' ', 20), '...') as small_desc
            , (CASE WHEN recipe_id_review IS NULL THEN 0 ELSE 1 END) as has_reviews
            , t2.avgRate
            , top3.ingr_count
            , top2.ingr_have_count
        FROM recipes
        LEFT JOIN (
            SELECT recipe_id as recipe_id_review, AVG(rate) as avgRate
            FROM reviews
            GROUP BY recipe_id
        ) as t2
        ON recipes.recipe_id = t2.recipe_id_review
       
        /* add on count of recipe ingredients */
        LEFT JOIN (
            SELECT recipeid, COUNT(*) as ingr_count
            FROM `recipe_ingredients`
            GROUP BY recipeid
        ) as top3
        ON recipes.recipe_id = top3.recipeid
             
        /* ingredients i have count */

        LEFT JOIN (
        ";

                   $sql .= "
                     SELECT recipeid, count(myepantry.userid) as ingr_have_count FROM recipes
                     LEFT JOIN recipe_ingredients ON recipes.recipe_id = recipe_ingredients.recipeid
                     LEFT JOIN myepantry ON myepantry.groceryid = recipe_ingredients.groceryid AND
                     myepantry.userid = '".$userid."' WHERE verified = '1' GROUP BY recipe_id
                   ";
   
        $sql .= "
        ) as top2
        ON recipes.recipe_id = top2.recipeid
       
        WHERE (1 = 1)
        AND recipe_name LIKE '%$safe_recipe_title%'
    ";

 

the rating stuff in this can be removed and I can just access it directly after the query. I suspect it will be faster this way anyway.

 

Nathan Cobb

[xtopolis]

Ok, thanks for the structures, helps a lot.  So to answer the first question, regarding showing recipes that could be made based on pantry items:

 

I came up with a query, but I didn't take into account the amount needed.  I gave user_id 26 some basil to test, and it seemed to work.  I can't really explain it as I'm not that good at mysql, but basically I joined all the tables, and ordered by the pantry items that matched...Here's the code if you want, I spaced it in case you didn't want a spoiler:

Scroll Down

















SELECT r.name,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 r.name LIKE "chicken%"
GROUP BY r.name
ORDER BY COUNT(p.ingredient_id) DESC

It may not be the best query, but it's a start.

 

Haven't looked at your big query at the bottom.... it's a bit over my head.  I might look at it later, but otherwise, I can help with specific questions, but not huge ones lol.  Hope the above query gets you a bit closer to what you need.

[nathancobb]

Hey this looks really good to me at first glance.

 

I tried it and it seems to be pulling the correct data too!

 

I'm going to do some further tests and then come back and thank you some more.

 

it looks like a very good start if not exactly what I need.

 

very very much appreciated.

[nathancobb]

Thanks again to all who helped. Xtopolis gave me a great query and my initial tests show that it does just the thing. Very grateful.