Reading Time: 8 minutes

12,303 Views

CodeIgniter 4 provides the complete set of Query builder methods to use in querying database. Inside this article we will see the concept of MySQL Like Operator in codeigniter 4 Query builder.

In Query builder to use Like operator we have a method available. Like operator helps us to get data with a certain pattern with a different different string format.

MySQL Like Operator uses % or _ in query to create pattern. % represents multiple characters, _ is for single character.

In CodeIgniter, It uses only % to create search patterns.

Learn More –

Let’s get started.

Create Database

To create a database, either we can create via Manual tool of PhpMyadmin or by means of a mysql command.

We will use MySQL command to create database. Run this command into Sql tab of PhpMyAdmin.

CREATE DATABASE codeigniter4_app;

Successfully, we have created a database.

Create Database Table

Next, we need a table. That table will be responsible to store data.

Let’s create a table with some columns.

CREATE TABLE `students` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(50) NOT NULL,
 `email` varchar(50) NOT NULL,
 `phone_no` varchar(15) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Successfully, we have created a table.

Dump Test Data To Table

Open MySQL database and run this command to insert test data into table.

--
-- Dumping data for table `students`
--

INSERT INTO `students` (`id`, `name`, `email`, `phone_no`) VALUES
(1, 'Sanjay Kumar', 'sanjay_kumar@gmail.com', '8527419630'),
(2, 'Sanjay Mishra', 'sanjaymishra1001@yahoo.com', '7456321089'),
(3, 'Rahul Khanna', 'rahulkhanna@yahoo.com', '9632145870'),
(4, 'Vijay Singh', 'vijay_singh@gmail.com', '7896541230'),
(5, 'Suraj Lal', 'suraj_lal@outlook.com', '7894563210'),
(6, 'Dhananjay Kumar', 'dhananjay_kumar@outlook.com', '8529633217');

Here, we have few rows taking to understand the concept of like() method. You can consider many more rows.

CodeIgniter 4 Like Operator Method

In CodeIgniter 4, like() named method available to do like pattern queries.

$builder->like('column_name','match', 'before | after | both')

Example #1

Task

We want to list all students whose name starts from S character.

MySQL Query

SELECT * FROM students WHERE name LIKE 'S%'

Writing in CodeIgniter 4

We will use query builder methods for this.

Like Method syntax –

$builder->like('name','S', 'after');

It generates query as –

WHERE `name` LIKE 'S%' ESCAPE '!'

<?php

namespace App\Controllers;

class Home extends BaseController
{
	public function __construct()
	{
		$this->db = db_connect();
	}

	public function index()
	{
		$builder = $this->db->table("students");

		$builder->select('*');
		$builder->like('name','S','after');
		$query = $builder->get();

		echo "<pre>";
		print_r($query->getResult());
      
        // To get last query executed
        // $this->db->getLastQuery();
	}
    
    //...
}

Behind the scene it will generate query as –

SELECT * FROM `students` WHERE `name` LIKE 'S%' ESCAPE '!'

If we run this query to MySQL –

Output To Browser

Example #2

Task

We want to list all students whose email ends with yahoo.com

MySQL Query

SELECT * FROM students WHERE email LIKE '%yahoo.com'

Writing in CodeIgniter 4

We will use query builder methods for this.

Like Method syntax –

$builder->like('email','yahoo.com', 'before');

It generates query as –

WHERE `email` LIKE '%yahoo.com' ESCAPE '!'

<?php

namespace App\Controllers;

class Home extends BaseController
{
	public function __construct()
	{
		$this->db = db_connect();
	}

	public function index()
	{
		$builder = $this->db->table("students");

		$builder->select('*');
		$builder->like('email','yahoo.com','before');
		$query = $builder->get();

		echo "<pre>";
		print_r($query->getResult());
      
        // To get last query executed
        // $this->db->getLastQuery();
	}
    
    //...
}

Behind the scene it will generate query as –

SELECT * FROM `students` WHERE `email` LIKE '%yahoo.com' ESCAPE '!'

If we run this query to MySQL –

Output To Browser

Example #3

Task

We want to list all students whose phone number contains 321 in digits.

MySQL Query

SELECT * FROM students WHERE phone_no LIKE '%321%'

Writing in CodeIgniter 4

We will use query builder methods for this.

Like Method syntax –

$builder->like('phone_no','%321%', 'both');

It generates query as –

WHERE `phone_no` LIKE '%321%' ESCAPE '!'

<?php

namespace App\Controllers;

class Home extends BaseController
{
	public function __construct()
	{
		$this->db = db_connect();
	}

	public function index()
	{
		$builder = $this->db->table("students");

		$builder->select('*');
		$builder->like('phone_no','321','both');
		$query = $builder->get();

		echo "<pre>";
		print_r($query->getResult());
      
        // To get last query executed
        // $this->db->getLastQuery();
	}
    
    //...
}

Behind the scene it will generate query as –

SELECT * FROM `students` WHERE `phone_no` LIKE '%321%' ESCAPE '!'

If we run this query to MySQL –

Output To Browser

Example #4

Task

We want to list all students whose name starts from S OR phone number contains 321 in digits.

Any condition will true then it returns a data row.

MySQL Query

SELECT * FROM students WHERE name LIKE 'S%' OR phone_no LIKE '%321%'

Writing in CodeIgniter 4

$builder = $this->db->table("students");

$builder->select('*');
$builder->like('name','S','after');
$builder->orLike('phone_no','321','both');
$query = $builder->get();

Example #5

Task

We want to list all students whose phone number doesn’t contains 321 in digits.

MySQL Query

SELECT * FROM students WHERE phone_no NOT LIKE '%321%'

Writing in CodeIgniter 4

$builder = $this->db->table("students");

$builder->select('*');
$builder->notLike('phone_no','321','both');
$query = $builder->get();

We hope this article helped you to learn MySQL Like Operator in CodeIgniter 4 Query Builder Tutorial in a very detailed way.

If you liked this article, then please subscribe to our YouTube Channel for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on Twitter and Facebook.