在PHP中通过MySQL左联接循环与2个单独的查询 [英] Looping through MySQL left join in php vs. 2 separate queries

查看:70
本文介绍了在PHP中通过MySQL左联接循环与2个单独的查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

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

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

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

我的问题-

我不知道是使用两个单独的查询来提取与每个答案相关的注释,还是使用上面的连接并使用php构建另一个数组.我真不知道该怎么做.如果我使用联接,我想要一个看起来像这样的数组,但是我不确定如何使用php中的循环来构建它.

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

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

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.

推荐答案

一个查询就可以了.有了它,也许是更好的选择器.您必须找出更有效的方法,以便让MySQL承受压力,或者让网络和PHP承受压力.让PHP比MySQL承受压力要好得多,但是MySQL具有内置"功能,例如您想要的分组,然后离开MySQL并节省网络流量.

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.

要执行此操作,请执行以下操作:在查询中添加"ORDER BY p.post_id,pc.comment_id"-这将按顺序获取结果.

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

然后,如果您必须构建到数组中(尽管您无需使用数组就可以直接进行处理,则方法将类似):

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

这篇关于在PHP中通过MySQL左联接循环与2个单独的查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆