title: mysql to json using php. Nested objects
body: <p>Good Afternoon,
  I am trying to get these results into arrays in PHP so that I can encode them into json objects and send them to the client. The results of the query look like this:</p>
  
  <pre><code>   id   name    hours   cat status
  3bf JFK Int 24  pass    open
  3bf JFK Int 24  std closed
  3bf JFK Int 24  exp open
  5t6 Ohm CA  18  pass    closed
  5t6 Ohm CA  18  std closed
  5t6 Ohm CA  18  std2    open
  5t6 Ohm CA  18  exp open
  ...
  </code></pre>
  
  <p>I would like for the json objects to look like this: </p>
  
  <pre><code>{ "id": "3bf", "name": "JFK Int", "cats":
      { [ { "cat": "pass", "status": "open" },
          { "cat": "std", "status": "closed" },
          { "cat": "exp", "status": "open" } ] }
  { "id": "5t6", "name": "Ohm CA", "cats":
      { [ { "cat": "pass", "status": "closed" },
          { "cat": "std", "status": "closed" },
          { "cat": "std2", "status": "open" } ],
          { "cat": "exp", "status": "open" } ] }
  </code></pre>
  
  <p>I have succesfully connected to mysql and exported using json_encode using flat tables but this part I do not know how to do in PHP. Thanks.</p>
  
  <p>This is the code that I have. This returns an array of json objects but it is flat, not nested:</p>
  
  <pre><code>$SQL = "SELECT id, name, hours, cat, status FROM bwt.vewPortCats";
  
  $result = mysql_query($SQL);
  
  $arr = array();
      while ($row = mysql_fetch_assoc($result)) {
          $arr[] = $row;}
  
  $json = json_encode($arr);
  
  echo $json;
  </code></pre>
  
  <p>The data itself is from a view that combines the tables ports and cats.</p>