title: Json from two mysql tables in PHP
body: <p>I am trying to build a json output from two related MySQL tables.
  I have a table of "Restaurants" and table "Dishes" each item in Restaurants table has several relative items in the Dishes table which are referenced by id. Each Restaurant item ID is a foreign key in the Dishes table as f_id.</p>
  
  <p>for example:
  Restaurants table</p>
  
  <pre><code>+----+-------------+-----------+
  | Id |    Name     | Misc Info |
  +----+-------------+-----------+
  |  1 | Restaurant1 | Some Info |
  +----+-------------+-----------+
  </code></pre>
  
  <p>Dishes table</p>
  
  <pre><code>+----+------+-----------+-------------+
  | Id | f_id |   dish    | description |
  +----+------+-----------+-------------+
  |  1 |    1 | DishName  | DishDesc.   |
  |  2 |    1 | DishName2 | DishDesc.   |
  +----+------+-----------+-------------+
  </code></pre>
  
  <p>I would like to create a JSON output from those to tables to look like this:</p>
  
  <pre><code>{
      "Restaurants": [
          {
              "name": "String content",
              "misc info": "String content"
              "Dishes": [
                  {
                      "dish": "String content",
                      "description": "String content"
  
                  },
                  {
                      "dish": "String content",
                      "description": "String content"
                  }
              ],
  
          },
          {
              "name": "String content",
              "misc info": "String content"
              "Dishes": [
                  {
                      "dish": "String content",
                      "description": "String content"
                  },
                  {
                      "dish": "String content",
                      "description": "String content"
                  }
              ],
  
          }
      ]
  }
  </code></pre>
  
  <p>I am using PHP and mysql_query methods to figure out the logic, I plan on using PDO int he production version. Here is the code I've tried so far.</p>
  
  <pre><code>//Create Our Query
  $srtResult = "SELECT * FROM Restaurants";
  
  //Execute Query
  $result=mysql_query($srtResult);
  //Iterate Throught The Results
  
  while ($row = mysql_fetch_assoc($result)) {
      $count = $row['id'];
      $srtResult2 = "SELECT * FROM Dishes WHERE id = $count";
      $result2 = mysql_query($srtResult2);
      while(mysql_num_rows($result2)){
          $dishes = mysql_fetch_row($result2);
          $dishList[] = Array(
              "dish" =&gt; $dishes[3],
              "description" =&gt; $dishes[4]);
      }
      $json['Restaurants'][] = 
          Array("Restaurants" =&gt; Array(
                  "name" =&gt; $row['name'], 
          "Dishes" =&gt; Array(
              $dishList)));
  }
  header('Content-type: application/json');
  echo json_encode($json);
  </code></pre>
  
  <p>The problem I have is the dishes do not iterate according to the current restaurant item, for each restaurant item, I am getting dishes from the first restaurant.
  I think the problem lies in the loop itself since I am getting different int for count in each Restaurant wrapper. Any help would be much appreciated, I've been working on this for several days already and have exhausted my basic PHP knowledge.</p>