How to get total amount of rows while using limit when using PDO prepared statements?

Question

I found some similar ones, but not the exact one that I need:

Background information: I am using MySQL with PDO class

Currently, I am using two queries as follows:

To get one page of data:

$sql = "SELECT * FROM `tab`
WHERE `condition` = :condition 
LIMIT $page_size OFFSET $offset";

$array = array('condition'=>$condition);
$mysql = $pdo->prepare($sql);
$mysql->execute($array);

To get total amount of rows:

$sql = "SELECT COUNT(*) FROM `tab`
WHERE `condition` = :condition";

$array = array('condition'=>$condition);
$mysql = $pdo->prepare($sql);
$mysql->execute($array);

Can you show your code / attempt to achieve it ? – Sulthan Allaudeen Jul 05 '15 at 23:30 — Sulthan Allaudeen, Jul 05 '15 at 23:30
@SulthanAllaudeen, see the new question content. – Dainy Jul 05 '15 at 23:37 — Dainy, Jul 05 '15 at 23:37

score 2 · Accepted Answer · edited May 23 '17 at 12:02

You can use the SQL_CALC_FOUND_ROWS command to tell MySQL to return the total of the matching records

$sql = "SELECT SQL_CALC_FOUND_ROWS * FROM `tab`
WHERE `condition` = :condition 
LIMIT $page_size OFFSET $offset";

To get the total rows found you can run this query

SELECT FOUND_ROWS()

However, it is often faster to execute 2 separate queries than using SQL_CALC_FOUND_ROWS. Here is a benchmark to explain

you can read more about this here

How to get total amount of rows while using limit when using PDO prepared statements?

1 Answers1