0

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

user3312792
  • 1,101
  • 2
  • 12
  • 27

1 Answers1

1

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

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

Community
  • 1
  • 1
barryhunter
  • 20,886
  • 3
  • 30
  • 43