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:

• Do the work but not using PDO
• Using PDO but not using LIMIT

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);

Answer 1

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