Use mysqli_stmt::prepare to implement pagination query

gitbox 2025-05-28

When developing PHP applications based on MySQL, in order to improve query efficiency and user experience of data presentation, we usually use paging queries to load data in segments. Compared to using the method of splicing SQL directly, using the mysqli_stmt::prepare function can not only effectively prevent SQL injection, but also improve the readability and maintenance of the code. This article will take you to learn more about how to implement pagination query through mysqli_stmt::prepare and parse it with examples.

1. Basic concepts

The core of pagination query lies in the LIMIT clause of SQL, whose syntax is:

 SELECT * FROM table_name LIMIT offset, page_size;

in:

offset : offset, indicating which record to read from.
page_size : The number of records displayed per page.

When using the mysqli_stmt::prepare function, since the parameters of the LIMIT clause cannot directly bind variables, we need to use some tricks to bypass this limitation.

2. Preparation

Make sure you are using the MySQLi extension and preprocessing statements are enabled.
The database connection has been established.
There is a data table for querying, such as users .

3. Implementation steps of pagination query

Step 1: Establish a database connection

 <?php
$mysqli = new mysqli("localhost", "username", "password", "database");
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}
?>

Step 2: Set the paging parameters

 <?php
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$page_size = 10;
$offset = ($page - 1) * $page_size;
?>

Step 3: Write preprocessing statements

Since variables in the LIMIT clause cannot be bound through bind_param , we can only construct SQL through string stitching. However, you can still use prepare to bind other conditional parameters:

 <?php
$sql = "SELECT id, name, email FROM users ORDER BY id DESC LIMIT ?, ?";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("ii", $offset, $page_size);
$stmt->execute();
$result = $stmt->get_result();
?>

Step 4: Output query results

 <?php
while ($row = $result->fetch_assoc()) {
    echo "<p>{$row['id']} - {$row['name']} - {$row['email']}</p>";
}
?>

Step 5: Pagination Navigation Links

Assuming the total number of records is the variable $total_rows , you can generate a paging link in the following way:

 <?php
$total_rows = 100; // Usually, you need to query separately to get the total number
$total_pages = ceil($total_rows / $page_size);

for ($i = 1; $i <= $total_pages; $i++) {
    echo "<a href='https://gitbox.net/userlist.php?page=$i'>1. $i Page</a> ";
}
?>

4. Complete sample code

 <?php
$mysqli = new mysqli("localhost", "username", "password", "database");
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$page_size = 10;
$offset = ($page - 1) * $page_size;

$sql = "SELECT id, name, email FROM users ORDER BY id DESC LIMIT ?, ?";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("ii", $offset, $page_size);
$stmt->execute();
$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
    echo "<p>{$row['id']} - {$row['name']} - {$row['email']}</p>";
}

$stmt->close();
$mysqli->close();
?>

5. Summary

The key to implementing pagination query using mysqli_stmt::prepare is to reasonably construct SQL and understand the limitations of parameter binding. Although the LIMIT parameter cannot use named placeholders, we can still use bind_param to bind numeric type parameters to achieve safe and reliable paging functions. In actual projects, this method is safer and easier to maintain than traditional string splicing. Combined with page navigation logic, you can build a complete paging data display system to improve the system's user experience and performance.

mysqli_stmt::prepare