problem with some mysql constat

[eluser]DanTyan[/eluser]
Hello,

i use:

Code:

SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
WHERE id > 100 LIMIT 10;

SELECT FOUND_ROWS();

but after update to CI 1.6 script quote SELECT SQL_CALC_FOUND_ROWS and i get next query

Code:

SELECT `SQL_CALC_FOUND_ROWS`  ...

and mysql error

i write here because i'm not sure that i set up my CI correctly.

please help.
Dan

[eluser]Seppo[/eluser]
Are you using AR to generate the query?
If so, you shouldn't for that query, because SQL_CALC_FOUND_ROWS is not supported by AR and it escapes it as a field name.

[eluser]Derek Allard[/eluser]
Dan, if you are using AR, you can always try the 2nd parameter of select to prevent escaping. Seppo knows his stuff... but we'll be able to help you more after we see the actual code generating the query.

[eluser]DanTyan[/eluser]
Hello Derek,

thanks for your reply it works =)


best regards,
Dan

[eluser]kjackson7_93[/eluser]
Dan,

I'm new to CI and using AR. I'd like to use SQL_CALC_FOUND_ROWS and FOUND_ROWS in my model function. Could you please post a sample code from your model that implements this function.

Thanks!

[eluser]DanTyan[/eluser]
Hi

here the the code with SQL_CALC_FOUND_ROWS

Code:

$sSelect = '
   SQL_CALC_FOUND_ROWS
   `Id`,
   `Title`,
   `Body`,
  `Author`
';
  
$this->db->select($sSelect, false);
$this->db->from('`post`');
$this->db->limit(10);
$oRes = $this->db->get();
$aPost = $oRes->result_array();


$this->db->select("FOUND_ROWS() as PostNum");
$rRes2 = $this->db->get();
$iPostNum = $rRes2->row_array();
$iPostNum = $iPostNum['PostNum'];

[eluser]kjackson7_93[/eluser]
Thanks a ton! It was the get() without passing a database that I was stumped on... this is how I used it in my application:

Code:

function getNotes($offset=0,$row_count=$this->default_page_count){
        $this->db->select("SQL_CALC_FOUND_ROWS board_notes.*");
        $this->db->orderby('board_notes.created', 'desc');
        $this->db->limit($row_count,$offset);
        $board_notes = $this->db->get('board_notes');
        
        $this->db->select("FOUND_ROWS() as PostNum");
        $rRes2 = $this->db->get();
        $iPostNum = $rRes2->row_array();
        $this->_setFoundRows($iPostNum['PostNum']);

        return $board_notes->result();
    }
    
    function _setFoundRows($found_rows){
        $this->FoundRows = $found_rows;
    }
    
    function getFoundRows(){
        return $this->FoundRows;
    }

Then in my controller:

Code:

$notes['offset'] = (int) $this->uri->segment(3, 0);
$notes['notes_list'] = $this->Notes_model->getNotes($notes['offset'], $paging['per_page']);
$notes['found_rows'] = $this->Notes_model->getFoundRows();

Thanks again!