0

I have have a search routine/display (in php) that is so common it must be in a library. Any way, I search a database and find, say, 1000 records. I only want to display 50 at a time. That's easy to do. You don't need to know how many total records there are, just remember where you were and configure the limit construct for the next chunk.

But how do you calculate the number of pages available unless you run the query 'wide open' at least once to get the 'grand total'? Seems a waste to do that.

I thought about caching the unlimited query in $_SESSION. I've never done that and even with experimentation I'm not sure I want to.

What is the common practice of discovery. How do you get the total number of records other than by running a query twice?

user116032
  • 321
  • 2
  • 15
  • 2
    If you have something like `SELECT f1, f2, f3 FROM table LIMIT x;` then `SELECT COUNT(*) FROM table;` will give you the grand total – Aguardientico Jan 25 '17 at 01:34

2 Answers2

1

How do you get the total number of records other than by running a query twice?

You don't need to actually run the search query twice, you can just do SELECT COUNT(*) FROM yourTable. This isn't as bad as you might think, and in the worst case would require just a single table scan. Assuming you would only need to get the total number of records at the start of the search, I don't think this would be a major problem.

Another option is to use SQL_CALC_FOUND_ROWS, but this may peform slower than using COUNT(*). You can read about the performance of the two options here:

Which is fastest? SELECT SQL_CALC_FOUND_ROWS FROM `table`, or SELECT COUNT(*)

Community
  • 1
  • 1
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
1

You want SQL_CALC_FOUND_ROWS:

select SQL_CALC_FOUND_ROWS t.*
from t
limit 50

You can then fetch the number of rows in another query:

SELECT FOUND_ROWS();

Read about it in the documentation.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786