Looping through MySQL left join in php vs. 2 separate queries

Programming in mysql: Looping through MySQL left join in php vs. 2 separate queries on newest questions tagged mysql – Stack Overflow

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

<?php
try{
   $parent=$_GET['id'];
   $post_type = "2";
   $stmt = $dbh->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->bindParam(':parent', $parent, PDO::PARAM_INT);
   $stmt->bindParam(':post_type', $post_type, PDO::PARAM_INT);
   $stmt->execute();

   $answers_array = array();
   while ($answers = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $answers_array[]= $answers;
   }
}

?>

This returns the following results in an array

   Array (
[0] => Array (
        [post_id] => 12
        [content] => I have an answer
        [author] => Author1
        [created] => 2012-06-09 21:43:56
        [comment_id] =>
        [comment_content] => )
[1] => Array (
        [post_id] => 13
        [content] => My second answer
        [author] => Author1
        [created] => 2012-06-10 06:30:58
        [comment_id] => 35
        [comment_content] => 1st comment )
[2] => Array (
        [post_id] => 13
        [content] => My second answer
        [author] => Author2
        [created] => 2012-06-10 06:30:58
        [comment_id] => 36
        [comment_content] => 2nd comment )
[3] => Array (
        [post_id] => 13
        [content] => My second answer
        [author] => Author2
        [created] => 2012-06-10 06:30:58
        [comment_id] => 37
        [comment_content] => 3rd comment )
)

On my question.php page I know I will need to loop through these results with something like

<?php $answer_count = count($answers_array);
 for($x = 0; $x < $answer_count; $x++) {
 $answers = $answers_array[$x];
}
?>

My question -

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.

     Array (
[0] => Array (
        [post_id] => 12
        [content] => I have an answer
        [author] => Author1
        [created] => 2012-06-09 21:43:56
        [comment_id] =>
        [comment_content] => )
[1] => Array (
        [post_id] => 13
        [content] => My second answer
        [author] => Author1
        [created] => 2012-06-10 06:30:58
            Array(
                   [1] => Array (
                       [comment_id] => 35
                       [comment_content] => 1st comment)
                   [2] => Array (
                       [comment_id] => 36
                       [comment_content] => 2nd comment)
                   [3] => Array (
                       [comment_id] => 37
                       [comment_content] => 3rd comment))

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.

source: http://stackoverflow.com/questions/11165597/looping-through-mysql-left-join-in-php-vs-2-separate-queries
Programming in mysql: programming-in-mysql