How to Get Total Rows of Query Results in PHP Using PDO

Getting Total Rows of Query Results Using PHP and PDO

In web development, it is often necessary to fetch data from a database and count the total number of rows returned by a query. PHP's PDO (PHP Data Objects) provides an efficient and secure way to achieve this. This article will guide you through the complete steps with example code.

Establishing a Database Connection

First, you need to connect to the database using PDO:

<?php
$dsn = 'mysql:host=localhost;dbname=test';
$username = 'root';
$password = '';

try {
    $pdo = new PDO($dsn, $username, $password);
    echo "Connected to the database successfully";
} catch (PDOException $e) {
    echo "Database connection failed: " . $e->getMessage();
}
?>

Executing an SQL Query

After connecting, you can write and execute an SQL query:

<?php
$sql = "SELECT * FROM users";
$stmt = $pdo->query($sql);

if ($stmt) {
    echo "Query executed successfully";
} else {
    echo "Query failed";
}
?>

Getting the Total Number of Rows

Once the query is executed, you can use PDO's rowCount() method to get the total number of rows:

<?php
$sql = "SELECT * FROM users";
$stmt = $pdo->query($sql);

if ($stmt) {
    $totalRows = $stmt->rowCount();
    echo "Total number of rows: " . $totalRows;
} else {
    echo "Query failed";
}
?>

Notes

Note that the rowCount() method may depend on the database driver when used with SELECT statements, and the result might not always be accurate. For INSERT, UPDATE, or DELETE statements, rowCount() returns the number of affected rows instead of the total rows of a query result.

Summary

Using PHP and PDO, you can easily obtain the total number of rows returned by a query. Simply connect to the database, execute the query, and call the rowCount() method. This approach is simple, efficient, and suitable for everyday development tasks.