Row count with PDO

Question

There are many conflicting statements around. What is the best way to row count using PDO in PHP? Before using PDO, I just simply used mysql_num_rows.

fetchAll is something I won't want because I may sometimes be dealing with large datasets, so not good for my use.

Do you have any suggestions?

Answer 1

function count_x($connect) { $query = " SELECT * FROM tbl WHERE id = '0' "; $statement = $connect->prepare($query); $statement->execute(); $total_rows = $statement->rowCount(); return $total_rows; }

Answer 2

This is an old post, but getting frustrated looking for alternatives. It is super unfortunate that PDO lacks this feature, especially as PHP and MySQL tend to go hand in hand.

There is an unfortunate flaw in using fetchColumn() as you can no longer use that result set (effectively) as the fetchColumn() moves the needle to the next row. So for example, if you have a result similar to

1. Fruit->Banana
2. Fruit->Apple
3. Fruit->Orange

If you use fetchColumn() you can find out that there are 3 fruits returned, but if you now loop through the result, you only have two columns, The price of fetchColumn() is the loss of the first column of results just to find out how many rows were returned. That leads to sloppy coding, and totally error ridden results if implemented.

So now, using fetchColumn() you have to implement and entirely new call and MySQL query just to get a fresh working result set. (which hopefully hasn't changed since your last query), I know, unlikely, but it can happen. Also, the overhead of dual queries on all row count validation. Which for this example is small, but parsing 2 million rows on a joined query, not a pleasant price to pay.

I love PHP and support everyone involved in its development as well as the community at large using PHP on a daily basis, but really hope this is addressed in future releases. This is 'really' my only complaint with PHP PDO, which otherwise is a great class.

Answer 3

$sql = "SELECT count(*) FROM `table` WHERE foo = bar"; 
$result = $con->prepare($sql); 
$result->execute(); 
$number_of_rows = $result->fetchColumn(); 

Not the most elegant way to do it, plus it involves an extra query.

PDO has PDOStatement::rowCount(), which apparently does not work in MySql. What a pain.

From the PDO Doc:

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.

EDIT: The above code example uses a prepared statement, which is in many cases is probably unnecessary for the purpose of counting rows, so:

$nRows = $pdo->query('select count(*) from blah')->fetchColumn(); 
echo $nRows;

Answer 4

This is super late, but I ran into the problem and I do this:

function countAll($table){
   $dbh = dbConnect();
   $sql = "select * from `$table`";

   $stmt = $dbh->prepare($sql);
    try { $stmt->execute();}
    catch(PDOException $e){echo $e->getMessage();}

return $stmt->rowCount();

It's really simple, and easy. :)

Answer 5

<table>
      <thead>
           <tr>
                <th>Sn.</th>
                <th>Name</th>
           </tr>
      </thead>
      <tbody>
<?php
     $i=0;
     $statement = $db->prepare("SELECT * FROM tbl_user ORDER BY name ASC");
     $statement->execute();
     $result = $statement->fetchColumn();
     foreach($result as $row) {
        $i++;
    ?>  
      <tr>
         <td><?php echo $i; ?></td>
         <td><?php echo $row['name']; ?></td>
      </tr>
     <?php
          }
     ?>
     </tbody>
</table>

Answer 6

Here's a custom-made extension of the PDO class, with a helper function to retrieve the number of rows included by the last query's "WHERE" criteria.

You may need to add more 'handlers', though, depending on what commands you use. Right now it only works for queries that use "FROM " or "UPDATE ".

class PDO_V extends PDO
{
    private $lastQuery = null;

    public function query($query)
    {
        $this->lastQuery = $query;    
        return parent::query($query);
    }
    public function getLastQueryRowCount()
    {
        $lastQuery = $this->lastQuery;
        $commandBeforeTableName = null;
        if (strpos($lastQuery, 'FROM') !== false)
            $commandBeforeTableName = 'FROM';
        if (strpos($lastQuery, 'UPDATE') !== false)
            $commandBeforeTableName = 'UPDATE';

        $after = substr($lastQuery, strpos($lastQuery, $commandBeforeTableName) + (strlen($commandBeforeTableName) + 1));
        $table = substr($after, 0, strpos($after, ' '));

        $wherePart = substr($lastQuery, strpos($lastQuery, 'WHERE'));

        $result = parent::query("SELECT COUNT(*) FROM $table " . $wherePart);
        if ($result == null)
            return 0;
        return $result->fetchColumn();
    }
}