CodeIgniter "like()" function with % wildcard inside search terms
In order to achieve such kind of functionality I've updated your code with some different conditions as like.
Note: Here I've manually placed values of categories and search
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'll generate the query as follows:
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'll generate the query as
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'
the it'll generate the query as
SELECT * FROM (`table_name`) WHERE `category` = 'electronics' AND `foo_column` LIKE '%match%' LIMIT 10
and if $search = ''
the it'll generate the query as
SELECT * FROM (`table_name`) WHERE `category` = 'electronics' LIMIT 10
the $this->db->like()
function escapes special characters it contains - including, of course, %.
I guess, would be to bypass the problem and use a where function containing your LIKE
clause:
$this->db->select('*')->from('table')->where("foo_column LIKE %$search%")->get();
Starting in 3.0.0 using the "Query Builder" (vs. previous versions "Active Record") there is an additional parameter for like()
that lets you prevent escaping the match string:
$this->db->like('foo_column', '%match%something%', 'none', false)
- 3rd param =
'none'
prevents prefix/postfix of additional wildcard chars - 4th param =
false
prevents escaping of embedded wildcard chars
Note if you use a variable for your match string rather than a literal, you should use $this->db->escape_like_str($match)
when the 4th param is false