如何实现多个查询结果以减少查询次数? [英] How to multiple query results in order to reduce the query number?

问题描述

我想根据评论类型列出我的数据库中的评论.

I want to list comments from my database depending on their type.

我的数据库中有三种类型的评论，我用三种不同的查询来调用它们.

There are three types of comments in my database and I call them with three different queries.

//01 - Awaiting Comments
  $query = $handler->prepare("SELECT * FROM comments WHERE confirmed = 0");

  $query->execute();
  $r = $query->fetchAll(PDO::FETCH_ASSOC);
    echo "<h1>Awaiting Comments</h1>";
    foreach($r as $r_)  {
    echo "<li>r_[title]</li>";
  }
//02 - Comments waiting for confirmation
  $query = $handler->prepare("SELECT * FROM comments WHERE confirmed = 2");

  $query->execute();
  $r = $query->fetchAll(PDO::FETCH_ASSOC);
    echo "<h1>Comments waiting for confirmation</h1>";
    foreach($r as $r_)  {
    echo "<li>r_[title]</li>";
  }

//03 - Confirmed comments
  $query = $handler->prepare("SELECT * FROM comments WHERE confirmed = 1");

  $query->execute();
  $r = $query->fetchAll(PDO::FETCH_ASSOC);
    echo "<h1>Confirmed Comments</h1>";
    foreach($r as $r_)  {
    echo "<li>r_[title]</li>";
  }  

使用我当前的代码，我得到了我想要的输出:

With my current code i get the output i want like that:

Awaiting Comments
-comment 1
-comment 8
-comment 5

Comments waiting confirmation
-comment 9
-comment 4
-comment 2

Confirmed Comments
-comment 3
-comment 6
-comment 7

有没有办法用单个查询而不是三个查询来获得相同的输出?

Is there any way to get same output with single query instead of three of them?

推荐答案

PDO 比大家想象的要多一些.例如，它为您提供了一个很棒的功能，称为 PDO::FETCH_GROUP.

PDO is a bit more than everyone thinks it is. For example, it has a magnificent feature for you, called PDO::FETCH_GROUP.

更不用说其他可以显着缩短代码的小改进了.

Not to mention other little improvements that can make your code dramatically shorter.

$r = $handler->query("SELECT confirmed, c.* FROM comments c")->fetchAll(PDO::FETCH_GROUP);

是您需要的所有代码.

在这里，您首先选择 confirmed 字段，然后告诉 PDO 根据其值对结果进行分组(或相乘").

here you are selecting the confirmed field first and then tell PDO to group (or "multiply") the results based on its value.

现在您可以随时随地打印您的评论

And now you can print your comments wherever you want

// Awaiting Comments
foreach($r[0] as $r_) {
    echo "<li>$r_[title]</li>";
}

// Confirmed comments
foreach($r[2] as $r_) {
    echo "<li>$r_[title]</li>";
}

或者，让它在一个循环中

Or, to make it in one loop

$titles = [
    0 => 'Awaiting Comments',
    2 => 'Comments waiting confirmation',
    1 => 'Confirmed Comments',
];

foreach ($titles as $code => $title)
{
    echo "<h3>$title</h3>";
    foreach($r[$code] as $r_) {
        echo "<li>$r_[title]</li>";
    }
}

这篇关于如何实现多个查询结果以减少查询次数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！