2010/6/16 Mike Christensen
<mike@kitchenpc.com> I'm generating a query on the fly to return a set of data, however I
only want to display 30 rows at a time to the user. For this reason,
I use the LIMIT 30 OFFSET x clause on the select statement. However,
I also want to know the total rows that match this query had there
been no limit, that way I can display to the user the total count and
the number of pages, and have Next/Prev buttons in my UI. I can think
of the following ways to do this:
1) When the page loads, execute two totally separate queries. One
that does the COUNT, and then another query immediately after to get
the desired page of data. I don't like this as much because the two
queries will execute in separate transactions and it'd be nice if I
could just perform a single SQL query and get all this information at
once. However, I will use this if there's no other way.
Just run them in one transaction.
You can also just show the Next/Prev buttons and then do something just for the case where there is no data.
Or use LIMIT 31 so you always know that there is the next page with at least one record.
regards
Szymon Guz