php mysql query based on multiple user selection

Question

I have a database that has 3 fields (Country, Recipe, Ingredient). All of them are primary keys.

I have a website where a user selects 2 countries and I want to display the ingredients that are the same between 2 countries.

So I wrote a query:

$result = mysql_query("SELECT DISTINCT (Recipe) 
                         FROM recipes r1, 
                              recipes r2 
                        WHERE r1.Country = '$temp' 
                            ^ r2.Country = '$temp2' 
                            ^ r1.Ingredient = r2.Ingredient");

But this gives me an error saying "Column Recipe is ambiguous". How exactly do I fix this?

I also want a count of all the distinct recipes. So I wrote a query:

$result = mysql_query("CREATE VIEW temporary(Inglist) AS (
                          SELECT DISTINCT (Recipe) 
                            FROM recipes r1, 
                                 recipes r2 
                           WHERE r1.Country = '$temp' 
                               ^ r2.Country = '$temp2' 
                               ^ r1.Ingredient = r2.Ingredient) 
                          SELECT COUNT(*) 
                            FROM 'temporary' ");  

I am quite new to writing queries so I am confused as to how to get these to work. How exactly do I go about fixing these? Any help would be appreciated.

Answer 1

Try

$result = mysql_query("SELECT DISTINCT (r1.Recipe) FROM recipes r1, recipes r2 WHERE r1.Country = '$temp' ^ r2.Country = '$temp2' ^ r1.Ingredient = r2.Ingredient");

Answer 2

why is Recipe in perens? perens are a grouping operator or for specifying arguments, but in this case they are not needed. DISTINCT is not a function. not knowing the structure of your table, I can't help you further

Answer 3

You may try as Chris Thompson said or try with

$result = mysql_query("SELECT DISTINCT (r2.Recipe) FROM recipes r1, recipes r2 WHERE r1.Country = '$temp' ^ r2.Country = '$temp2' ^ r1.Ingredient = r2.Ingredient");

But as I understand you your table have:

1. Country
2. Recipe
3. Ingredient

And on recipe have on ingredient? And than you want to show the same ingredients in 2 countries OR?

Answer 4

It would be helpful if you provided an example of your database structure. Also, you should first optimize your database by creating separate tables for the countries, recipes, and ingredients. As it is now, you will have repeated entries with the same country and recipe just to list all of the ingredients.

Your tables should look like this:

Countries Table

countryId          countryName
    1         United States of America
    2                England

Recipes Table

recipeId           recipeName       countryId      dateAdded      
   1                Jambalaya           1         2012-10-11
   2             Bangers and Mash       2         2013-11-04

Ingredients

ingredientId       ingredientName
    1                  rice
    2                 potato

Recipe and Ingredients Map

riMapId          recipeId          ingredientId
   1                1                    1
   2                2                    2

Ingredients and recipes are associated in the "Recipe and Ingredients Map" table. This properly separates the country from the ingredients and allows you to have more fields like 'dateAdded' that have nothing to do with the ingredients or country. While this makes select statements more complex, it also makes them more efficient and powerful.

To select all of the ingredients from two countries

SELECT
    `ingredients`.`ingredientName` AS 'ingredientName',
    `countries`.`countryName` AS 'countryName',
    `recipes`.`recipeName` AS 'recipeName',
    `recipeIngredientMap`.`riMapId` AS 'riMapId'
FROM
    `ingredients`
LEFT JOIN
    `recipeIngredientMap` ON `recipeIngredientMap`.`ingredientId` = `ingredients`.`ingredientId`
LEFT JOIN
    `recipes` ON `recipes`.`recipeId` = `recipeIngredientMap`.`recipeId`
JOIN
    `countries` ON `countries`.`countryId` = `recipes`.`countryId` AND
    `countries`.`countryId` IN (1,2)

To select the count of recipes from the same two countries

SELECT
    COUNT(`recipeName`) AS 'recipeCount'
FROM
    `recipes`
WHERE
    `countryId` IN (1,2)

The mysql structure

--
-- Database: `food`
--

-- --------------------------------------------------------

--
-- Table structure for table `countries`
--

CREATE TABLE `countries` (
  `countryId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `countryName` varchar(255) NOT NULL,
  PRIMARY KEY (`countryId`),
  UNIQUE KEY `countryName` (`countryName`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

-- --------------------------------------------------------

--
-- Table structure for table `ingredients`
--

CREATE TABLE `ingredients` (
  `ingredientId` int(11) NOT NULL AUTO_INCREMENT,
  `ingredientName` varchar(255) NOT NULL,
  PRIMARY KEY (`ingredientId`),
  UNIQUE KEY `ingredientName` (`ingredientName`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

-- --------------------------------------------------------

--
-- Table structure for table `recipeIngredientMap`
--

CREATE TABLE `recipeIngredientMap` (
  `riMapId` int(11) NOT NULL AUTO_INCREMENT,
  `recipeId` int(10) unsigned NOT NULL,
  `ingredientId` int(10) unsigned NOT NULL,
  PRIMARY KEY (`riMapId`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

-- --------------------------------------------------------

--
-- Table structure for table `recipes`
--

CREATE TABLE `recipes` (
  `recipeId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `recipeName` varchar(255) NOT NULL,
  `countryId` int(10) unsigned NOT NULL,
  `dateAdded` date NOT NULL,
  PRIMARY KEY (`recipeId`),
  UNIQUE KEY `recipeName` (`recipeName`),
  KEY `countryId` (`countryId`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

Note that many of the fields are set as unique indexes. This prevents duplicate information. I would take this one step further and add foreign keys to prevent inserting rows that reference items that don't exist.