Get total number of results using Sphinx MySQL

Question

How can I get the total number of results using mysql and sphinx?

First I tried with a PDO statement, which does return a number but it is not accurate.

$array = $pdo_sphinx->prepare("select * from `my_index` where MATCH ('@name ($search)') limit $start, $limit");
$array->execute(); 

$query = $pdo_sphinx->prepare("select COUNT(*) from `my_index` where MATCH ('@name ($search)')");
$query->execute();      
$total = $query->fetchColumn();

Then I read you can get total_found from SHOW META if you run it after the query

$array = $sphinx->Query("select * from `my_index` where MATCH ('@name ($search)') limit $start, $limit; SHOW META");

$total = $array['total_found'];

$total is returning 0, when it should be 9. How do I get the correct total_found from the query above? Is there a way to do this with the PDO statement? I need the correct result for paging

Answer 1

Note when you add the 'SHOW META' it makes it a multi-query. There are two separate queries, each with their own resultset.

• http://php.net/manual/en/pdostatement.nextrowset.php

• PDO multiple queries

(yes, using COUNT(*) may be inaccurate, because grouping can be somewhat approximate)