Select the number of rows using PDO
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();
Related articles:
- SELECT query with PDO
- INSERT query using PDO
- UPDATE query using PDO
- How to connect to MySQL using PDO
- PDO Examples
- Authenticating a user using PDO and password_verify()
- How to check if email exists in the database?
- How to create a WHERE clause for PDO dynamically
- DELETE query using PDO
- How to create a prepared statement for UPDATE query
- Getting a nested array when multiple rows are linked to a single entry
- How to execute 1000s INSERT/UPDATE queries with PDO?
- Adding a field name in the ORDER BY clause based on the user's choice
- INSERT helper function for PDO Mysql
- PDO Examples
- PDO Examples
Add a comment
Please refrain from sending spam or advertising of any sort.
Messages with hyperlinks will be pending for moderator's review.
Markdown is now supported:
>
before and an empty line after for a quote