counting mysql query

Question

Hi,

I have the following code

$result = $handle->select()->from('store_products_id', array('count'=>'COUNT(store_products_id.product_id)'))
                                       ->where('store_products_id.store_id=?', $this->store_id)
                                       ->columns($selectColumns)
                                       ->join('product_quickinfo', 'store_products_id.product_id = 
                   product_quickinfo.product_id')

                                       ->join('image_paths', 'product_quickinfo.image_id = 
                   image_paths.image_id')

               ->order('product_quickinfo.date_created DESC')
               ->limitPage($this->page_number, $this->sum_each_page)
                                       ->query(ZEND_DB::FETCH_OBJ);

However, I only get back one result.. here's a look at the print_r:

Array ( [0] => stdClass Object ( [count] => 14 [small_path] => 1 [product_name] => v evrecvrv [price] => 22 [product_id] => 1 [image_id] => 1 [date_created] => 0000-00-00 00:00:00 [large_path] => [description] => ) )

When I remove the COUNT controller, I get all the items back. How can I both count the entire query (beyond the LIMIT that I impose) and also get the entire query back?

Thanks.

Answer 1

The most efficient way is simply to run two queries, in this case.

Answer 2

Since MySQL 4.0.0 you can use SQL_CALC_FOUND_ROWS in your query which will tell MySQL to count total number of rows disregarding LIMIT clause. You still need to execute a second query in order to retrieve row count, but it’s a simple query that just retrieves the stored row count.

Usage is pretty simple. In you main query you need to add SQL_CALC_FOUND_ROWS option just after SELECT and in second query you need to use FOUND_ROWS() function to get total number of rows. Queries would look like this:

SELECT SQL_CALC_FOUND_ROWS name, email 
FROM users 
WHERE name LIKE 'a%' 
LIMIT 10;

SELECT FOUND_ROWS();

The only limitation is that you must call second query immediately after the first one because SQL_CALC_FOUND_ROWS does not save number of rows anywhere.

(Copied from this post)

Answer 3

This is similar to another question that I answered.