How to multiple query results in order to reduce the query number?

Question

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?

Answer 1

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

Is all the code you need.

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