How multiple query results to reduce the number of queries?

Question

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

There are three types of comments in my database, and I call them 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:

 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 a way to get the same output with one query instead of three of them?

Answer 1

PDO is a little more than everyone thinks. For example, it has a great function for you called PDO :: FETCH_GROUP .

Not to mention other small improvements that can make your code much shorter.

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

Anything you need.

here you first select the confirmed field, and then indicate the 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 do 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>"; } } 

Answer 2

Yes, you can use the IN condition:

 SELECT * FROM comments WHERE confirmed IN (0, 1, 2) 

You can then scroll through the results as many times as you want to create output.

Answer 3

You can use this query syntax:

 SELECT * FROM comments WHERE (confirmed >= 0 AND confirmed < 3 ) ORDER BY FIELD(confirmed,0,2,1) 

This way you have all the rows sorted by your desired result.

If the confirmed field allows only 0,1,2 values, you can sempliy as follows:

 SELECT * FROM comments ORDER BY FIELD(confirmed,0,2,1) 

Then:

 while( $row = $query->fetch(PDO::FETCH_ASSOC) ) { if ( $row['confirmed'] == 0 ) { (...) } elseif( $row['confirmed'] == 1 ) { (...) } else { (...) } } 

Answer 4

You can do: SELECT * FROM comments WHERE confirmed=1 OR confirmed=2 OR confirmed=0 Or use IN