PHP, PDO Count on mysql

Question

I am trying to get a count of items with PDO (on a MySql table). I read somewhere that the rowCount does not work on MySql. Is this correct?

So far I definitely can't get it to work as I keep getting count=0.

Could anyone give me an idea so I can avoid going back to the db every time? I have multiple queries that look similar to this one:

    $items = $con -> prepare("SELECT * FROM item_descr ORDER BY $sortBy DESC");
    $count = $items -> rowCount();
    $items -> execute();
while($info = $items->fetch(PDO::FETCH_ASSOC)) { ... }

I want to try to avoid an extra query with SELECT COUNT (*)

Thanks!

deceze · Accepted Answer · 2012-08-23 15:24:57Z

7

You need to first execute the query. Only then will the database do its work and only then can you get a count of the found results.

answered Aug 23, 2012 at 15:24

deceze♦

525k89 gold badges806 silver badges954 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

FirmView · Accepted Answer · 2012-08-23 15:27:50Z

2

As, @deceze indicated,

$items = $con -> prepare("SELECT * FROM item_descr ORDER BY $sortBy DESC");
$items -> execute();
$count = $items -> rowCount();
while($info = $items->fetch(PDO::FETCH_ASSOC)) { ... }

answered Aug 23, 2012 at 15:27

FirmView

3,1508 gold badges36 silver badges50 bronze badges

Comments

Explosion Pills · Accepted Answer · 2012-08-23 15:25:26Z

1

rowCount only works after execute. I have never had a problem with rowCount in MySQL.

Using rowCount is nice, but you could also use your own counter variable if you're going to iterate over the results anyway.

answered Aug 23, 2012 at 15:25

Explosion Pills

192k56 gold badges341 silver badges417 bronze badges

Comments

SupaMonkey · Accepted Answer · 2012-10-30 20:30:05Z

Just to add this is all, please refer to the PHP documentation:

http://www.php.net/manual/en/pdostatement.rowcount.php

Particularly:

For most databases, PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement. Instead, use PDO::query() to issue a SELECT COUNT(*) statement with the same predicates as your intended SELECT statement, then use PDOStatement::fetchColumn() to retrieve the number of rows that will be returned. Your application can then perform the correct action.

And an example from said page:

<?php
$sql = "SELECT COUNT(*) FROM fruit WHERE calories > 100";
if ($res = $conn->query($sql)) {

    /* Check the number of rows that match the SELECT statement */
  if ($res->fetchColumn() > 0) {

        /* Issue the real SELECT statement and work with the results */
         $sql = "SELECT name FROM fruit WHERE calories > 100";
       foreach ($conn->query($sql) as $row) {
           print "Name: " .  $row['NAME'] . "\n";
         }
    }
    /* No rows matched -- do something else */
  else {
      print "No rows matched the query.";
    }
}

$res = null;
$conn = null;
?>

N.B. · Accepted Answer · 2012-08-23 15:32:26Z

0

You can simplify your code so much if you are using PDO, such as:

$items = $conn->query("SELECT * FROM item_descr ORDER BY $sortBy DESC")->fetchAll(PDO::FETCH_ASSOC);

if(count($items))
{
    // You can count the array to see how many records you got and you can iterate trough it using foreach / for loops 
}
else 
{
    // 0 records returned
}

There is no need for prepared statements in this particular case or checking whether you got any rows back using rowCount. It is true that rowCount CAN fail even with MySQL, it all depends whether you are using unbuffered queries or not.

answered Aug 23, 2012 at 15:32

N.B.

14.1k3 gold badges47 silver badges56 bronze badges

2 Comments

deceze Over a year ago

Using a one-liner such as that means you're pumping all the data immediately into a PHP array and therefore into memory all at once. That may be a huge performance problem, depending on the amount of data.

N.B. Over a year ago

I didn't say it's good or bad, it's based on what OP asked - sure, it definitely IS a potential hole, however it's due to the query itself and not underlying PHP code.

Imran Durrani · Accepted Answer · 2014-06-16 07:18:09Z

-1

PDOStatement::rowCount() returns the number of rows only affected by a DELETE, INSERT, or UPDATE statement.

For most databases, PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement. Instead, use PDO::query() to issue a SELECT COUNT(*) statement with the same predicates as your intended SELECT statement, then use PDOStatement::fetchColumn() to retrieve the number of rows that will be returned. Your application can then perform the correct action.

answered Jun 16, 2014 at 7:18

Imran Durrani

11 bronze badge

Collectives™ on Stack Overflow

PHP, PDO Count on mysql

6 Answers 6

Comments

Comments

Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related