CodeIgniter 3 provides two primary methods for connecting to a database: Automatic Connection and Manual Connection. This tutorial will guide you through both approaches, including connecting to multiple databases in a single application.

1. Automatically Connecting to the Database

The automatic connection method in CodeIgniter allows you to connect to your database automatically with every page load, eliminating the need to manually load the database in each controller.

Step 1: Enable Auto-Connect in CodeIgniter

To enable automatic database connections, you need to modify the autoload.php file, which is located in the application/config/ directory.

1. Open the autoload.php file.
2. Locate the $autoload['libraries'] array.
3. Add the database library to the array.

Here’s how your configuration should look:

With this configuration, CodeIgniter will automatically load the database library on every page request, making the database connection available throughout your application.

2. Manually Connecting to the Database

If you want more control over when the database is loaded, or if you only need to connect to the database on specific pages, you can load the database manually in your controllers or models.

How to Manually Connect to the Database

To manually connect to the database, you can use the $this->load->database() method. This method should be called within the controller or model where you need to access the database.

Example of Manual Database Connection in a Controller:

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Welcome extends CI_Controller {

    public function index() {
        // Connect to the first database group
        $DB1 = $this->load->database('group_one', TRUE);

        // Connect to the second database group
        $DB2 = $this->load->database('group_two', TRUE);

        // Query the first database
        $query1 = $DB1->get('table1');
        foreach ($query1->result() as $row) {
            echo $row->column_name . '<br>';
        }

        // Query the second database
        $query2 = $DB2->get('table2');
        foreach ($query2->result() as $row) {
            echo $row->column_name . '<br>';
        }
    }
}

In this example, the database library is loaded only when the index method is called, making it more efficient if your application does not need the database for every page request.

3. Connecting to Multiple Databases

CodeIgniter also allows you to connect to multiple databases within the same application. This can be useful if you need to access different databases for different tasks, such as reading from a reporting database while writing to a primary database.

Step 1: Configure Multiple Database Groups

To connect to multiple databases, you need to define the database connection groups in your database.php configuration file, which is located in the application/config/ directory.

Example of database.php Configuration:

// application/config/database.php
$active_group = 'default';
$query_builder = TRUE;

$db['default'] = array(
'dsn'    => '',
'hostname' => 'localhost',
'username' => 'root',
'password' => '',
'database' => 'primary_database',
'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
);

$db['group_one'] = array(
'dsn'    => '',
'hostname' => 'localhost',
'username' => 'user1',
'password' => 'password1',
'database' => 'database_one',
'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
);

$db['group_two'] = array(
'dsn'    => '',
'hostname' => 'localhost',
'username' => 'user2',
'password' => 'password2',
'database' => 'database_two',
'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
);

Example of Connecting to Multiple Databases:

// application/controllers/Welcome.php
<?php 
defined('BASEPATH') OR exit('No direct script access allowed');

class Welcome extends CI_Controller {

    public function index() {
        // Connect to the first database group
        $DB1 = $this->load->database('group_one', TRUE);

        // Connect to the second database group
        $DB2 = $this->load->database('group_two', TRUE);

        // Query the first database
        $query1 = $DB1->get('table1');
        foreach ($query1->result() as $row) {
            echo $row->column_name . '<br>';
        }

        // Query the second database
        $query2 = $DB2->get('table2');
        foreach ($query2->result() as $row) {
            echo $row->column_name . '<br>';
        }
    }
}

Explanation:

1. Automatic Connection: The database library is autoloaded with each request, useful when database access is needed throughout the application.
2. Manual Connection: The database is connected only when explicitly needed, allowing for more control and efficiency.
3. Multiple Database Connections: Multiple databases can be connected by specifying different configuration groups, allowing access to multiple data sources within the same application.