0

I am using a simple select statement using SQL_CALC_FOUND_ROWS in zf2. The code would look like as follows and uses quantifier.

$select = $this->getSlaveSql()->select('posts');
$select->quantifier(new Expression('SQL_CALC_FOUND_ROWS'));
$select->columns([
       'total'=>new Expression("FOUND_ROWS()"),
       '*'
]);

And the generated sql is as below

SELECT SQL_CALC_FOUND_ROWS FOUND_ROWS() AS `total`, `posts`.* FROM `posts`

screenshot: enter image description here

But for some reasons, found_rows is always returned as 0 and I do not want to add a second query for pagination. Please help.

Laxman
  • 1,149
  • 2
  • 11
  • 17

1 Answers1

3

FOUND_ROWS() is normally used in a subsequent query. I doubt you can run it in the same one that you select data in. See the example in the docs: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

Also note that SQL_CALC_FOUND_ROWS makes no sense in a query without a limit.

Tim Fountain
  • 33,093
  • 5
  • 41
  • 69
  • I have the same problem with limit too. Using SELECT SQL_CALC_FOUND_ROWS FOUND_ROWS() AS `total`, `posts`.* FROM `posts` LIMIT '10' also have no effect :( – Laxman Dec 30 '14 at 11:00
  • Well, like I said, the `FOUND_ROWS()` call needs to be in a separate query after you've selected the posts data. – Tim Fountain Dec 30 '14 at 11:04
  • thanks tim, the problem is solved using another select query for the found_rows(), as you said. – Laxman Dec 31 '14 at 09:45