CODEWITHSUNDEEP
phpmysqlcodeigniterquery-builder
JrrMaster
JrrMaster

Reputation: 65

CodeIgniter "like()" function with % wildcard inside search terms

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.

Upvotes: 3

Views: 4281

Answers (3)

user9645
user9645

Reputation: 6806

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

Upvotes: 0

Narendrasingh Sisodia
Narendrasingh Sisodia

Reputation: 21422

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

Upvotes: 2

Saty
Saty

Reputation: 22532

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();

Upvotes: 1

Related Questions