CODEWITHSUNDEEP
phpmysqlpdo
LetsSeo
LetsSeo

Reputation: 875

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?

Upvotes: 7

Views: 3212

Answers (4)

Your Common Sense
Your Common Sense

Reputation: 157914

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

Upvotes: 23

fusion3k
fusion3k

Reputation: 11689

You can use this query syntax:

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

In this way you have all rows sorted by your desired output.

If the confirmed field allows only 0,1,2 values, you can sempliy in this way:

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
    {
        (...)
    }
}

Upvotes: 2

Carbos
Carbos

Reputation: 117

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

Upvotes: 0

Jay Blanchard
Jay Blanchard

Reputation: 34426

Yes, you can use and IN condition:

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

Then you can loop through the results as many times as you like to create the output.

Upvotes: 1

Related Questions