Current Location: Home> Latest Articles> How to handle pagination query in combination with PDOStatement::rowCount

How to handle pagination query in combination with PDOStatement::rowCount

gitbox 2025-05-26

In PHP, using PDO (PHP Data Objects) for database operations is a very common way. Through the API provided by PDO, we can easily perform database connections, queries and operations. When conducting data queries, pagination query is a very common requirement. This article will introduce how to use PDOStatement::rowCount to implement the pagination query function.

What is PDOStatement::rowCount ?

In PDO, the PDOStatement::rowCount method returns the number of rows affected by the current SQL query. For SELECT queries, rowCount returns the number of rows in the query result, while for INSERT , UPDATE , or DELETE operations, it returns the number of rows affected.

For paging queries, we usually need to know the total number of records in the database that meet the query conditions in order to calculate the total number of pages. PDOStatement::rowCount is a simple way to get the total number of records.

Basic ideas for pagination query

The basic idea of ​​pagination query is: to control the record range of the query through LIMIT and OFFSET , and to use rowCount to obtain the total number of records that meet the conditions, thereby calculating the total number of pages. This can realize batch loading of data, reducing the overhead of loading large amounts of data at one time.

Common steps for pagination query are as follows:

  1. Get the total number of records : Query the total number of records that meet the conditions and use PDOStatement::rowCount to obtain the number of records.

  2. Calculate the total number of pages : Calculate the total number of pages based on the total number of records and the number of records displayed on each page.

  3. Get the current page data : Use LIMIT and OFFSET to obtain the current page data based on the current page number and the number of records displayed per page.

Sample code

Below is a simple paging query implementation example. Suppose we have a database table users that contains the basic information of the user. We will use PDOStatement::rowCount to get the total number of records and load the data through a pagination query.

 <?php
// Database connection settings
$host = 'localhost';
$dbname = 'your_database';
$username = 'root';
$password = '';

// create PDO Example
try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
    exit;
}

// Get the current page number，Default is the first page
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$limit = 10; // Displayed per page 10 Record
$offset = ($page - 1) * $limit; // Calculate offset

// Get the total number of records
$stmt = $pdo->query("SELECT COUNT(*) FROM users");
$totalRecords = $stmt->fetchColumn(); // use rowCount Get the total number of records
$totalPages = ceil($totalRecords / $limit); // Calculate the total number of pages

// Get the data of the current page
$stmt = $pdo->prepare("SELECT * FROM users LIMIT :limit OFFSET :offset");
$stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();

// Output data on the current page
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo 'ID: ' . $row['id'] . ' | Name: ' . $row['name'] . '<br>';
}

// Output paging information
echo "<br>Page $page of $totalPages<br>";

// Output paging link
if ($page > 1) {
    echo '<a href="?page=' . ($page - 1) . '">Previous</a> ';
}

if ($page < $totalPages) {
    echo '<a href="?page=' . ($page + 1) . '">Next</a>';
}
?>

Code parsing

  1. Database connection : We create database connections through PDO .

  2. Get the total number of records : First, query the total number of records in the database through SELECT COUNT(*) FROM users , and then use fetchColumn() to get the first field of the result (i.e., total number of records).

  3. Paging calculation : We calculate the offset $offset based on the current page number $page and the number of records displayed per page $limit . The total number of pages is calculated by the ceil() function.

  4. Query the data of the current page : Query the data of the current page through LIMIT and OFFSET .

  5. Page output : The paged link is output based on the total number of pages. Users can click on the link to jump to the previous page or the next page.

Things to note

  • Performance optimization : In the case of large data volumes, using rowCount to obtain the total number of records may affect performance. If the data table is very large, you can consider using other optimization solutions, such as querying only the required data and using cache.

  • SQL injection prevention : In the above code, we preprocess SQL queries through prepare and bindParam to ensure that the risk of SQL injection is avoided.

Related