Let's say I have 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();
}
Produce query as :
SELECT * FROM table_name WHERE foo_column LIKE '%match something%'
As you can see the % wildcard can be added in both side, before and after.
And how if I want to produce like:
... WHERE foo_column LIKE '%match%something%'?
FYI, I use str_replace() function to change space to % but codeigniter always escape it with slash. It Produces query like:
... WHERE foo_column LIKE '%match\%something%'
This is useful when to search match another something with keyword match something, and wildcard on first and/or after seems doesn't work.
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments