CODEWITHSUNDEEP
phpmysqlcountpdo
samyb8
samyb8

Reputation: 2598

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 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!

Upvotes: 0

Views: 3150

Answers (7)

Imran Durrani
Imran Durrani

Reputation: 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.

Upvotes: -1

AnotherOther
AnotherOther

Reputation: 107

This works for me:

$my_query = $db->query('SELECT COUNT(*) AS Count FROM the_table');
$c = $my_query->fetch(PDO::FETCH_OBJ);

return $c->Count;

Upvotes: -1

SupaMonkey
SupaMonkey

Reputation: 884

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

Upvotes: 1

N.B.
N.B.

Reputation: 14091

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.

Upvotes: 0

FirmView
FirmView

Reputation: 3150

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

Upvotes: 2

Explosion Pills
Explosion Pills

Reputation: 191819

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.

Upvotes: 1

deceze
deceze

Reputation: 522626

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.

Upvotes: 7

Related Questions