English
English
When developing using MySQL database, mysqli_result is a commonly used class that provides encapsulation of query results, allowing developers to extract data in the result set through a series of fetch_* methods. However, many developers encounter a problem when using these methods: after multiple calls to the fetch_* method, the query results seem to "disappear" and cannot be accessed again. What's going on? Let's take a deeper look.
After the MySQL query is executed, the returned result set is encapsulated into a mysqli_result object. In PHP, we can read data line by line by calling different fetch_* methods, such as:
fetch_assoc() : Returns the result row in associative array.
fetch_row() : Returns the result row in the way of indexing array.
fetch_object() : Returns the result row as an object.
For example:
$conn = new mysqli("localhost", "user", "password", "database");
$query = "SELECT * FROM users";
$result = $conn->query($query);
At this time, the $result object is a mysqli_result , which can be used to call the above fetch_* method.
When the mysqli_result object calls the fetch_* method, it actually reads the data line by line. When you call the fetch_* method, the pointer to the result set will move forward. This means that each time fetch_* is called, a row of data is consumed and the position of the pointer is updated. If you call the fetch_* method multiple times, it will read line by line until there is no more data to read.
For example:
while ($row = $result->fetch_assoc()) {
// Processing data
}
while ($row = $result->fetch_assoc()) {
// No data is obtained here，Because the previous query has consumed the result set completely
}
As shown above, the first while loop reads all the data and consumes the entire result set. When the second while loop tries to get the data again, the result set has been "eated" and therefore cannot get the data.
To avoid this, we can take the following methods:
If you need to re-traverse the result set, you can use the mysqli_data_seek() method to reset the data pointer. For example:
$result = $conn->query("SELECT * FROM users");
while ($row = $result->fetch_assoc()) {
// Read data for the first time
}
// Reset result set pointer
$result->data_seek(0);
while ($row = $result->fetch_assoc()) {
// Read data for the second time
}
By calling data_seek(0) you can reset the data pointer back to the beginning of the result set, thus reading the data again.
If you want to get all the data at once, you can use the mysqli_fetch_all() method, which will extract all the data into an array at once, without reading it row by line like fetch_* . For example:
$result = $conn->query("SELECT * FROM users");
$rows = $result->fetch_all(MYSQLI_ASSOC);
// You can use it multiple times $rows Data in array
foreach ($rows as $row) {
// Processing data
}
This approach avoids the situation where the result set is read line by line and consumes the result set, allowing you to use the result set data in multiple places.
If your query results are small, you can also store the data in an array and reuse the data in the array. For example:
$result = $conn->query("SELECT * FROM users");
$data = [];
while ($row = $result->fetch_assoc()) {
$data[] = $row;
}
// You can use it multiple times $data Array
foreach ($data as $row) {
// Processing data
}
This method also avoids repeated query of the database, and also avoids the problem of the result set being "eated".
When you call the fetch_* method multiple times, the result set is read line by line and consumed. If you want to traverse the result set multiple times, using mysqli_data_seek() to reset the result set pointer, or using mysqli_fetch_all() to get all the data at once, it is a good choice. Through these methods, you can avoid the "result sets are eaten" problems encountered during development and ensure that the data is read correctly.
