CodeIgniter 4 Working With Database Query

How To Work with CodeIgniter 4 Database Queries Tutorial

by
Reading Time: 19 minutes
13,593 Views

Mastering database queries is fundamental for developing robust web applications. In this tutorial we’ll delve into working with database queries in CodeIgniter 4.

CodeIgniter, a powerful PHP framework, provides a straightforward and efficient way to interact with databases, enabling developers to retrieve, insert, update, and delete data effortlessly.

Database queries form the backbone of data manipulation and retrieval, allowing applications to interact with stored information effectively.

Read More: How To Cache Database Query Using CodeIgniter Cache?

Lets’s get started.

CodeIgniter 4 Installation

To create a CodeIgniter 4 setup run this given command into your shell or terminal. Please make sure composer should be installed.

composer create-project codeigniter4/appstarter codeigniter-4

Assuming you have successfully installed application into your local system.

Introduction of Database with CodeIgniter 4

CodeIgniter 4 by default comes with many configuration like – Email, Routes, Database, Security etc.

For database we have several options available to connect a CodeIgniter 4 application in a very easy way.

As per the latest concept, we will have a environment file of application i.e .env file (Initially it will be env make it to .env to use it).

In .env file we have database connectivity details like username, hostname, database name etc. In other alternative way we have Database.php file inside /app/Config.

So, basically we use these files to connect database with CodeIgniter 4 application.

We can connect multiple database inside a single application. We can load all by using their respective keys/connection variables. Inside CodeIgniter 4 we can also use multiple database drivers – MySQLi, Postgre, SQLite3.

Inside this article we will see database working with MySQLi db driver.

Environment (.env) Setup

When we install CodeIgniter 4, we will have env file at root. To use the environment variables means using variables at global scope we need to do env to .env

Either we can do via renaming file as simple as that. Also we can do by terminal command.

Open project in terminal

cp env .env

Above command will create a copy of env file to .env file. Now we are ready to use environment variables.

Enable Development Mode

CodeIgniter starts up in production mode by default. You need to make it in development mode to see any error if you are working with application.

Open .env file from root.

# CI_ENVIRONMENT = production

 // Do it to 
 
CI_ENVIRONMENT = development

Now application is in development mode.

Read More: CodeIgniter 4 How To Work with Spark CLI Tutorial

How to Connect Database with CodeIgniter 4

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

CREATE DATABASE codeigniter4_app;

Let’s see what are the parameters we need to pass to connect with the database. Open .env file from project root.

#--------------------------------------------------------------------
# DATABASE
#--------------------------------------------------------------------

 database.default.hostname = localhost
 database.default.database = codeigniter4_app
 database.default.username = root
 database.default.password = root
 database.default.DBDriver = MySQLi

In .env file you can see we have some lines like this which is asking for hostname, database, username, password & DB driver. This is the first option from where we connect to database.

In the alternative method (Database.php), but not recommended for this.

//...

public $default = [
		'DSN'      => '',
		'hostname' => 'localhost',
		'username' => 'root',
		'password' => 'root',
		'database' => 'codeigniter4_app',
		'DBDriver' => 'MySQLi',
		'DBPrefix' => '',
		'pConnect' => false,
		'DBDebug'  => (ENVIRONMENT !== 'production'),
		'cacheOn'  => false,
		'cacheDir' => '',
		'charset'  => 'utf8',
		'DBCollat' => 'utf8_general_ci',
		'swapPre'  => '',
		'encrypt'  => false,
		'compress' => false,
		'strictOn' => false,
		'failover' => [],
		'port'     => 3306,
];

//...

We should find something like this. Here also we need to provide the same details.

Load Database to Application

As we have discussed earlier i.e working with CodeIgniter 4 we have multiple ways to do database operations. Working with database options include –

Load Database Object to Controller

To perform database operation by using Raw Queries we need a database instance. By the help of that instance we connect with DB and perform insert, update, delete and etc operations.

Say, we are inside SiteController.php Controller file. Define or load database instance in class construct method. This method is best to load and use. Construct method is the default controller method of PHP classes which executes first when we create instance of that class.

We need to create a __construct() method and load database into it. Not only inside construct method even we can load it into any of the specific method.

// Example Controller

class SiteController extends BaseController
{
    public function __construct()
    {
        $this->db = \Config\Database::connect();
      
        //OR
      
        //$this->db = db_connect();
    }
}

$this->db is the object of database. We can perform all the CRUD operations by the help of this.

By default this connect method loads “default” database connection variable.

\Config\Database::connect()

\Config\Database is the service of CodeIgniter 4 and connect is the method we are using.

When we work with query builder class approach or by concept of model, there is no need to create instance this like this. Instead we create table instance in query builder and model instance in Model concept.

Working with Raw Queries in CodeIgniter 4

Raw Queries are direct queries what we run directly at PhpMyAdmin. These queries are of all types – insert, select etc.

Let’s see about database operations – 

//...

// Insert data
public function insertRaw()
{
  $query = "Insert into tbl_users(name, email, phone_no) values('Sanjay', 'sanjay@gmail.com', '2222222222')";

  if ($this->db->query($query)) {
    echo "<h3>Data has been inserted</h3>";
  } else {
    echo "<h3>Failed to insert data</h3>";
  }
}

//...

$this->db is the database instance which we have created in previous topic. By the help of this created instance we have called query method.

$query variable contains MySQL query, which we passed into $this->db->query() method. After insertion it returns last row insert_id.

Additionally, if we want to get the last executed query in bulk operations, simply we need call –

$this->db->getLastQuery()

Now, let’s see Update Operation – 

//...

// update query
public function updateRawQuery()
{
  $query = "Update tbl_users SET name = 'Sanju', email = 'online@gmail.com', phone_no = '7896541230' WHERE id = 2";

  if ($this->db->query($query)) {
    echo "<h3>Data has been updated</h3>";
  } else {
    echo "<h3>Failed to update data</h3>";
  }
}

//...

We have provided direct update query. On the basis of where condition we are updating data. The only difference from previous query is all about in query difference. Rest the process of calling is same.

Let’s run Delete Query – 

//...

// delete query
public function deleteRawQuery()
{
  $query = "Delete from tbl_users where id = 2";

  if ($this->db->query($query)) {
    echo "<h3>Data has been deleted</h3>";
  } else {
    echo "<h3>Failed to delete data</h3>";
  }
}

//...

This is all about deleting a data row on the basis of where condition.

Now, we will see that how can we select data by using raw query.

//...

// Select/Find
public function getData()
{
  //$data = $this->db->query("SELECT * from tbl_users")->getResult();
  
  //$data = $this->db->query("SELECT * from tbl_users")->getResult('array');

  //$data = $this->db->query("SELECT * from tbl_users")->getResultArray();

  //$data = $this->db->query("SELECT * from tbl_users WHERE id = 3")->getRow();
  
  $data = $this->db->query("SELECT * from tbl_users WHERE id = 3")->getRowArray();

  return $data;
}

//...

Query types –

SELECT * from tbl_users – This will select all users from table. It returns data in object format by default.

SELECT * from tbl_users WHERE id = 3 – It will help to find a specific user whose Id = 3

Here, you can find one thing in queries i.e getResult(), getResult(‘array’), getResultArray(), getRow(), getRowArray()

These methods are very useful to read data into formats. By default getResult() will work which returns data set into Object format. But in case if we want result in array format then we have 2 options – getResultArray() OR getResult(‘array’). These are the cases when we fetching all data.

When we select a specific row from table getRow() will work. It also returns data in Object format. When we want to convert data in array format, we need to use getRowArray() method.

Read More: How To Fix CodeIgniter Session Fixation & Hijacking Attack

Understanding Query Builder Class Method

Inside this approach we are not going to run mysql queries in direct way. Instead we will use some methods of $this->db instance. Keep in mind this $this->db instance we have created at initial stage of this article.

In the raw queries section, we have created database instance and worked, alright. In similar way, here we also need database object, but we will use table class methods.

Create Table Class Instance

//...

# Creating table instance
$builder = $this->db->table("tbl_users");

//...

We use the $builder instance to create & run the insert, update, delete & find methods.

Insert query by Table instance

//...

// Insert data
public function insertData()
{
  $builder = $this->db->table("tbl_users");

  $data = [
    "name" => "User 1",
    "email" => "user1@gmail.com",
    "phone_no" => "8888888888",
  ];

  return $builder->insert($data);
}

//...

Here, we inserting data into table. We have used insert method and passed data into key values pairs. Keys are the table column names.

Also, you can see here we are not using the raw query, Instead we are using insert() method. In case if we want to insert bulk data into table, have a look – 

//...

// create insert data
$data = [
  array(
    "name" => "Data 1",
    "email" => "data1@gmail.com",
    "phone_no" => "555555555",
  ),
  array(
    "name" => "Data 2",
    "email" => "data2@gmail.com",
    "phone_no" => "66666666666",
  ),
];

// bulk insert
$return_data = $builder->insertBatch($data);

//...

Let’s run Update Query –

//...

// update query
public function updateData()
{
        $builder = $this->db->table("tbl_users");
  
        $updated_data = [
            "name" => "Data",
            "email" => "updated-data@dummy.com",
            "phone_no" => "8888888",
        ];
  	    
        $builder->where([
            "id" => 8
        ]);
        $builder->set($updated_data);
  
        return $builder->update(); 
}

//...

Update query – by the help of where condition means at id = 8 we are updating record. For update operation we have used update method. Here, we have used 3 different methods, where() method for where condition. set() method is used set values in key value pairs. Finally we have used update().

We can use all these methods in one line as well. This type is called chaining of methods.

//...

// update query - chaining method
public function updateData()
{
        $builder = $this->db->table("tbl_users");
  
        $updated_data = [
            "name" => "Data",
            "email" => "updated-data@dummy.com",
            "phone_no" => "8888888",
        ];
  	    
        return $builder->where([
            "id" => 8
        ])->set($updated_data)->update();
}

//...

Delete Query by Query builder class

Delete data by using where condition, we need to use delete() method. This delete method we will call using $builder instance.

//...

// Delete query
public function deleteData()
{
  $builder = $this->db->table("tbl_users");

  $id = 9;

  $builder->where([
    "id" => $id,
  ]);

  return $builder->delete();
}

//...

If suppose, we have used several queries in an application. So detect or generate last executed query we will use the same logical query as –

$this->db->getLastQuery();

Let’s run select/find method to get data.

//...

// Select/Find data
public function getData()
{
  $builder = $this->db->table("tbl_users");

  // lets add where condition
  //$builder = $builder->where("id", 4);
  //$builder = $builder->where("email", "online@gmail.com");

  $builder = $builder->where(array(
    "id" => 4,
    "email" => "online@gmail.com",
  ));

  // select all data
  $data = $builder->get()->getRow();

  //echo $this->db->getLastQuery();

  //$data = $this->db->query("SELECT * from tbl_users");

  //$data = $this->db->table("tbl_users")->get()->getResult();

  return $data;
}

//...

Inside this code snippet, you can see we have two types of queries – First query which returns all data and Second which returns the data on the basis of where condition. All data format methods getRow(), getRowArray(), getResult(), getResultArray() we can use it here.

About Models in CodeIgniter 4

Models are those files which stored inside /app/Models. They are responsible to connect to any specific table. By the help of model we can do all database operations.

Before using models in CodeIgniter, we have to do it’s basic settings. Basic settings includes table name, primary key, allowed fields and many more.

$ php spark make:model Student --suffix
<?php

namespace App\Models;

use CodeIgniter\Model;

class StudentModel extends Model
{
	protected $DBGroup              = 'default';
	protected $table                = 'tbl_users';
	protected $primaryKey           = 'id';
	protected $useAutoIncrement     = true;
	protected $insertID             = 0;
	protected $returnType           = 'array';
	protected $useSoftDelete        = false;
	protected $protectFields        = true;
	protected $allowedFields        = [
		"name", 
		"email", 
		"phone_no"
	];

	// Dates
	protected $useTimestamps        = false;
	protected $dateFormat           = 'datetime';
	protected $createdField         = 'created_at';
	protected $updatedField         = 'updated_at';
	protected $deletedField         = 'deleted_at';

	// Validation
	protected $validationRules      = [];
	protected $validationMessages   = [];
	protected $skipValidation       = false;
	protected $cleanValidationRules = true;

	// Callbacks
	protected $allowCallbacks       = true;
	protected $beforeInsert         = [];
	protected $afterInsert          = [];
	protected $beforeUpdate         = [];
	protected $afterUpdate          = [];
	protected $beforeFind           = [];
	protected $afterFind            = [];
	protected $beforeDelete         = [];
	protected $afterDelete          = [];
}

Inside this variable $allowedFields, generally we specify all columns of table. To perform any insert operation we must need to supply all columns here. Other wise if we don’t then those columns will not available for mass assignment.

It’s my recommendation from all methods of database like raw query, query builder class we should use Models based concept. This is pretty very simple and more secure. Neat and clean method it is.

For defining user model class, we must need to extends Model class as the parent class. Let’s see CRUD operations using Model.

//...

# Load Model First
use App\Models\StudentModel;

# Insert data
public function insertData()
{
  $userModel = new StudentModel();
  // create insert data
  
  /*$data = array(
      "name" => "TTT2",
      "email" => "ttt2@gmail.com",
      "phone_no" => "53454354545",
   );*/
  
  $data = [
    array(
      "name" => "TTT2",
      "email" => "ttt2@gmail.com",
      "phone_no" => "53454354545",
    ),
    array(
      "name" => "TTT3",
      "email" => "ttt3@gmail.com",
      "phone_no" => "7665353453",
    ),
  ];

  // insert method call
  $return_data = $userModel->insertBatch($data);
  //$return_data = $userModel->insert($data);

  echo $return_data;
}

//...

Inside this insert statement, we can see we have used insert() method and insertBatch() method. insert() method is used when we are inserting a single data row, but in case if we want to insert multiple data rows in a single command – we need to use insertBatch() method.

Update data using Model

//...

# To update data
public function updateData()
{
  $userModel = new StudentModel();
  
  $data = [
    "name" => "Updated TTT",
    "email" => "updated_ttt@gmail.com",
    "phone_no" => "8975645132",
  ];

  return $userModel->where([
    "id" => $update_id,
  ])->set($data)->update();
}

//...

Delete Operation using Model – Pretty simple methods we are learning.

//...

// Delete data
public function deleteData()
{
  $userModel = new StudentModel();

  return $userModel->where([
    "id" => $delete_id,
  ])->delete();
}

//...

To learn more about CodeIgniter 4 Model & Enity, Click here.

That’s it.

We hope this article helped you to learn about How To Work with CodeIgniter 4 Database Queries Tutorial in a very detailed way.

Online Web Tutor invites you to try Skillshike! Learn CakePHP, Laravel, CodeIgniter, Node Js, MySQL, Authentication, RESTful Web Services, etc into a depth level. Master the Coding Skills to Become an Expert in PHP Web Development. So, Search your favourite course and enroll now.

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.