CODEWITHSUNDEEP
phpmysqlcodeigniter
Al Jenssen
Al Jenssen

Reputation: 695

Code Igniter - MySQL query using where and like

I have searched every answer but nothing describes what I want or maybe I didn` t comprehend them quite right. So here goes my question. I want a "complex" query like this:

select * from MyTable
where PropertyA='$propertyValue'
and (id like '%$someValue%' or name like '%$someValue%' or description like '%$someValue%') 
order by id desc limit 10 offset $offsetValue

How can I write this query in code igniter? $propertyValue, $someValue, $offsetValue are all php variables. And of course I need to avoid sql injection. I have also tested this in phpmyadmin and my query works fine.

Upvotes: 3

Views: 752

Answers (2)

mickmackusa
mickmackusa

Reputation: 47874

  • select('*') is safe to omit, because the default SELECT clause is SELECT *.
  • Your where(), from(), limit() and offset() method calls can all be combined into a get_where() call.
  • Use group_start() and group_end() to encapsulate the OR LIKE condition logic.
  • or_like() can receive an associative array in its first parameter (just remember that keys must not be repeated).
  • Caution: it seldom makes sense to perform a LIKE comparison on a numeric column which id might be.
return $this->db
    ->group_start()
        ->or_like(['id' => $someValue, 'name' => $someValue, 'description' => $someValue])
    ->group_end()
    ->order('id', 'DESC')
    ->get_where('MyTable', ['PropertyA' => $propertyValue], $limit, $offset)
    ->result();

Upvotes: 0

Saty
Saty

Reputation: 22532

For SQL injections, using binded queries and Active records is safe, it will save you from SQL injections as the framework does all of the work of escaping vulnerable user input.

You just write your code in Active record FOR 3.X version

$this->db->select('*');
$this->db->where('PropertyA', $propertyValue);
$this->db->group_start();
$this->db->like('id', $someValue);
$this->db->or_like('name', $someValue);
$this->db->or_like('description', $someValue);
$this->db->group_end();
$this->db->order('id','desc')
$this->db->limit($limit, $start);// add your limit here
$this->db->get('MyTable');

Upvotes: 9

Related Questions