how to pull and display range (min-max) data for each page in pagination?
- by Ty W
I have a table of data that is searchable and sortable, but likely to produce hundreds or thousands of results for broad searches. Assuming the user searches for "foo" and sorts the foos in descending price order I'd like to show a quick-jump select menu like so:
<option value="1">Page 1 ($25,000,000 - $1,625,000)</option>
<option value="2">Page 2 ($1,600,000 - $1,095,000)</option>
<option value="3">Page 3 ($1,095,000 - $815,000)</option>
<option value="4">Page 4 ($799,900 - $699,000)</option>
...
Is there an efficient way of querying for this information directly from the DB? I've been grabbing all of the matching records and using PHP to calculate the min and max value for each page which seems inefficient and likely to cause scaling problems.
The only possible technique I've been able to come up with is some way of having a calculated variable that increments every X records (X records to a page), grouping by that, and selecting MIN/MAX for each page grouping... unfortunately I haven't been able to come up with a way to generate that variable.
