title: One to many relationship in MySQL, Selecting "child" table information
body: <p>I have written some PHP scripts to make pulling information out of a series of MySQL tables easier, however there are severe performance problems.  As a result I'm trying to reduce the number of queries.</p>
  
  <p>The tables have a one to many relationship:
  Parent Table (parent_id, first, last, phone)
  Child Table (child_id, parent_id, date, request, details)</p>
  
  <p>So, for any entry in the parent table there may be multiple associated rows in the child table.  How, can I select a row in the parent table and use the parent_id (primary key) to pull all the associated rows from the child table?</p>
  
  <pre><code>SELECT * FROM `Parent Table` WHERE `parent_id` = 5 
  </code></pre>
  
  <p>...and then...</p>
  
  <pre><code>SELECT * FROM `Child Table` WHERE `parent_id` = '5'
  </code></pre>
  
  <p>I want to then take the result and put it into an associative array, and json_encode to return as JSON.</p>
  
  <p>To clarify, I already have this working, but it's doing an additional query for each row selected, so instead of one query it is doing 101 for 100 rows.</p>
  
  <p>Any help would be greatly appreciated, thanks in advance.</p>
  
  <p><strong>UPDATE:</strong> It has been suggested to use Joins, however a new problem arises</p>
  
  <p>Sample data:
  Parent table (1, 'albert', 'smith', '12345')
  Child table (1, 1, 2010-10-5, 'test', 'etc etc'), (2, 1, 2010-10-6, 'again', 'eg eg')</p>
  
  <p>Doing a join results in two rows</p>
  
  <blockquote>
    <p>1, albert, smith, 12345, 1, 1, 2010-10-5, test, etc etc</p>
    
    <p>1, albert, smith, 12345, 2, 1, 2010-10-6, again, eg eg</p>
  </blockquote>
  
  <p>So, I have two rows with the parent table information duplicated.  To use joins I need some way of cleaning this up, and putting it into a hierarchical form.</p>
  
  <p>The result should be 
      <code>{parent_id:1, first:albert, last:smith, phone:12345, child_table:[{child_id:1,        date:2010-10-5, request:test, details:'etc etc'},{child_id:2, date:2010-10-6,   request:again, details:'eg eg'}]}</code></p>
  
  <p><strong>SOLUTION:</strong> So, the answer for me is to use joins, and write a function to convert the returned rows into an associative array</p>
  
  <p><strong>mysqlResult</strong> is an associative array from a mysql_query call, 
  <strong>parent_key</strong> is the name of the primary key of the parent table,
  <strong>child_key</strong> is the name of the primary key of the child table,
  <strong>child_table</strong> is the name of the child table,
  <strong>child_fields</strong> is an associative array of the names of all the fields in the child table</p>
  
  <pre><code>   function cleanJoin($mysqlResult, $parent_key, $child_key, $child_table, $child_fields)
      {
      $last_parent = 0;
      $last_child = 0;
      $ch_ctr = 0;
  
      for ($i = 0; $i &lt; count($mysqlResult); $i++)
      {
          if ($mysqlResult[$i][$child_key] != $last_child)
          {
              echo "new child!";
              $pr_ctr = count($answer[$i]);
              foreach ($child_fields as $field =&gt; $type)
              {
                 $answer[$pr_ctr][$child_table][$ch_ctr][$field] = $mysqlResult[$i][$field];
                 unset($mysqlResult[$field]);
              }
              $ch_ctr++;
          }
          if ($mysqlResult[$i][$parent_key] != $last_parent)
          {
              foreach($mysqlResult[$i] as $field =&gt; $value)
              {
                  $answer[$i][$field] = $value;
              }
          }
          $last_parent = $mysqlResult[$i][$parent_key];
          $last_child = $mysqlResult[$i][$child_key];
      }
  
      return $answer;
  }
  </code></pre>