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.
