Codeigniter: Weird behaviour of $this->db->like()
Use the escape_like_str()
method.
The escape_like_str()
method should be used when strings are to be used in LIKE
conditions so that LIKE
wildcards %
, _
in the string are also properly escaped. It cannot automatically add the ESCAPE
!
condition for you, and so you’ll have to manually do that.
Hope it helps.
$keyword = "keyword sample";
$sql = "SELECT id FROM table WHERE column LIKE '%" .
$this->db->escape_like_str($keyword)."%' ESCAPE '!'";
Source:- https://www.codeigniter.com/userguide3/database/queries.html
If you dig a bit into CodeIgniter's internals, you'll notice that the $this->db->like()
function escapes special characters it contains - including, of course, %
.
I don't think like()
will help you much with your particular needs. Your best bet, I guess, would be to bypass the problem and use a where
function containing your LIKE
clause:
$this->db->select('*')->from('table')->where("column LIKE '%$keyword%'")->get()->result_array();
You just try it like this:
$this->db->select('*')
$this->db->from('table');
$this->db->like('column', $keyword);
return $this->db->get()->result_array();
If you want to control where the wildcard (%) is placed, you can use an optional third argument. Your options are 'before', 'after' and 'both' (which is the default).
example:
$this->db->select('*')
$this->db->from('table');
$this->db->like('column', $keyword, 'before');
return $this->db->get()->result_array();
If you do not want to use the wildcard (%) you can pass to the optional third argument the option 'none'.
example:
$this->db->select('*')
$this->db->from('table');
$this->db->like('column', $keyword, 'none');
return $this->db->get()->result_array();
BUT, for your example you must need to search like "%keyword sample%"
, or like "%keyword%" OR "%simple%"
;
For example:
$this->db->like('column', 'keyword simple');
// Produces: WHERE column LIKE '%keyword simple%'
OR
$this->db->like('column', 'keyword');
$this->db->or_like('column', 'simple');
// Produces: WHERE column LIKE '%keyword%' OR column LIKE '%simple%'
More, you can read CodeIgniter User Guide