DB Active Record Class WHERE question

DB Active Record Class WHERE question - 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: DB Active Record Class WHERE question (/showthread.php?tid=4606)

DB Active Record Class WHERE question - El Forum - 12-05-2007

[eluser]rustyvz[/eluser]
Hello all,

I hope someone can help me with the following:
I have a SQL statement that I need to put into the active record class format:

Code:
SELECT *

FROM qanda

WHERE status = 'public'

AND shortcourse = 'cellbio' AND  (question LIKE '&#xen;docytosis%'

OR  answer LIKE '&#xen;docytosis%')

ORDER BY schoolyear DESC, shortcourse DESC, archivename DESC, createdate ASC, createtime ASC

When I use the following:

Code:
$db->from('qanda');

$db->where('status', $this->parameters['status']); 

$db->where('shortcourse', $this->parameters['shortcourse']); 

$db->like('question', $this->parameters['keyword']);

$db->orlike('answer', $this->parameters['keyword']);

$db->orderby('schoolyear DESC, shortcourse DESC, archivename DESC, createdate ASC, createtime ASC');

I get this statement as a result:

Code:
SELECT *

FROM qanda

WHERE status = 'public'

AND shortcourse = 'cellbio' AND  question LIKE '&#xen;docytosis%'

OR  answer LIKE '&#xen;docytosis%'

ORDER BY schoolyear DESC, shortcourse DESC, archivename DESC, createdate ASC, createtime ASC

I need those paren around the "AND question LIKE '&#xen;docytosis%' OR answer LIKE '&#xen;docytosis%'" and I need that part of the code to appear with "AND" as it does in the first example.

Any ideas?

Thanks in advance!

DB Active Record Class WHERE question - El Forum - 12-05-2007

[eluser]gtech[/eluser]
the easiest option I can think of

Code:
$sql = "SELECT * FROM qanda

WHERE status = ? AND shortcourse = ? AND  (question LIKE ? OR  answer LIKE ?)

ORDER BY schoolyear DESC, shortcourse DESC, archivename DESC, createdate ASC, createtime ASC ";

$this->db->query($sql, array($this->parameters['status'],

                             $this->parameters['shortcourse'],

                             $this->parameters['keyword'],

                             $this->parameters['keyword']));

DB Active Record Class WHERE question - El Forum - 12-05-2007

[eluser]JasonSTX[/eluser]

Code:
$db->from('qanda');

$db->where('status', $this->parameters['status']);

$db->where('shortcourse', $this->parameters['shortcourse']);

$db->where('(question LIKE '$this->parameters['keyword']' OR answer LIKE '$this->parameters['keyword']')');

$db->orderby('schoolyear DESC, shortcourse DESC, archivename DESC, createdate ASC, createtime ASC');

Would something like that work as a workaround?

DB Active Record Class WHERE question - El Forum - 12-05-2007

[eluser]ejangi[/eluser]
@JasonSTX - I think you're in danger of SQL injection. The method gtech mentioned cleans/quotes the variables before replacing the ?'s (question marks). I could be wrong, but that is my assumption???

DB Active Record Class WHERE question - El Forum - 12-05-2007

[eluser]JasonSTX[/eluser]
I guess it isn't a good idea to assume that the parameters information was already cleaned.

But you could change it from:
$this->parameters['keyword']
to:
$this->db->escape($this->parameters['keyword'])

But binding the data means you don't have to remember to escape. I was just trying to think of how to do it while still building the query the same way.

DB Active Record Class WHERE question - El Forum - 12-05-2007

[eluser]gtech[/eluser]
@JasonSTX.. I did try your solution out and it produced the right query (minor modification)

Code:
$this->db->from('qanda');

$this->db->where('status', $this->parameters['status']);

$this->db->where('shortcourse', $this->parameters['shortcourse']);

$this->db->where('(question LIKE \''.$this->parameters['keyword'].'\' OR answer LIKE \''.$this->parameters['keyword'].'\')');

$this->db->orderby('schoolyear DESC, shortcourse DESC, archivename DESC, createdate ASC, createtime ASC'); 

$this->db->get();

echo $this->db->last_query();

DB Active Record Class WHERE question - El Forum - 12-05-2007

[eluser]Armchair Samurai[/eluser]
I just ran into this the other day. If you have to do it by Active Record:

Code:
$string = $this->db->escape('%'.$this->parameters['keyword'].'%');

$this->db->where('status', $this->parameters['status']); 

$this->db->where('shortcourse', $this->parameters['shortcourse']); 

$this->db->where("(question LIKE $string OR answer LIKE $string)");

$this->db->orderby('schoolyear DESC, shortcourse DESC, archivename DESC, createdate ASC, createtime ASC');

$query = $this->db->get('qanda');

DB Active Record Class WHERE question - El Forum - 12-05-2007

[eluser]JasonSTX[/eluser]
[quote author="gtech" date="1196927039"]@JasonSTX.. I did try your solution out and it produced the right query (minor modification)

Code:
$this->db->from('qanda');

$this->db->where('status', $this->parameters['status']);

$this->db->where('shortcourse', $this->parameters['shortcourse']);

$this->db->where('(question LIKE \''.$this->parameters['keyword'].'\' OR answer LIKE \''.$this->parameters['keyword'].'\')');

$this->db->orderby('schoolyear DESC, shortcourse DESC, archivename DESC, createdate ASC, createtime ASC'); 

$this->db->get();

echo $this->db->last_query();

result =

SELECT * FROM qanda WHERE status = 'a' AND shortcourse = 'b' AND (question LIKE 'c' OR answer LIKE 'd') ORDER BY schoolyear DESC, shortcourse DESC, archivename DESC, createdate ASC, createtime ASC

so there you go two possible solutions[/quote]

Do remember to escape the parameters though like in my previous post:
change it from:
$this->parameters[’keyword’]
to:
$this->db->escape($this->parameters[’keyword’])

Things would be so much easier if we didn't have to ever worry about security Smile

DB Active Record Class WHERE question - El Forum - 12-06-2007

[eluser]rustyvz[/eluser]
I'll give those a shot and see how they work! THANKS!

One thing about escaping data for security reasons: One of the reasons I specifically used the ARC was because of the following note in the user manual for $this->db->where():

Note: All values passed to this function are escaped automatically, producing safer queries.

Nice! Same note for LIKE, SET, UPDATE, DELETE and method chaining!

EDIT: Ok, that worked great! Thanks all!
I wish there WAS a way to use LIKE & ORLIKE as theu are intended to be used though, but with some sort of 'grouping' (not GROUP BY).

I also wonder if I could have used 'having', and just included the whole paren and statement segment in it. Gee, maybe I should test that... :-)