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

Question

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)

Here's my PHP code:

<?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");
  foreach ($ingredient as $subrow) {
    $xml->startElement('ingredient');
    $xml->writeRaw($subrow['ingredient']);
    $xml->endElement();
    }
  $xml->endElement();
}
$xml->endElement();
$xml->endDocument();
$xml->flush();
?>

And here's what is returned:

<?xml version="1.0" encoding="UTF-8"?>
<recipes>
  <recipe>
    <title>Recipe #1</title>
    <ingredient>ingredient A</ingredient>
    <ingredient>ingredient C</ingredient>
    <ingredient>ingredient F</ingredient>
    <ingredient>ingredient G</ingredient>
    <ingredient>ingredient H</ingredient>
  </recipe>
  <recipe>
    <title>Recipe #2</title>
    <ingredient>ingredient A</ingredient>
    <ingredient>ingredient C</ingredient>
    <ingredient>ingredient F</ingredient>
    <ingredient>ingredient G</ingredient>
    <ingredient>ingredient H</ingredient>
  </recipe>
</recipes>

Answer 1

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.