Row count with PDO

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 as I may sometimes be dealing with large datasets, so not good for my use.

Any suggestions?

Pdo Row count error

I have been trying to use the row count function which is part of the PDO’s.. i am using the for each loop to go thru a list of broken parts and then count how many of each part i need to order but us

PDO – Get row count before limit applied

I have a pagination script which requires the row count(obviously) and the row count returned from my select statement which is limited gives only 20 (because the amount of rows returned is 20) but I

PDO fetchColumn not returning row count

I have a table in my database called posts it contains a row and yet when I do a count on it I’m not getting anything back, not 0 just empty. $numRows contains nothing at all when I echo it out. If yo

PDO::rowCount VS COUNT(*)

i have a query use PDO, count the row first, if row >1 than fetch data SELECT * WHERE id=:id $row=$SQL->rowCount(); if($row>0){ while($data=$SQL->fetch(PDO::FETCH_ASSOC)){… } } else{echo

PHP PDO statement returns incorrect row count with SELECT?

Why does this portion of code return true even when it shouldn’t be? $stmt = $dbh->prepare(SELECT COUNT(`user_id`) FROM `users` WHERE `username`= :username LIMIT 1); $stmt->bindParam(‘:usernam

PDO duplicate row

I’m looking for a way to duplicate this row using PDO. I don’t want to retype all the values. $strsql=INSERT INTO `dreamyAuctions`(`category`,`type`,`city`,`host`,`host_name`,`title`,`fee`,`base_fee`

Can’t return correct row count from database using php pdo

I am trying to return the row count from a table in my database but continue to get the wrong value. I need the row count to process subset values for pagination. There are 11 items in my table but I

MySQL PDO row counts [duplicate]

Possible Duplicate: PHP PDO – Num Rows I am working with PDO and MySQL, and I am fairly new to PDO. So Have two questions? What function do I use to count the number of rows found based on a select

select count, sum using PHP PDO

How can I get count(*) or SUM(col1) using PHP PDO I have used $STH_SELECT = $dbh->prepare(SELECT count(*) FROM Table); $STH_SELECT->execute; $result = $STH_SELECT->fetchAll; $Count = $resul

PHP, PDO Count on mysql

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

Answers

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

As I wrote previously in an answer to a similar question, the only reason mysql_num_rows() worked is because it was internally fetching all the rows to give you that information, even if it didn’t seem like it to you.

So in PDO, your options are:

  1. Use MySQL’s FOUND_ROWS() function.
  2. Use PDO’s fetch_all() function to fetch all the rows into an array, then use count() on it.
  3. Do an extra query to SELECT COUNT(*), as karim79 suggested.

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.

I ended up using this:

$result = $db->query($query)->fetchAll();

if (count($result) > 0) {
    foreach ($result as $row) {
        echo $row['blah'] . '<br />';
    }
} else {
    echo "<p>Nothing matched your query.</p>";
}

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. 🙂

when you make a COUNT(*) in your mysql statement like in

$q = $db->query("SELECT COUNT(*) FROM ...");

your mysql query is already counting the number of result why counting again in php? to get the result of your mysql

$q = $db->query("SELECT COUNT(*) as counted FROM ...");
$nb = $q->fetch(PDO::FETCH_OBJ);
$nb = $nb->counted;

and $nb will contain the integer you have counted with your mysql statement a bit long to write but fast to execute

Edit: sorry for the wrong post but as some example show query with count in, I was suggesting using the mysql result, but if you don’t use the count in sql fetchAll() is efficient, if you save the result in a variable you won’t loose a line.

$data = $dbh->query("SELECT * FROM ...");
$table = $data->fetchAll(PDO::FETCH_OBJ);

count($table) will return the number of row and you can still use the result after like $row = $table[0] or using a foreach

foreach($table as $row){
  print $row->id;
}

Too bad, this question confused too many people.

However, we have a confirmation from the OP that “he has already done a select query and now wants to know how many rows were returned.”

So, in fact there are two questions in one.

1. Should a developer select more rows than needed on the page?

No. LIMIT clause or similar operator have to be used to limit the number of rows returned.
If he needs no data but only count – he shouldn’t have selected the data at all, but te very count only. So, no function to get the number of rows ever applicable here.

2. How to count them rows returned?

Just count them. To get data into array using fetchAll() is the right way. While to get the count from an array is a piece of cake.

Therefore, we can conclude that

  • No dedicated function, be it mysql_num_rows or rowCount() ever needed: get your data first and you’ll be able to count it later.
  • Neither an extra query is a sane idea to get the count of rows you already have. This is why accepted answer ambiguous and deceiving. A COUNT(*) query have to be used only if you need the count, but not the data itself. But that’s totally different question.
  • for the usual case of the search query with pagination, you actually need two queries:
    • one to get the data for the particular page using LIMIT
    • one to get total count (can be disguised somehow, see SQL_CALC_FOUND_ROWS for example)

There is still very small chance for the case where both data and rowCount() will be actually needed. But it occurs too seldom to talk about. But yes, in such exceptional case when you need both too much data and a count – rowCount() have to be used, as it works with Mysql many years already.

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

A quick one liner to get the first entry returned. This is nice for very basic queries.

<?php
$count = current($db->query("select count(*) from table")->fetch());
?>

Reference

Uh ? Guys what about :

mysqli_result::$num_rows

$count = $mydb->query(‘SELECT * FROM yourTable’)->num_rows;

echo $count . ‘ rows in the select statement’;

No ? Or am I missing essence of the initial question/need ?

Also just taking a look on the Manual helps a lot :

http://php.net/mysqli

<pre>I tried $count = $stmt->rowCount(); with oracle11.2 and did not work.
 I decided to used a for loop as show below. 

   $count =  "";
    $stmt =  $conn->prepare($sql);
    $stmt->execute();
   echo "<table border='1'>/n";
   while($row = $stmt->fetch(PDO::FETCH_OBJ)) {
        $count++;
        echo "<tr>/n";
    foreach ($row as $item) {
    echo "<td class='td2'>".($item !== null ? htmlentities($item, ENT_QUOTES):"&nbsp;")."</td>/n";
        } //foreach ends
        }// while ends
        echo "</table>/n";
       //echo " no of rows : ". oci_num_rows($stmt);
       //equivalent in pdo::prepare statement
       echo "no.of rows :".$count;
    </pre>

This post is old but Getting row count in php with PDO is simple

$stmt = $db->query('SELECT * FROM table');
$row_count = $stmt->rowCount();

Answering this because I trapped myself with it by now knowing this and maybe it will be useful.

Keep in mind that you cant fetch results twice. You have to save fetch result into array, get row count by count($array), and output results with foreach. For example:

$query = "your_query_here";
$STH = $DBH->prepare($query);
$STH->execute();
$rows = $STH->fetchAll();
//all your results is in $rows array
$STH->setFetchMode(PDO::FETCH_ASSOC);           
if (count($rows) > 0) {             
    foreach ($rows as $row) {
        //output your rows
    }                       
}

For straight queries where I want a specific row, and want to know if it was found, I use something like:

function fetchSpecificRow(&$myRecord) {
    $myRecord = array();
    $myQuery = "some sql...";
    $stmt = $this->prepare($myQuery);
    $stmt->execute(array($parm1, $parm2, ...));
    if ($myRecord = $stmt->fetch(PDO::FETCH_ASSOC)) return 0;
    return $myErrNum;
}

Use parameter array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL), else show -1:

Usen parametro array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL), sin ello sale -1

example:

$res1 = $mdb2->prepare("SELECT clave FROM $tb WHERE id_usuario='$username' AND activo=1 and id_tipo_usuario='4'", array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
$res1->execute();

$count=$res1->rowCount();
echo $count;

You can combine the best method into one line or function, and have the new query auto-generated for you:

function getRowCount($q){ 
    global $db;
    return $db->query(preg_replace('/SELECT [A-Za-z,]+ FROM /i','SELECT count(*) FROM ',$q))->fetchColumn();
}

$numRows = getRowCount($query);
$qry = "select * from tabel";
$cmd = $conn->prepare($qry);
$cmd->execute();
$cmd->rowCount()

If you just want to get a count of rows (not the data) ie. using COUNT(*) in a prepared statement then all you need to do is retrieve the result and read the value:

$sql = "SELECT count(*) FROM `table` WHERE foo = bar";
$statement = $con->prepare($sql); 
$statement->execute(); 
$count = $statement->fetch(PDO::FETCH_NUM); // Return array indexed by column number
return reset($count); // Resets array cursor and returns first value (the count)

Actually retrieving all the rows (data) to perform a simple count is a waste of resources. If the result set is large your server may choke on it.

<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->fetchAll(PDO::FETCH_ASSOC);
     foreach($result as $row) {
        $i++;
    ?>  
      <tr>
         <td><?php echo $i; ?></td>
         <td><?php echo $row['name']; ?></td>
      </tr>
     <?php
          }
     ?>
     </tbody>
</table>