Counting Total Records

[owentech]

Hi,
I have a quesry that sometimes retrieves very many records. This made it take a while. So i Introduced LIMIT and Previous and Next links.
This all works quite well.

My problem is that I'd like to know how many records in total will be returned (in all the pages) but i fear that this would be the same us running the query without the LIMIT, which would make it slow again.

How can I overcome this?

Abe

[mwasif]

You can run a query with COUNT() e.g.

$rs = mysql_fetch_array(mysql_query("SELECT COUNT(*) FROM table"));
$total_results = $rs[0]; 

Or you can use SQL_CALC_FOUND_ROWS in the query to get the total records.

$result = mysql_query("SELECT SQL_CALC_FOUND_ROWS * FROM table LIMIT 0,10");
$rs = mysql_query(mysql_query("SELECT FOUND_ROWS()"));
$total_results = $rs[0];

$rs = mysql_fetch_array($result); 

[owentech]

You can run a query with COUNT() e.g.

$rs = mysql_fetch_array(mysql_query("SELECT COUNT(*) FROM table"));
$total_results = $rs[0]; 

Or you can use SQL_CALC_FOUND_ROWS in the query to get the total records.

$result = mysql_query("SELECT SQL_CALC_FOUND_ROWS * FROM table LIMIT 0,10");
$rs = mysql_query(mysql_query("SELECT FOUND_ROWS()"));
$total_results = $rs[0];

$rs = mysql_fetch_array($result); 

Is that faster than actually running the original unlimited query?

[mwasif]

Is that faster than actually running the original unlimited query?

If you use mysql_num_rows() to count the total records then it is certainly faster.

[praetor]

select sql_calc_found_rows * from table limit 10; --the normal query
select found_rows(); -- gives you the total rows

[owentech]

Thanks.