Row count with PDO

by 
$sql = "SELECT count(*) FROM `table` WHERE foo = ?"; 
$result = $con->prepare($sql); 
$result->execute([$bar]); 
$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;

More Related Contents:

  1. Insert form in MySQL with PDO [duplicate]
  2. Undefined index $_POST
  3. How to check if a row exists in MySQL? (i.e. check if username or email exists in MySQL)
  4. PDO get the last ID inserted
  5. How do I use password hashing with PDO to make my code more secure? [closed]
  6. How to view query error in PDO PHP
  7. PDO::__construct(): Server sent charset (255) unknown to the client. Please, report to the developers
  8. Insert/update helper function using PDO
  9. PDO with INSERT INTO through prepared statements [closed]
  10. PDO::fetchAll vs. PDO::fetch in a loop
  11. PHP Connection failed: SQLSTATE[HY000] [2002] Connection refused
  12. Resetting array pointer in PDO results
  13. Real escape string and PDO [duplicate]
  14. PDO multiple queries
  15. PHP PDO vs normal mysql_connect
  16. Can I create a database using PDO in PHP?
  17. How to bind parameters to a raw DB query in Laravel that’s used on a model?
  18. PDO::PARAM for type decimal?
  19. PHP PDO and MySQLi [duplicate]
  20. PDO fetch one column from table into 1-dimensional array
  21. Update query with PDO and MySQL
  22. Is “SET CHARACTER SET utf8” necessary?
  23. Getting a PHP PDO connection from a mysql_connect()?
  24. Having issue with matching rows in the database
  25. Unknown database error in PHP but it exists in PHPMyAdmin
  26. Set PDO to throw exceptions by default [duplicate]
  27. PDO: MySQL server has gone away
  28. Checking for an empty result (PHP, PDO, and MySQL) [duplicate]
  29. PHP/SQL Insert Error when using Named Placeholders
  30. Using PDO Prepared Statement and Incrementing a column value

Leave a Comment