How to count the rows in a table
Short answer
A COUNT(*) query returns one value — fetchColumn() fetches it:
<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE products (id INTEGER PRIMARY KEY, title TEXT, price INTEGER)');
$pdo->exec("INSERT INTO products (title, price) VALUES ('Tea', 250), ('Coffee', 400), ('Juice', 180), ('Green tea', 300)");
echo (int) $pdo->query('SELECT COUNT(*) FROM products')->fetchColumn();Output:
4By a condition
With values from a user — a prepared statement:
<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE products (id INTEGER PRIMARY KEY, title TEXT, price INTEGER)');
$pdo->exec("INSERT INTO products (title, price) VALUES ('Tea', 250), ('Coffee', 400), ('Juice', 180), ('Green tea', 300)");
$statement = $pdo->prepare('SELECT COUNT(*) FROM products WHERE price > ?');
$statement->execute([200]);
echo (int) $statement->fetchColumn();Output:
3Do not count in PHP
count($pdo->query('SELECT * FROM products')->fetchAll()) loads the whole table into memory for one number. The database should do the counting.
Functions used in this recipe
- PDO — A database connection.
- PDOStatement — A prepared statement and its result.
- intval — Converts a value to an integer.