Using mysqli_data_seek() Function to Grab Random Row
July 31, 2013 Development, MySQL, PHP Social Share
I wanted to use a set of database results twice on a page but didn’t want to run the database query again. Using the mysqli_data_seek() function, we can reset the query results to the first row.
PHP
// Store the database result
$result = mysqli_query($connection, $query);
// Loop through the results
while($row = mysqli_fetch_assoc($result))
{
echo $row['name'];
}
// Call the function and return to the first position
mysqli_data_seek($result, 0);
// Loop through the results again
while($row = mysqli_fetch_assoc($result))
{
echo $row['city'];
}
By setting the position of mysqli_data_seek() to be zero, we are returning to the first row. If we set the position to be 199, the resulting row would be number 200. This can be useful when we only need to use the second half of the results.
PHP
$total_rows = mysqli_num_rows($result); // Get total number of rows
$halfway_point = ceil($total_rows / 2); // Calculate the halfway point and round up
mysqli_data_seek($result, $halfway_point - 1); // Reset our results to the halfway point
By using a combination of mysqli_num_rows() to count the total number of rows and ceil() to round our halfway point up, we can start from midway through our data set. We can also use floor() to round down or round() to automatically round up or down.
This technique doesn’t come in handy too often since there are often better ways to print out the same result set twice, such as using an array, but there are a few exceptions. It’s a bit more useful when used before printing out your query results to skip to a specific row number. We can also print out a random row from the result by using the rand() function.
PHP
$total_rows = mysqli_num_rows($result); // Get total number of rows
$random_point = rand(1, $total_rows); // Grab a random point (minimum = 1, maximum = total)
mysqli_data_seek($result, $random_point - 1); // Seek our random row
Instead of querying our database again, we can now using the mysqli_data_seek() function.