php - 循环通过 MySQL 左加入 php 与 2 个单独的查询

Question

我的网站上有一个简单的问答部分，允许发表评论。我目前使用循环和 $_GET['id'] 值填充答案。这是我的查询

<?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;
   }
}

?>

这将在数组中返回以下结果

   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 ) 
)

在我的 question.php 页面上，我知道我需要使用类似

的方式遍历这些结果

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

我的问题-

我不知道是使用两个单独的查询来提取与每个答案相关的评论，还是使用上面的连接并使用 php 构建另一个数组。我不知道如何诚实地做到这一点。如果我使用连接，我想要一个看起来像这样的数组，但我不确定如何使用 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))

一旦我有了这样的数组，我想我可以在 question.php 页面上的原始 php 循环中为每个做一个。

Answer 1

一个查询就可以了。正如你所拥有的，可能是更好的选项。你必须弄清楚哪个更有效率，让 MySQL 承担压力，或者让网络和 PHP 承担压力。让 PHP 承担压力比 MySQL 好得多，但是如果 MySQL 具有“内置”功能，例如您想要的分组，那么就离开 MySQL 并节省网络流量。

要使其正常工作:将“ORDER BY p.post_id, pc.comment_id”添加到您的查询中 - 这会按顺序获得结果。

那么，如果一定要构建成数组(虽然也可以不使用数组直接处理，但方法类似):

$lastPostID = 0;
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    if ($lastPostID <> $row['post_id']) {
        $lastPostID  = $row['post_id'];
        $answers[$lastPostID] = array('post_id' => $row['post_id'],
                                      'author_id' => $row['author_id'],
                                      etc
                                      'comments' => array() );
    }
    $answers[$lastPostID]['comments'][] = array('comment_id' => $row['comment_id'], 'coment' => $row['comment'] etc);
}