PHP and PDO: How to get the total number of rows in query results

PHP and PDO: How to get the total number of rows of query results

When developing web applications, we often need to retrieve data from the database and know the total number of rows of query results. By using the powerful database abstraction layer of PHP and PDO (PHP Data Objects), we can easily implement this function. This article will show you how to use PDO to get the total number of rows of a query result.

First, we need to establish a connection to the database. The following code snippet shows how to use PDO to connect to a MySQL database:

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

try {
    $pdo = new PDO($dsn, $username, $password);
    echo "成功连接到数据库";
} catch (PDOException $e) {
    echo "数据库连接失败: " . $e->getMessage();
}
?>

Once we have established a connection to the database, we can then write a SQL query and execute it. The following code snippet shows how to use PDO to execute a query statement:

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

if ($stmt) {
    echo "查询成功";
} else {
    echo "查询失败";
}
?>

Now, we have obtained a set of query results. Next, we need to calculate the total number of rows in the query results. To achieve this, we can use PDO's rowCount() method. The following code snippet shows how to get the total number of rows in the query result:

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

if ($stmt) {
    $totalRows = $stmt->rowCount();
    echo "查询结果的总行数为：" . $totalRows;
} else {
    echo "查询失败";
}
?>

In this example, we first executed the query statement and stored the result in the $stmt variable. Then, use the rowCount() method of the $stmt object to get the total number of rows in the query results and store it in the $totalRows variable. Finally, we print out the total number of rows.

It should be noted that the rowCount() method is only valid for SELECT statement execution. For INSERT, UPDATE, and DELETE statements, the rowCount() method will return the number of affected rows rather than the total number of rows in the query result.

To summarize, by using PHP and PDO, we can easily get the total number of rows of the query results. Simply use PDO's rowCount() method to achieve this functionality. Hope this article helps you!

The above is the detailed content of PHP and PDO: How to get the total number of rows in query results. For more information, please follow other related articles on the PHP Chinese website!