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

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

我的网站上有一个简单的问答部分，允许评论。我目前使用循环和$ _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;
   }
}

?>

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

在我的question.php页面上，我知道我需要用类似的东西来遍历这些结果

<?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.

我不知道是否使用两个单独的查询来提取与每个答案相关的注释或使用我上面的连接并使用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))

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.

一旦我有一个类似的数组，我想我可以在question.php页面上的原始php循环中为每个进行。

3 个解决方案

#1

One query is fine. As you have it, and probably the better opton. You have to work out which is more efficient, to let MySQL take the strain, or the network and PHP take the strain. It's a lot better to let PHP take the strain than MySQL, but where MySQL has "inbuilt" features, such as the grouping you desire, then leave the MySQL and save the network traffic.

一个查询很好。就像你拥有它，也许是更好的opton。你必须找出更高效的方法，让MySQL承受压力，或者让网络和PHP承受压力。让PHP承受压力比使用MySQL要好得多，但是MySQL具有“内置”功能，例如你想要的分组，然后离开MySQL并节省网络流量。

To make this work: add "ORDER BY p.post_id, pc.comment_id" to your query - this gets the results in order.

要使其工作：在查询中添加“ORDER BY p.post_id，pc.comment_id” - 这将按顺序获取结果。

Then, if you must build into an array (although you may be able to process directly without using an array, the method would be similar):

然后，如果你必须构建一个数组（虽然你可以直接处理而不使用数组，方法将类似）：

$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);
}

#2

The choice is yours. Each has their advantages and drawbacks. Using a single query, (obviously, single database call and) you only need to loop over your data set once, but you have the potential of returning duplicated data. Using multiple queries, you have only pull back exactly the data you need, but (obviously, multiple database calls and) you have to iterate over multiple sets of data.

这是你的选择。每个都有它们的优点和缺点。使用单个查询（显然，单个数据库调用），您只需要遍历数据集一次，但您有可能返回重复数据。使用多个查询，您只需准确地提取所需的数据，但（显然，多个数据库调用和）您必须迭代多组数据。

Below is a dirty implementation of the single query method (same basic flow applies to the multi-query method except comment building is it's own loop). But the basic idea is there. You have a map of your answers, adding/retrieving them as you iterate the records, and appending the comments.

下面是单个查询方法的脏实现（相同的基本流程适用于多查询方法，除了注释构建是它自己的循环）。但基本的想法是存在的。您有一个答案地图，在迭代记录时添加/检索它们，并附加注释。

while ($answers = $stmt->fetch(PDO::FETCH_ASSOC)) 
{
    $post_id = strval($answers['post_id']);
    if(!array_key_exists($post_id, $answers_array)) {
        $answers_array[$post_id] = array(
            'post_id' => $answers['post_id'],
            // ... assign other stuff ...
            'comments' => array()); //initialize the comments array
    }
    if(!empty($answers_array['comment_id'])) {
        $obj = $answers_array[$post_id];
        $obj['comments'][] = array(
            'comment_id' => $answers['comment_id'], 
            'comment_content' => $answers['comment_content']);
    }
}

#3

You'll want to use a JOIN to return all the results with a single query, otherwise, you'll be making multiple queries, one for each post. You'd have a lot of overhead by issuing many separate queries.

您将希望使用JOIN通过单个查询返回所有结果，否则，您将进行多个查询，每个帖子一个。通过发出许多单独的查询，您将获得大量开销。

An exception would be if you wanted to return a very small number of post results.

如果您想返回非常少量的帖子结果，则会有例外。

Be sure to ORDER the query by post_id, comment_id.

请务必通过post_id，comment_id对查询进行ORDER。

To iterate through the combined results, it would look something like this:

要迭代组合结果，它看起来像这样：

$post_id = 0;
foreach($rows as $row) {
  // See if our post changed
  if ($post_id != $row['post_id']) {
    // Display the post and post header
    ..
    // Update the current post_id variable
    $post_id = $row['post_id'];
  }
  // Display the comment
}