CodeIgniter Like Query Example

CodeIgniter Like Query Example: This tutorial shows you how to write like query in codeigniter. Like clause is used in 'SELECT Query' to search for the matching pattern in columns. The operator is part of the where clause and employs wildcard character (%) to search for the matching string. Generally like clause can be used when only a portion of text/value is known. You can have multiple like conditions in a select query which should be chained together with 'AND/OR' operators.

Here we'll see how to write like query in codeigniter using Active Record Class lib.

CodeIgniter Like Query Example:

In codeigniter framework, we have to use the function $this->db->like(); to generate the LIKE clause. It should be used along with codeigniter select query to automatically generate where and like operators. Code igniter also supports several variations of like clause which we'll see later in this article.

$this->db->like('column', 'pattern');

// Generates: WHERE column LIKE '%pattern%' 

Take this mysql table 'Books' as an example.

This like query in codeigniter searches the table 'Books' for the records having the substring 'Power' in the column 'BookName'.

$this->db->select('*');
$this->db->from('Books');
$this->db->like('BookName', 'Power');
$query = $this->db->get();

// Produces SQL:
// SELECT * FROM Books WHERE BookName LIKE '%Power%';

Query Result:

When you pass only two parameters to the like() function, codeigniter places '%' before and after the pattern string. But you can control the placement of the wildcard character by using the optional third parameter.

Control Wildcard(%) Placement in Like Query:

Pass 'before' to like() function if you want '%' to be placed only at the start of the string or 'after' if you want it at the end of the string. Use 'both' if you want it at the start and end of the string.

$this->db->from('Books');
$this->db->like('Category', 'Self', 'before');
$query = $this->db->get();

// Produces SQL:
// SELECT * FROM Books WHERE Category LIKE '%Self';

Query Result:

Above query returns zero results as there will be no records with column 'Category' ending with 'self' value.

$this->db->from('Books');
$this->db->like('Category', 'Self', 'after');
$query = $this->db->get();

// Produces SQL:
// SELECT * FROM Books WHERE Category LIKE 'Self%';

Codeigniter Multiple Like Query:

As said earlier you can have multiple like clauses in a single select query. For that use multiple instances of like() function which will be combined with 'AND' operator.

$this->db->select('*');
$this->db->from('Books');
$this->db->like('Author', 'Robert', 'after');
$this->db->like('Category', 'Self');
$query = $this->db->get();

// Produces SQL:
// SELECT * FROM Books WHERE Author LIKE 'Robert%' AND Category LIKE '%Self%';

Result:

Like Array in CodeIgniter:

You can also pass multiple like conditions as an associative array to the $this->db->like() function.

$array = array('BookName' => 'Power', 'Author' => 'e', 'ISBN' => '14');
$this->db->select('*');
$this->db->from('Books');
$this->db->like($array);
$query = $this->db->get();

// Produces SQL:
// SELECT * FROM Books WHERE BookName LIKE '%Power%' AND Author LIKE '%e%' AND ISBN LIKE '%14%';

Codeigniter LIKE OR Query Example:

Active Records library also supports other variations of like() methods. If you want to combine multiple like clauses with 'OR' operator then use $this->db->or_like() function.

$this->db->from('Books');
$this->db->like('BookName', 'power');
$this->db->or_like('Category', 'lit');
$query = $this->db->get();

// Produces SQL:
// SELECT * FROM Books WHERE BookName LIKE '%power%' OR Category LIKE '%lit%';

Resultset:

Codeigniter NOT LIKE Query Example:

Use $this->db->not_like() function to generate NOT LIKE clause in codeigniter.

$this->db->select('*');
$this->db->from('Books');
$this->db->like('Category', 'self');
$this->db->not_like('ISBN', '978-14');
$query = $this->db->get();

// Produces SQL:
// SELECT * FROM Books WHERE Category LIKE '%self%' AND ISBN NOT LIKE '%978-14%';

Using $this->db->or_not_like() Method:

You can use $this->db->or_not_like() function when you want to combine NOT LIKE clause with OR operator.

$this->db->select('*');
$this->db->from('Books');
$this->db->like('BookName', 'grow');
$this->db->or_not_like('Author ', 'll');
$query = $this->db->get();

// Produces SQL:
// SELECT * FROM Books WHERE BookName LIKE '%grow%' OR Author NOT LIKE '%ll%';

Read Also:

• CodeIgniter Inner Join Query Example
• CodeIgniter Left Join Query Example

That was all about codeigniter like query. You can use above examples to write like query in codeigniter.

Tweet