English
English
When using PHP to manipulate MySQL databases, we usually use the mysqli extension to execute multiple SQL statements. In this multi-statement query scenario, next_result() is a very critical function that allows developers to gradually access the result set of each query. In addition to this basic function, next_result() can also help us implement advanced functions such as paging and sorting in some complex scenarios.
This article will combine actual code examples to show how to use next_result() in pagination and sorting, especially when we want to complete multiple data fragments through a single request.
next_result() is a method in mysqli object that is used to process query results containing multiple statements. When executing multiple SQLs, the first result set is obtained through store_result() or use_result() , and next_result() is used to move to the next result set.
Basic syntax:
$mysqli->next_result();
In most applications, paging and sorting are usually done by sending a SQL query with LIMIT and ORDER BY to the database. But sometimes, in order to improve performance or reduce the number of interactions between clients and databases, we may want to send multiple queries at once and get multiple paged data blocks or different sorted results at once. This is exactly where next_result() can come in handy.
Let's take a user list as an example, suppose we want to get:
Data for the current page (with sort);
Total records (for pagination navigation).
<?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;
$pageSize = 10;
$offset = ($page - 1) * $pageSize;
$sortColumn = isset($_GET['sort']) ? $_GET['sort'] : 'created_at';
$sortOrder = (isset($_GET['order']) && strtolower($_GET['order']) === 'asc') ? 'ASC' : 'DESC';
// NoticeSQLinjection，Simplify processing here
$sql = "
SELECT id, username, email, created_at
FROM users
ORDER BY $sortColumn $sortOrder
LIMIT $offset, $pageSize;
SELECT COUNT(*) as total FROM users;
";
if ($mysqli->multi_query($sql)) {
// The first result set：Paginated data
if ($result = $mysqli->store_result()) {
echo "<h3>Current page user list</h3><ul>";
while ($row = $result->fetch_assoc()) {
echo "<li>{$row['username']} ({$row['email']})</li>";
}
echo "</ul>";
$result->free();
}
// Move to the next result set：Total records
if ($mysqli->next_result()) {
if ($countResult = $mysqli->store_result()) {
$countRow = $countResult->fetch_assoc();
$total = $countRow['total'];
$countResult->free();
$totalPages = ceil($total / $pageSize);
echo "<p>common {$totalPages} Page，Total records：{$total}</p>";
}
}
} else {
echo "Query failed: " . $mysqli->error;
}
$mysqli->close();
?>
Multi_query() is used to execute multiple SQLs;
Use store_result() to get each result set;
Use next_result() to move to the next result set;
Dynamically support sorting fields and sorting methods.
Suppose we want to get both a "Sorted by Creation Time" and a "Sorted by Username" record, we can write this:
$sql = "
SELECT id, username, email FROM users ORDER BY created_at DESC LIMIT 0,10;
SELECT id, username, email FROM users ORDER BY username ASC LIMIT 0,10;
";
Next, use next_result() to switch each piece of data.
Avoid SQL injection: pagination and sorting parameters need to be strictly filtered;
Before using it, determine whether there are more result sets;
If your database layer is encapsulated (such as using ORM), you may need to manually execute the underlying SQL to use next_result() .
Although next_result() is usually used to process multi-statement query results, using it in compound requests for paging and sorting can greatly improve the efficiency of data acquisition and reduce the number of database connections. In some scenarios, it can make your system more flexible and efficient.
Through the examples in this article, have you also thought of some business scenarios that can be optimized? Welcome to use next_result() in a more clever way!
