Is there a way to get the number of records from a query with Zend-framework?

Question

Given my generic select below, is there a way to get the number of records returned from a query with Zend Framework? $row++ in a loop is not acceptable for my solution as I am using paging (though its not in my sample).

I also DO NOT want to add another query with "Count(*)".

$query = "Select * from Users where active = 1";
$stmt = $db->query($query);

$noOfRows = ???;

while ($row = $stmt->fetch())
{

  // processing
}

Answer 1

Use fetchAll()

fetchAll returns an array, so you can do something like this:

$rows = $db->fetchAll("select ...");
$numRows = sizeof($rows);
foreach ($rows as $row)
{
  // process each row
}

Answer 2

If you are returning the entire result set in your SQL query, you can do a fetchAll() instead of a fetch(), and then count() the number of items in the array returned from fetchAll().

However, if you are doing paging with a MySQL LIMIT clause or equivalent, you will only get the count for number of items returned for that query(page). There's no way to get the entire count of results without doing a COUNT() in SQL in this case. You can only do calculations based on the resultset from the database.

Answer 3

It still requires another query, but with MySQL there is a SELECT parameter SQL_CALC_FOUND_ROWS - it stores a value in your connection that you can then SELECT FOUND_ROWS()

Note - the performance of your initial query will go down, because the limit will be processed later, but it may be faster than a separate COUNT().

FOUND_ROWS()

$query = "Select SQL_CALC_FOUND_ROWS * from Users where active = 1 limit 20";
$stmt = $db->query($query);

while ($row = $stmt->fetch())
{

  // processing
}

$count = $db->fetchOne('SELECT FOUND_ROWS()');

Answer 4

Kekoa is wrong.

If you put that SQL_CALC_FOUND_ROWS and then if you put a limit you will get the full number of rows (is no affected by the limit).

If you have a simple table then is recomended to use count(*) in the pagination since is much faster, but if you have complex queries and your data is the result of many joins and so on then my advice is to use SQL_CALC_FOUND_ROWS.

I hope this helps you.

BornForCode

Answer 5

It's strange but this just works:

$oSelect = $oTable->select()
                ->where(...)
                ->order(...)
                ->limit(...)
$oRows = $oTable->fetchAll($oSelect);
// these two lines are the solution: we simply run the query again!
$db->query($oSelect);
$iTotal = $db->fetchOne("select FOUND_ROWS()");

i should mention that i use this line in my config file:

db.profiler.enabled = true

here is some code for feather ideas; however it does not work:

$query = $db->select()
->from('your_table', array(new Zend_Db_Expr('SQL_CALC_FOUND_ROWS id as fake_column'),'*'))
->where(...)
->limit(0, 10);

$db->query($query);

$iCount = $db->fetchOne('select FOUND_ROWS()');

also take a look at http://old.nabble.com/RE:-CALC_FOUND_ROWS-vs.-count%28*%29-p16761518.html