CodeIgniter "like ()" function with% wildcard inside search terms
Let's say I have a function like this:
public function get_list($category = '', $limit = 10, $offset = 0) {
if (!empty($category))
$this->db->where('category', $category);
$search = $this->input->get('search');
if (!empty($search))
$this->db->or_like(array('foo_column'=>$search));
$query = $this->db->get('table_name', $limit, $offset);
//echo $this->db->last_query();
return $query->result();
}
Make a request like:
SELECT * FROM table_name WHERE foo_column LIKE '%match something%'
As you can see, the wildcard %
can be added to both sides, before
and after
.
And how if I want to create, for example:
... WHERE foo_column LIKE '%match%something%'?
FYI, I use str_replace()
the before change space
before function %
, but codeigniter is always escape
using slash
. It makes a request like:
... WHERE foo_column LIKE '%match\%something%'
This is useful when the search matches something else with a keyword match with something , but wildcard
on the first and / or after doesn't seem to work.
source to share
To achieve this functionality, I have updated your code with some different conditions.
Note. Here I have manually placed the category and search values
public function get_list($category = '', $limit = 10, $offset = 0) {
$category = 'electronics';
if (!empty($category)) {
$this->db->where('category', $category);
}
$search = 'match something';
if (preg_match('/\s/', $search) > 0) {
$search = array_map('trim', array_filter(explode(' ', $search)));
foreach ($search as $key => $value) {
$this->db->or_like('foo_column', $value);
}
} else if ($search != ''){
$this->db->like('foo_column', $search);
}
$query = $this->db->get('table_name', $limit, $offset);
return $query->result();
}
Here $search = 'match something'
and this will generate a request like this:
SELECT * FROM (`table_name`) WHERE `category` = 'electronics' AND
`foo_column` LIKE '%match%' OR `foo_column` LIKE '%something%' LIMIT 10
If $search = 'match something another'
, then it will generate a request like
SELECT * FROM (`table_name`) WHERE `category` = 'electronics' AND
`foo_column` LIKE '%match%' OR `foo_column` LIKE '%something%' OR
`foo_column` LIKE '%another%' LIMIT 10
and if $search = 'match'
, it will generate a request like
SELECT * FROM (`table_name`) WHERE `category` = 'electronics' AND
`foo_column` LIKE '%match%' LIMIT 10
and if $search = ''
, it will generate a request like
SELECT * FROM (`table_name`) WHERE `category` = 'electronics' LIMIT 10
source to share
the function $this->db->like()
skips the special characters it contains, including of course%.
I think it would be to work around the problem and use the where function that contains your clause LIKE
:
$this->db->select('*')->from('table')
->where("foo_column LIKE %$search%")->get();
source to share
Since version 3.0.0 using the "Query Builder" (compared to previous versions of "Active Record"), like()
there is an additional parameter for to prevent escaping of the match string:
$this->db->like('foo_column', '%match%something%', 'none', false)
- 3rd parameter =
'none'
disallows prefix / postfix extra wildcards - 4th parameter =
false
prevents escaping inline wildcards
Note that if you are using a variable for a match string and not a literal, you should use $this->db->escape_like_str($match)
when the 4th parameter isfalse
source to share