title: Looping through MySQL left join in php vs. 2 separate queries
body: <p>I have a simple question and answer section on my website that allows comments. I currently populate the answers using a loop and a $_GET['id'] value. Here is my query</p>
  
  <pre><code>&lt;?php
  try{
     $parent=$_GET['id'];
     $post_type = "2";
     $stmt = $dbh-&gt;prepare(
      "SELECT p.post_id, p.content, p.author, p.created, pc.comment_id AS comment_id, pc.content AS comment_content
       FROM posts AS p
       LEFT JOIN posts_comments AS pc
       ON p.post_id = pc.parent_id
       WHERE p.post_type = :post_type AND  p.parent = :parent ");
  
     $stmt-&gt;bindParam(':parent', $parent, PDO::PARAM_INT);
     $stmt-&gt;bindParam(':post_type', $post_type, PDO::PARAM_INT);
     $stmt-&gt;execute();
  
     $answers_array = array();
     while ($answers = $stmt-&gt;fetch(PDO::FETCH_ASSOC)) {
          $answers_array[]= $answers;
     }
  }
  
  ?&gt;
  </code></pre>
  
  <p>This returns the following results in an array </p>
  
  <pre><code>   Array ( 
  [0] =&gt; Array ( 
          [post_id] =&gt; 12 
          [content] =&gt; I have an answer
          [author] =&gt; Author1
          [created] =&gt; 2012-06-09 21:43:56
          [comment_id] =&gt;
          [comment_content] =&gt; ) 
  [1] =&gt; Array ( 
          [post_id] =&gt; 13
          [content] =&gt; My second answer
          [author] =&gt; Author1
          [created] =&gt; 2012-06-10 06:30:58
          [comment_id] =&gt; 35
          [comment_content] =&gt; 1st comment ) 
  [2] =&gt; Array ( 
          [post_id] =&gt; 13
          [content] =&gt; My second answer
          [author] =&gt; Author2
          [created] =&gt; 2012-06-10 06:30:58
          [comment_id] =&gt; 36
          [comment_content] =&gt; 2nd comment ) 
  [3] =&gt; Array ( 
          [post_id] =&gt; 13
          [content] =&gt; My second answer
          [author] =&gt; Author2 
          [created] =&gt; 2012-06-10 06:30:58
          [comment_id] =&gt; 37
          [comment_content] =&gt; 3rd comment ) 
  )
  </code></pre>
  
  <p>On my question.php page I know I will need to loop through these results with something like </p>
  
  <pre><code>&lt;?php $answer_count = count($answers_array);
   for($x = 0; $x &lt; $answer_count; $x++) {
   $answers = $answers_array[$x];
  }
  ?&gt;
  </code></pre>
  
  <p>My question -</p>
  
  <p>I don't know whether to use two separate queries to pull the comments associated with each answer or use the join I have above and build another array with php. I don't know how to do that honestly. If I use the join I would like an array that looks like this but I'm not sure how to build it using a loop in php. </p>
  
  <pre><code>     Array ( 
  [0] =&gt; Array ( 
          [post_id] =&gt; 12 
          [content] =&gt; I have an answer
          [author] =&gt; Author1 
          [created] =&gt; 2012-06-09 21:43:56 
          [comment_id] =&gt; 
          [comment_content] =&gt; ) 
  [1] =&gt; Array ( 
          [post_id] =&gt; 13 
          [content] =&gt; My second answer
          [author] =&gt; Author1 
          [created] =&gt; 2012-06-10 06:30:58 
              Array( 
                     [1] =&gt; Array (
                         [comment_id] =&gt; 35
                         [comment_content] =&gt; 1st comment)
                     [2] =&gt; Array (
                         [comment_id] =&gt; 36
                         [comment_content] =&gt; 2nd comment)
                     [3] =&gt; Array (
                         [comment_id] =&gt; 37
                         [comment_content] =&gt; 3rd comment))
  </code></pre>
  
  <p>Once I have an array like that I was thinking I could do a for each inside my original php loop on the question.php page.</p>