Don't know how to restrict inner SQL query in PHP

don 2
to don something
don urban dictionary
don (1978)
don smallgoods
don movie
don japanese
don meaning slang

Tons of S/O searching has been extremely helpful so far ... and the following almost works. My problem is the second SQL query returns ALL the 'ingredients' for EACH 'recipe' -- whereas Recipe #1 has ingredients A,C,F, and Recipe #2 had ingredients A,G,H. How do I restrict the second query to return only the ingredients for the recipe in the current loop? I'd be very grateful for a PHP/SQL wizard's help to get over the top.

DB structure with many-to-many relationship:

RECIPE (recid, title) 
REC_ING (recid, ingid) 
INGREDIENT (ingid, ingredient)

Assuming recid is the id of the recipe. Try this:

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "recipe";
try {
  $db = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
}
catch(PDOException $e) {
   echo "Error: " . $e->getMessage();
}
$xml = new XMLWriter();
$xml->openURI('stackexch.xml');
$xml->setIndent(2);
$xml->startDocument('1.0', 'UTF-8');
$xml->startElement('recipes');
$recipe = $db->query("SELECT * FROM recipe");
foreach ($recipe as $row) {
  $xml->startElement('recipe');
  $xml->startElement('title');
  $xml->writeRaw($row['title']);
  $xml->endElement();
  $ingredient = $db->query("SELECT ingredient FROM recipe, rec_ing, ingredient WHERE recipe.recid=rec_ing.recid AND ingredient.ingid=rec_ing.ingid AND recipe.recid = " . $row['recid']);
  foreach ($ingredient as $subrow) {
    $xml->startElement('ingredient');
    $xml->writeRaw($subrow['ingredient']);
    $xml->endElement();
    }
  $xml->endElement();
}
$xml->endElement();
$xml->endDocument();
$xml->flush();
?>

Since you're already selecting from the recipe table, you don't need to select from it again. You could probably simplify the code like this:

<?php
    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "recipe";
    try {
      $db = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    }
    catch(PDOException $e) {
       echo "Error: " . $e->getMessage();
    }
    $xml = new XMLWriter();
    $xml->openURI('stackexch.xml');
    $xml->setIndent(2);
    $xml->startDocument('1.0', 'UTF-8');
    $xml->startElement('recipes');
    $recipe = $db->query("SELECT * FROM recipe");
    foreach ($recipe as $row) {
      $xml->startElement('recipe');
      $xml->startElement('title');
      $xml->writeRaw($row['title']);
      $xml->endElement();
      $ingredient = $db->query("SELECT ingredient FROM rec_ing, ingredient WHERE ingredient.ingid=rec_ing.ingid AND rec_ing.recid = " . $row['recid']);
      foreach ($ingredient as $subrow) {
        $xml->startElement('ingredient');
        $xml->writeRaw($subrow['ingredient']);
        $xml->endElement();
        }
      $xml->endElement();
    }
    $xml->endElement();
    $xml->endDocument();
    $xml->flush();
    ?>

Typically, I wouldn't recommend concatenating variables into a query, but in this case it is safe because you're already selecting the id from the database.

Don, Head of any mafia crime family. 2. Dom Perignon. Expensive Champagne. Often called 'Don' in the suburbs and hood as a joke that we don't know what it is. 3. 1. Don (also dōn) Used as a courtesy title before the name of a man in a Spanish-speaking area. 2.

This is consuming resources:

SELECT * FROM recipe

Yet you use only [title], so why pull all columns?

This is being executed for each row returned from above:

SELECT ingredient 
FROM recipe, rec_ing, ingredient 
WHERE recipe.recid=rec_ing.recid AND ingredient.ingid=rec_ing.ingid

But you can do both of the above in one query, therefore consuming less resources:

SELECT r.title, i.ingredient 
FROM recipe r
INNER JOIN rec_ing ON r.recid=rec_ing.recid 
INNER JOIN ingredient i ON i.ingid=rec_ing.ingid

Also note, over 25 years ago ANSI formalized a set of joins that you should try to adopt. 2 tips for this:

  1. deny yourself the use of commas between tables names in the from clause, and
  2. if a condition has two table references, this condition belongs to a JOIN e.g recipe.recid=rec_ing.recid (see? that has a table reference on each side of the equals, this is a join condition)

One last suggestion: a SQL query does not have to be a single row in PHP code.

DON is a valid scrabble word, transitive verb. 1 : to put on (an article of clothing) donned his hat and gloves. 2 : to wrap oneself in : take on sense 3a the donning of new and more tyrannous moralities— Edward Sapir. don. noun. Don definition, Mr.; Sir: a Spanish title prefixed to a man's given name. See more.

Welcome to Stackoverflow. You would need to add the RECIPE.recid='.$row['recid'] in the where clause.

However from my perspective you can simplify the logic using only one query. I would suggest to use inner joins to include the information from multiple tables.

SELECT *
FROM recipe AS r
INNER JOIN rec_ing AS ri ON ri.recid=r.recid
INNER JOIN INGREDIENT AS i ON i.ingid=ri.ingid;

What does DON mean? - DON Definition, Don definition, Mr.; Sir: a Spanish title prefixed to a man's given name. See more. Don, and dom, is derived from the Latin Dominus: a master of a household, a title with background from the Roman Republic in classical antiquity.

Don, don definition: 1. a lecturer (= a college teacher), especially at Oxford or Cambridge University in England 2. to…. Learn more. Dôn, in Celtic mythology, leader of one of two warring families of gods; according to one interpretation, the Children of Dôn were the powers of light, constantly in conflict with the Children of Llyr, the powers of darkness. In another view, the conflict was a struggle between indigenous gods and those of an invading people.

Don (2006 Hindi film), Directed by Farhan Akhtar. With Shah Rukh Khan, Priyanka Chopra, Arjun Rampal, Isha Koppikar. Vijay is recruited by a police officer to masquerade as his​  A don is a guy that everyone wants to be like. He is not only sexy and muscular but amazing in bed aswell. He can rock anyone. Being a don is a talent that not everyone can achieve.

Don – Is DON. Is Good., Give us your opinion about the film at http://www.flickbay.com/Movies/Dil-Chahta-​Hai/298 Duration: 2:26 Posted: 19 Jul 2009 Edward Don and Company is the world's leading distributor of foodservice equipment and supplies. Online ordering

Comments
  • Jonhasacat, you are my hero for today. Simple and brilliant solution. Danke.
  • Awesome, thanks for these great tips. I'll play, learn, and report back tomorrow. Much appreciated!
  • Thank you Emilio! My SQL is rusty, but this looks great. I will try it tomorrow and report back (or ask for more clarification). Much appreciated. I'm a new member but I've been a lurker for awhile - this is an amazing community and I hope to pay it forward.