使用PDO检索匹配行的总计数以及行本身

I have a PDO snippet that retrieves a bunch of rows from a MySQL table and assigns the field values (2 fields are returned per row) to two arrays like so:

$connect = dbconn(PROJHOST,'dbcontext', PROJDBUSER, PROJDBPWD);
$sql= "SELECT contextleft, contextright FROM tblcontext WHERE contextleft REGEXP :word LIMIT 0, 25";
$xleft = array();
$xright = array();
$countrows = 0;
$word = "[[:<:]]".$term."[[:>:]]";
$query = $connect->prepare($sql);
$query->bindParam(":word", $word, PDO::PARAM_STR);
if($query->execute()) {
    $rows = $query->fetchAll(PDO::FETCH_ASSOC);
    foreach($rows as $row){
        $pattern = '/\b'. $term .'\b/ui';
        $replacer = function($matches) { return '<span class="highlight">' . $matches[0] . '</span>'; };
        $xleft[$countrows] = preg_replace_callback($pattern, $replacer, $row['contextleft']);
        $xright[$countrows] = $row['contextright'];
        $countrows++;
    }
    $notfound = null;
}
$connect = null;

This works perfect. As you can see, I use the LIMIT clause to ensure only a maximum of 25 rows are extracted. However, there can actually be many more matching records in the table and I need to also retrieve the total count of all matching records along with the returned rows. The end goal is pagination, something like: 25 of 100 entries returned...

I understand I have 2 options here, both involving 2 queries instead of a single one:

$sql= "SELECT COUNT(*) FROM tblcontext WHERE contextleft REGEXP :word;
SELECT contextleft, contextright FROM tblcontext WHERE contextleft REGEXP :word LIMIT 0, 25";

or...

$sql= "SELECT SQL_CALC_FOUND_ROWS contextleft, contextright FROM tblcontext WHERE contextleft REGEXP :word LIMIT 0, 25;
SELECT FOUND_ROWS();";

But I am still confused around retrieving the returned count value in PHP. Like I could access the fields by running the fetchAll() method on the query and referencing the field name. How can I access the count value returned by either FOUND_ROWS() or COUNT()? Also, is there any way to use a single SQL statement to get both count as well as the rows?

If you have a separate query with the count, then you can retrieve its value exactly the same way as you read the values from any queries, there is no difference.

If you use SQL_CALC_FOUND_ROWS, then you need to have a separate query to call FOUND_ROWS() function:

SELECT FOUND_ROWS();

Since this is again a normal query, you can read its output the same way as you do now.

You can technically retrieve the record count within the query itself by adding a count(*) field to the query:

SELECT contextleft, contextright, (select count(*) from tblcontext) as totalrecords FROM tblcontext WHERE contextleft REGEXP :word LIMIT 0, 25

or

SELECT contextleft, contextright, totalrecords
FROM tblcontext WHERE contextleft, (select count(*) as totalrecords from tblcontext) t REGEXP :word LIMIT 0, 25

Limit affects the number of records returned, but does not affect the number of rows counted by the count() function. The only drawback is that the count value will be there in every row, but in case of 25 rows, that may be an acceptable burden.

You need to test which method works the best for you.