current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I'm doing a simple MySQL query to count the number of rows a query is returning without the effect of the LIMIT clause. I'm using Active Records with Codeigniter PHP framework.

Problem: I'm getting an error when SQL_CALC_FOUND_ROWS is used. Why is this so?

Query

SELECT `listing_id`, SQL_CALC_FOUND_ROWS listing_id FROM (`listings`) LIMIT 100

Error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SQL_CALC_FOUND_ROWS listing_id FROM (listings) LIMIT 100' at line 1

Codeigniter Active Records

$this->db->select('listing_id')
            ->select('SQL_CALC_FOUND_ROWS listing_id', FALSE)
            ->from('listings')
            ->where('price < 1000')
            ->limit($limit, $offset)
            ->order_by('listing_id', 'desc');
share|improve this question

2 Answers 2

up vote 0 down vote accepted

You can use MySql's count() function to achieve what you are looking for ie.

SELECT `listing_id`, count(*) FROM (`listings`) LIMIT 100
share|improve this answer
1  
+1 for teaching me something new :) –  Joachim Isaksson Feb 18 '12 at 18:00
    
Doesn't this need a GROUP BY statement as well? –  stef Apr 6 '12 at 12:19
up vote 1 down vote

SQL_CALC_FOUND_ROWS doesn't return a value, it's simply a modifier to indicate that the number of rows —where the LIMIT clause is not taken into account— should be saved so it can be retrieved later on, by using a second query (without generating the complete result set twice). Think of it the same as the DISTINCT keyword.

For more information, please read the documentation on this topic.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.