Select the number of rows using PDO

1. Comments (1)

When you need to count rows that match some criteria in your database, under no circumstances you should select the actual rows and then use rowCount()!

Instead, you should always ask your database to count the rows and then return the only number, with a query like this:

SELECT count(1) FROM users
// or
SELECT count(*) FROM goods WHERE category_id = 1

(note that it doesn't matter which constant value to use as a count() function's parameter, be it *, 0 or 1 or anything - all works the same as long as it's a constant as opposed to the field name. In case of the latter, only values of this field that are not null will be counted).

Luckily, PDO has a dedicated function to get that single number right off the query, fetchColumn().

If no variable is going to be used in the query, we could neatly chain this function right to the query() call:

$count = $pdo->query("SELECT count(*) FROM table")->fetchColumn();

But if any variable is going to be used in the query, it should be always substituted with a parameter, and executed using a prepared statement:

$stmt = $pdo->prepare("SELECT count(*) FROM goods WHERE category_id = ?");
$stmt->execute([$category_id]);
$count = $stmt->fetchColumn();