[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 'n;docytosis%'
OR answer LIKE 'n;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 'n;docytosis%'
OR answer LIKE 'n;docytosis%'
ORDER BY schoolyear DESC, shortcourse DESC, archivename DESC, createdate ASC, createtime ASC
I need those paren around the "AND question LIKE 'n;docytosis%' OR answer LIKE 'n;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!
[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']));
[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?
[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???
[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.
[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();
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
[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');
[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
[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... :-)
|