• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
DB Active Record Class WHERE question

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

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

#3
[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?

#4
[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???

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

#6
[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

#7
[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');

#8
[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

#9
[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... :-)


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.