CodeIgniter Forums
How to Force Code Igniter to NOT to excape queries? - 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: How to Force Code Igniter to NOT to excape queries? (/showthread.php?tid=27266)

How to Force Code Igniter to NOT to excape queries? - El Forum - 02-05-2010

[eluser]Sheikh Aman Alam[/eluser]
I wish to run this query-

Code:
SELECT `comment`.`commenttext`, `comment`.`cd`, `user`.`username`
FROM (`user`, `comment`)
WHERE `comment`.`videoid` = '2'
AND `comment`.`isactive` = 1
AND `user`.`userid` = comment.userid

so i fired this command-
[code]
$criteria = array(
       'comment.videoid'=>$videoid,
       'comment.isactive'=>1,
       'user.userid'=>'comment.userid'
          );
$selectWhichColumns = 'comment.commenttext,comment.cd,user.username';
$selectFromTable = 'user';
$data['response'] = $this->comment_model->_selectAND($criteria,$selectWhichColumns,$selectFromTable);

the _selectAND() method uses where to run these queries.
but this methods gives my following output-

Code:
SELECT `comment`.`commenttext`, `comment`.`cd`, `user`.`username`
FROM (`user`, `comment`)
WHERE `comment`.`videoid` = '2'
AND `comment`.`isactive` = 1
AND `user`.`userid` = comment.userid


MySQL isn't accepting the comment.id in quotes, so i want to stop codeigniter from escaping i with quotes.

i know how to do it with set() in INSERT statements, but not in this one.
please help.

Thanks in advance.

How to Force Code Igniter to NOT to excape queries? - El Forum - 02-05-2010

[eluser]Georgi Veznev[/eluser]
Code:
$this->db->select() accepts an optional second parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks. This is useful if you need a compound select statement.

$this->db->select('(SELECT SUM(payments.amount) FROM payments WHERE payments.invoice_id=4') AS amount_paid', FALSE);
$query = $this->db->get('mytable');


How to Force Code Igniter to NOT to excape queries? - El Forum - 02-05-2010

[eluser]danmontgomery[/eluser]
Do you mean:

Code:
WHERE `comment`.`videoid` = '2'

? Try:

Code:
$criteria = array(
    'comment.videoid'=>(int)$videoid,
    'comment.isactive'=>1,
    'user.userid'=>'comment.userid'
);

The above post will prevent CI from escaping fields, but not values if it thinks the value is a string

How to Force Code Igniter to NOT to excape queries? - El Forum - 02-05-2010

[eluser]Michael Wales[/eluser]
Wouldn't it be much easier to just select on the comment table and join the user table?

How to Force Code Igniter to NOT to excape queries? - El Forum - 02-05-2010

[eluser]Sheikh Aman Alam[/eluser]
@noctrum-
i didnt want to this:
WHERE `comment`.`videoid` = '2'

rather, i want to do this:
`user`.`userid` = comment.userid

and NOT this one:
`user`.`userid` = 'comment.userid'

codeigniter is doing the later one.


@michael Wales.
how to do that in this situation?

How to Force Code Igniter to NOT to excape queries? - El Forum - 02-06-2010

[eluser]Sheikh Aman Alam[/eluser]
Got it working.
used the join() method and now the code is like-

$this->db->select(`comment`.`commenttext`, `comment`.`cd`, `user`.`username`);
$this->db->from('comment');
$this->db->join('user','user.userid=comment.userid');
$this->db->where($criterea);
$resultSet= $this->db->get()

thanks a lot for giving ideas guys..
:-)