How to Connect Multiple Databases in CodeIgniter

Last week, I wrote the basic CodeIgniter3 tutorial series. I picked the CodeIgniter3 for my small web application because it is lightweight, flexible and really fast development. My project had the very tight deadline so CodeIgniter was the perfect one. I would suggest the PHP developer who never worked on the Model-View-Controller (MVC) pattern to start with CodeIgniter3. For the PHP developers who have experiences for development, can learn the CodeIgniter very quick. I learned within 2 days.

In my project, the application needs to connect two databases in order to display the reports to the end-user. One database(MariaDB) is on the web hosting where the application lives for. Another database(PostgreSQL) is a remote database which is used for the mobile app.

Database settings

First, you want to set the database settings at application/config/database.php. Below is my database settings for my two databases.

MariaDB – MariaDB uses the same DB driver with MySQL which is mysqli.

$db['default'] = array(
	'dsn'	=> '',
	'hostname' => 'localhost:3307',
	'username' => 'YourDBUsername',
	'password' => 'YourDBPassword',
	'database' => 'YourDBName',
	'dbdriver' => 'mysqli',
	'dbprefix' => '',
	'pconnect' => FALSE,
	'db_debug' => (ENVIRONMENT !== 'production'),
	'cache_on' => FALSE,
	'cachedir' => '',
	'char_set' => 'utf8',
	'dbcollat' => 'utf8_general_ci',
	'swap_pre' => '',
	'encrypt' => FALSE,
	'compress' => FALSE,
	'stricton' => FALSE,
	'failover' => array(),
	'save_queries' => TRUE
);

PostgreSQL – PostgreSQL needs to install and enable pdo_pgsql and pgsql extensions in order to connect to PostgreSQL.

$db['data_api'] = array(
	'dsn'	=> 'pgsql:host=YourHostName;port=5432;dbname=YourDBName;user=YourDBUsername;password=YourDBPassword',
	'hostname' => 'YourHostName',
	'username' => 'YourDBUsername',
	'password' => 'YourDBPassword',
	'database' => 'YourDBName',
	'dbdriver' => 'pdo',
	'dbprefix' => '',
	'pconnect' => FALSE,
	'db_debug' => (ENVIRONMENT !== 'production'),
	'cache_on' => FALSE,
	'cachedir' => '',
	'char_set' => 'utf8',
	'dbcollat' => 'utf8_general_ci',
	'swap_pre' => '',
	'encrypt' => FALSE,
	'compress' => FALSE,
	'stricton' => FALSE,
	'failover' => array(),
	'save_queries' => TRUE,
	'port' => '5432'
);

If you want to connect another database, simply create another $db array and set the database connection in there. Then in the Controller, you just need to load the database with this code.

$data = $this->load->database(‘data_api’, TRUE);

Notice that, data_api is the same name that we set in the $db array.

See the next section for fetching the data.

Fetch data in Controllers

The database settings are done. Next, we will fetch the data in our controller. For example, I want to fetch data from the User Controller. I will create a fetch_api method for fetching the data from PostgreSQL.

Here is my sample code for the User Controller and fetch_api method.

<?php
class Users extends CI_Controller
{
    // fetch data from PostgreSQL
    public function fetch_api()
    {
        // connect the PostgreSQL
        $data = $this->load->database('data_api', TRUE);
        // fetch data from the remote_users table
        $query = $data ->get('remote_users');

        // Add your code here
        var_dump($query->result());
    }
} // Users class ends

For the MariaDB, it is connected automatically because we set the active default group to be MariaDB connection in application/config/database.php.

If you are new to CodeIgniter, you can read the CodeIgniter tutorial from here.