CODEWITHSUNDEEP
phpmysql
Joel
Joel

Reputation: 6107

Counting all results of MySQL query for paging

I am building a search page, each page featuring 10 results. For that purpose, I would like to display pages numbers at the bottom (like in Google for example) so a user can jump to a specific page result. In order to do that I need to know the overall count of the query (e.g., if I show 10 results per page and the specific query returns 73 rows in my Table, i would need to display links to 8 pages).

The only way I can think of is using the following (obviously inefficient) query:

// Query for the current page
$res = mysql_query("select * from TABLE WHERE COL='Sample' LIMIT $offset,10");

// Geeting the total count do I can build links to other pages
$res2 = mysql_query("select COUNT(*) fromTABLE WHERE COL='Sample'");

Is that the only way to do it?

Thanks,

Danny

Upvotes: 2

Views: 3153

Answers (1)

Haim Evgi
Haim Evgi

Reputation: 125526

you can use

SQL_CALC_FOUND_ROWS


mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
    -> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();

SQL_CALC_FOUND_ROWS tells MySQL to calculate how many rows there would be in the result set, disregarding any LIMIT clause. The number of rows can then be retrieved with SELECT FOUND_ROWS()

Upvotes: 8

Related Questions