CodeIgniter 4 Models with Multiple Database Connection

Reading Time: 9 minutes
15,349 Views

If we are building a large project like eCommerce or CRM, hopefully we should add more than one database to manage everything like inventory, leads, customers etc.

Inside this article we will learn the concept of CodeIgniter 4 models with multiple database connection. This tutorial will be very easy to learn and to follow.

How to work with multiple databases in a codeigniter 4 application, you will surely handle those applications after completing this article.

Learn More –

Let’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.


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.


Create Database & Table in Application

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

CREATE DATABASE codeigniter4_ecommerce;

CREATE DATABASE codeigniter4_crm;

Now, we have 2 databases. Let’s add few tables inside these.

# Database: codeigniter4_ecommerce

CREATE TABLE products (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(120) DEFAULT NULL,
cost int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

# Database: codeigniter4_crm

CREATE TABLE customers (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(120) DEFAULT NULL,
email varchar(120) DEFAULT NULL,
mobile varchar(45) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Next, connect with the application.


Database Connectivity to Application

Open .env file from project root. Search for DATABASE. You should see the connection environment variables.

Let’s set the value for those to connect with database.

As we have multiple databases, need to follow steps –

Step #1

Add database connection in .env file.

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

# eCommerce Database
database.default.hostname = localhost
database.default.database = codeigniter4_ecommerce
database.default.username = admin
database.default.password = Admin@123
database.default.DBDriver = MySQLi
database.default.DBPrefix =

# CRM Database
database.crmdb.hostname = localhost
database.crmdb.database = codeigniter4_crm
database.crmdb.username = admin
database.crmdb.password = Admin@123
database.crmdb.DBDriver = MySQLi
database.crmdb.DBPrefix =
  

Step #2

Open Database.php file from /app/Config folder.

Add crmdb database connection array.

//...

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

//...

Now, database successfully connected with application.


Create Test Data For Tables

We need some fake data for above tables.

Run these mysql queries to database and insert test data for this article.

Dummy data for ‘products’ table

products table is from codeigniter4_ecommerce database.

--
-- Dumping data for table `products`
--

INSERT INTO `products` (`id`, `name`, `cost`) VALUES
(1, 'Product 1', 100),
(2, 'Product 2', 150),
(3, 'Product 3', 80);

Dummy data for ‘customers’ table

customers table is from codeigniter4_crm database.

--
-- Dumping data for table `customers`
--

INSERT INTO `customers` (`id`, `name`, `email`, `mobile`) VALUES
(1, 'Customer 1', 'customer1@gmail.com', '1234567890'),
(2, 'Customer 2', 'customer2@gmail.com', '1234567880'),
(3, 'Customer 3', 'customer3@gmail.com', '9638527401');

Create Model

Open project into terminal and run these spark commands.

# Model for 'products' table

$ php spark make:model Product --suffix

# Model for 'customers' table

$ php spark make:model Customer --suffix

These commands will create ProductModel.php and CustomerModel.php files at /app/Models folder.

Open ProductModel.php and write this complete code into it.

<?php

namespace App\Models;

use CodeIgniter\Model;

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

	// 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          = [];
}
  • protected $DBGroup = ‘default’; default connection points to codeigniter4_ecommerce. We can see in .env file.

Open CustomerModel.php and write this complete code into it.

<?php

namespace App\Models;

use CodeIgniter\Model;

class CustomerModel extends Model
{
	protected $DBGroup              = 'crmdb'; // CRM database
	protected $table                = 'customers';
	protected $primaryKey           = 'id';
	protected $useAutoIncrement     = true;
	protected $insertID             = 0;
	protected $returnType           = 'array';
	protected $useSoftDelete        = false;
	protected $protectFields        = true;
	protected $allowedFields        = [
		"name", 
		"email", 
		"mobile"
	];

	// 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          = [];
}
  • protected $DBGroup = ‘crmdb’; crmdb points to codeigniter4_crm connection. We can see in .env file.

Now, successfully we can see we have two models but they are connected with different databases.

Let’s see how to load and use them.


Create Controller & Load Models

Back to terminal and run this spark command to create controller.

$ php spark make:controller Site --suffix

It will creates SiteController.php file inside /app/Controllers folder.

Open SiteController.php and write this complete code into it.

<?php

namespace App\Controllers;

use App\Models\CustomerModel;
use App\Models\ProductModel;

class SiteController extends BaseController
{
	public function getData()
	{
        // crmdb
		$customers = new CustomerModel();

		echo "<pre>";
		print_r($customers->findAll());
		echo "</pre>";

        // ecommerce
		$products = new ProductModel();

		echo "<pre>";
		print_r($products->findAll());
		echo "</pre>";
	}
}

Successfully, now we can get data from multiple databases. Here we did only select. You can perform any operation like insert, update, delete etc.

We hope this article helped you to learn CodeIgniter 4 Models with Multiple Database Connection 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.