problem with some mysql constat

problem with some mysql constat - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: problem with some mysql constat (/showthread.php?tid=5754)

problem with some mysql constat - El Forum - 02-02-2008

[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

problem with some mysql constat - El Forum - 02-02-2008

[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.

problem with some mysql constat - El Forum - 02-02-2008

[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.

problem with some mysql constat - El Forum - 02-02-2008

[eluser]DanTyan[/eluser]
Hello Derek,

thanks for your reply it works =)

best regards,
Dan

problem with some mysql constat - El Forum - 03-04-2008

[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!

problem with some mysql constat - El Forum - 03-04-2008

[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'];

problem with some mysql constat - El Forum - 03-05-2008

[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!