Codeigniter LIKE with wildcard(%)

Question

I have simple database query in codeigniter, however I cannot get search to work with wildcard. This is my code:

$this->db->like('film.title',"%$query%");
$this->db->escape_like_str($query);
$res = $this->db->get('film');

If I remove wildcard(%) then search works fine. Also $query is just string with user input. Any help is appreciated.

Solution 1

$this->db->like() automatically adds the %s and escapes the string. So all you need is

$this->db->like('title', $query);
$res = $this->db->get('film');

See the CI documentation for reference: CI 2, CI 3, CI 4

Solution 2

  $this->db->like('title', 'match', 'before'); 
 // Produces: WHERE title LIKE '%match' 

 $this->db->like('title', 'match', 'after'); 
// Produces: WHERE title LIKE 'match%' 

$this->db->like('title', 'match', 'both'); 
// Produces: WHERE title LIKE '%match%'

Solution 3

For Full like you can user :

$this->db->like('title',$query);

For %$query you can use

$this->db->like('title', $query, 'before');

and for $query% you can use

$this->db->like('title', $query, 'after');

Solution 4

$this->db->like()

This method enables you to generate LIKE clauses, useful for doing searches.

$this->db->like('title', 'match');

Produces: WHERE title LIKE '%match%'

If you want to control where the wildcard (%) is placed, you can use an optional third argument. Your options are ‘before’, ‘after’, ‘none’ and ‘both’ (which is the default).

$this->db->like('title', 'match', 'before');

Produces: WHERE title LIKE '%match'

$this->db->like('title', 'match', 'after');

Produces: WHERE title LIKE 'match%'

$this->db->like('title', 'match', 'none');

Produces: WHERE title LIKE 'match'

$this->db->like('title', 'match', 'both');

Produces: WHERE title LIKE '%match%'

Solution 5

If you do not want to use the wildcard (%) you can pass to the optional third argument the option 'none'.

$this->db->like('title', 'match', 'none'); 
// Produces: WHERE title LIKE 'match'

View more solutions