English
English
When using PHP for database operations, we sometimes need to execute multiple queries and process the results of these queries. For some complex queries, we may need to use the paging logic returned by multiple queries. The next_result() function is very useful in this kind of scenario. It helps us to effectively process the results of multiple queries, especially when paging is required.
next_result() is a function in PHP's mysqli extension. It allows us to get the results of each query one by one when executing multiple queries. When you use the mysqli_multi_query() function to execute multiple queries, next_result() allows you to loop through the results of each query.
Typically, mysqli_multi_query() is used to execute multiple SQL queries, while next_result() allows us to get the results of each query one by one and process the data in each iteration.
We may need the pagination function when we process multiple query results. For example, when there are multiple paging query results, we need to get and display the results of each query in turn. At this time, next_result() can help us effectively jump to the result set of the next query and avoid confusion and duplication processing.
Suppose we have a database that contains multiple paging query results, we can use the following code to implement paging:
<?php
// Database connection
$mysqli = new mysqli("localhost", "username", "password", "database");
// Check if the connection is successful
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
// Perform multiple queries
$query = "SELECT * FROM table1 LIMIT 10;
SELECT * FROM table2 LIMIT 10;
SELECT * FROM table3 LIMIT 10;";
if ($mysqli->multi_query($query)) {
do {
// Get the result set
if ($result = $mysqli->store_result()) {
// Loop output result
while ($row = $result->fetch_assoc()) {
echo "ID: " . $row['id'] . " - Name: " . $row['name'] . "<br>";
}
// Release the result set
$result->free();
}
// Get the next result set
} while ($mysqli->next_result());
}
// 关闭Database connection
$mysqli->close();
?>
Database connection : First, we create a database connection object $mysqli through new mysqli() and connect to the local database.
Multi-query execution : Use the multi_query() function to execute multiple queries. Here we execute three queries, each using LIMIT 10 to limit the result set to 10 records.
Process each result set : get the result set of each query through store_result() , and then use fetch_assoc() to output the results one by one.
Use next_result() : After each query result is processed, call next_result() to jump to the next query result set.
Close database connection : After the operation is completed, close the database connection.
If we want to implement paging logic on the results of multiple queries, we can process the results of each query after next_result() . For example, if each query returns 10 records, you can display data by page, similar to the following code:
<?php
$perPage = 10; // Number of records per page
$currentPage = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$offset = ($currentPage - 1) * $perPage;
// Database connection
$mysqli = new mysqli("localhost", "username", "password", "database");
// Check if the connection is successful
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
// Perform a pagination query
$query = "SELECT * FROM table1 LIMIT $offset, $perPage;
SELECT * FROM table2 LIMIT $offset, $perPage;
SELECT * FROM table3 LIMIT $offset, $perPage;";
if ($mysqli->multi_query($query)) {
do {
// Get the result set
if ($result = $mysqli->store_result()) {
// Loop output result
while ($row = $result->fetch_assoc()) {
echo "ID: " . $row['id'] . " - Name: " . $row['name'] . "<br>";
}
// Release the result set
$result->free();
}
// Get the next result set
} while ($mysqli->next_result());
}
// 关闭Database connection
$mysqli->close();
?>
In this example, we dynamically calculate offset , adjust the query conditions according to the current number of pages, and implement the paging function.
Query order : When you use next_result() , the order of query is very important. Make sure you process the results of each query in the correct order.
Resource management : After each query result is processed, remember to call free() to release the resource.
Performance considerations : When using multi_query() to execute multiple queries, if the amount of data is large, it may affect performance. Be sure to use paging reasonably and avoid querying too much data at one time.
Through the next_result() function, we can easily implement paging processing of multiple query results. This is very suitable for scenarios where multiple database queries are required to be executed and each query result is paginated. Hopefully the sample code provided in this article can help you better understand and apply this function.
