English
English
In daily development, we often need to fetch result sets from a MySQL database and iterate through them. By default, mysqli_query returns the result set in the order of the query. If we want to traverse the result set in reverse, one simple and efficient method is to use PHP's mysqli_result::data_seek function.
mysqli_result::data_seek is a method provided by PHP that allows developers to move the internal pointer of a result set to a specified row. Its basic usage is as follows:
<span><span>mysqli_result::</span><span><span class="hljs-title function_ invoke__">data_seek</span></span><span>(</span><span><span class="hljs-keyword">int</span></span><span> </span><span><span class="hljs-variable">$offset</span></span><span>): </span><span><span class="hljs-keyword">bool</span></span></span>$offset specifies the target row index, starting from 0.
The return value is boolean: true on success and false on failure.
When we want to traverse a result set in reverse, the most straightforward approach is to read from the last row backwards, without using ORDER BY ... DESC in the SQL query. This can reduce database load in some cases, especially when dealing with very large datasets.
Suppose we have a MySQL table users with columns id and name, and we want to output the user list in reverse order:
<?php
$mysqli = new mysqli("localhost", "username", "password", "database");
<p>if ($mysqli->connect_error) {<br>
die("Connection failed: " . $mysqli->connect_error);<br>
}</p>
<p>$sql = "SELECT id, name FROM users";<br>
$result = $mysqli->query($sql);</p>
<p>if ($result) {<br>
$num_rows = $result->num_rows;</p>
for ($i = $num_rows - 1; $i >= 0; $i--) {
$result->data_seek($i); // Move the internal pointer to row $i
$row = $result->fetch_assoc();
echo "ID: " . $row['id'] . ", Name: " . $row['name'] . "\n";
}
$result->free();
} else {
echo "Query failed: " . $mysqli->error;
}
$mysqli->close();
$result->num_rows gets the total number of rows in the result set.
The for loop starts from the last row $num_rows - 1 and moves backward step by step.
Use $result->data_seek($i) to position to the specified row.
Call $result->fetch_assoc() to fetch the data of the current row.
This method does not require modifying the SQL query order and is very useful in scenarios where dynamic control of the traversal order is needed.
Advantages:
Simple to use and allows traversal order control directly at the PHP level.
Highly efficient for small result sets.
Considerations:
For large result sets, if memory usage is too high, it is still recommended to use ORDER BY in SQL for sorting.
data_seek only works on mysqli_result objects returned by mysqli_query and is not suitable for unbuffered queries.
With mysqli_result::data_seek, we can easily implement reverse traversal of result sets without relying on SQL sorting. It provides PHP developers with more flexible control, especially useful for scenarios requiring dynamic handling of query results.
Related Tags:
mysqli_result
