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

Question

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.

+3

php mysql codeigniter query-builder

JrrMaster May 14 '15 at 4:07

source to share

3 answers

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

+1

Saty May 14 '15 at 5:41

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

0

user9645 June 13. 19 at 12:53

source to share

Narendrasingh Sisodia · Accepted Answer · 2015-05-14T05:43:35+0000

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

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

More articles: