[mysql] Codeigniter - multiple database connections

I have to retrieve a MySQL database information from master database and then connect to that database, and fetch some records.

I mean that holding one database I want to load another database.

Is it possible with Codeigniter? Right now I'm using following lines of code in my model.

function connectDb($credential)
{

    $config['hostname'] = $credential['server'];
    $config['username'] = $credential['username'];
    $config['password'] = $credential['password'];
    $config['database'] = $credential['database'];
    $config['dbdriver'] = "mysql";
    $config['dbprefix'] = "";
    $config['pconnect'] = FALSE;
    $config['db_debug'] = TRUE;
    $config['cache_on'] = FALSE;
    $config['cachedir'] = "";
    $config['char_set'] = "utf8";
    $config['dbcollat'] = "utf8_general_ci";

    $DB2=$this->load->database($config);

    $DB2->db->select('first_name,last_name');
    $query = $DB2->db->get('person');
    print_r($query);

}

its not working is there any other way?

This question is related to mysql database codeigniter

The answer is


It works fine for me...

This is default database :

$db['default'] = array(
    'dsn'   => '',
    'hostname' => 'localhost',
    'username' => 'root',
    'password' => '',
    'database' => 'mydatabase',
    'dbdriver' => 'mysqli',
    'dbprefix' => '',
    'pconnect' => TRUE,
    '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
);

Add another database at the bottom of database.php file

$db['second'] = array(
    'dsn'   => '',
    'hostname' => 'localhost',
    'username' => 'root',
    'password' => '',
    'database' => 'mysecond',
    'dbdriver' => 'mysqli',
    'dbprefix' => '',
    'pconnect' => TRUE,
    '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
);

In autoload.php config file

$autoload['libraries'] = array('database', 'email', 'session');

The default database is worked fine by autoload the database library but second database load and connect by using constructor in model and controller...

<?php
    class Seconddb_model extends CI_Model {
        function __construct(){
            parent::__construct();
            //load our second db and put in $db2
            $this->db2 = $this->load->database('second', TRUE);
        }

        public function getsecondUsers(){
            $query = $this->db2->get('members');
            return $query->result(); 
        }

    }
?>

You should provide the second database information in `application/config/database.php´

Normally, you would set the default database group, like so:

$db['default']['hostname'] = "localhost";
$db['default']['username'] = "root";
$db['default']['password'] = "";
$db['default']['database'] = "database_name";
$db['default']['dbdriver'] = "mysql";
$db['default']['dbprefix'] = "";
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = FALSE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = "";
$db['default']['char_set'] = "utf8";
$db['default']['dbcollat'] = "utf8_general_ci";
$db['default']['swap_pre'] = "";
$db['default']['autoinit'] = TRUE;
$db['default']['stricton'] = FALSE;

Notice that the login information and settings are provided in the array named $db['default'].

You can then add another database in a new array - let's call it 'otherdb'.

$db['otherdb']['hostname'] = "localhost";
$db['otherdb']['username'] = "root";
$db['otherdb']['password'] = "";
$db['otherdb']['database'] = "other_database_name";
$db['otherdb']['dbdriver'] = "mysql";
$db['otherdb']['dbprefix'] = "";
$db['otherdb']['pconnect'] = TRUE;
$db['otherdb']['db_debug'] = FALSE;
$db['otherdb']['cache_on'] = FALSE;
$db['otherdb']['cachedir'] = "";
$db['otherdb']['char_set'] = "utf8";
$db['otherdb']['dbcollat'] = "utf8_general_ci";
$db['otherdb']['swap_pre'] = "";
$db['otherdb']['autoinit'] = TRUE;
$db['otherdb']['stricton'] = FALSE;

Now, to actually use the second database, you have to send the connection to another variabel that you can use in your model:

function my_model_method()
{
  $otherdb = $this->load->database('otherdb', TRUE); // the TRUE paramater tells CI that you'd like to return the database object.

  $query = $otherdb->select('first_name, last_name')->get('person');
  var_dump($query);
}

That should do it. The documentation for connecting to multiple databases can be found here: http://codeigniter.com/user_guide/database/connecting.html


If you need to connect to more than one database simultaneously you can do so as follows:

$DB1 = $this->load->database('group_one', TRUE);
$DB2 = $this->load->database('group_two', TRUE);

Note: Change the words “group_one” and “group_two” to the specific group names you are connecting to (or you can pass the connection values as indicated above).

By setting the second parameter to TRUE (boolean) the function will return the database object.

Visit https://www.codeigniter.com/userguide3/database/connecting.html for further information.


Use this.

$dsn1 = 'mysql://user:password@localhost/db1';
$this->db1 = $this->load->database($dsn1, true);     

$dsn2 = 'mysql://user:password@localhost/db2';
$this->db2= $this->load->database($dsn2, true);     

$dsn3 = 'mysql://user:password@localhost/db3';
$this->db3= $this->load->database($dsn3, true);   

Usage

$this->db1 ->insert('tablename', $insert_array);
$this->db2->insert('tablename', $insert_array);
$this->db3->insert('tablename', $insert_array);

While looking at your code, the only thing I see wrong, is when you try to load the second database:

$DB2=$this->load->database($config);

When you want to retrieve the database object, you have to pass TRUE in the second argument.

From the Codeigniter User Guide:

By setting the second parameter to TRUE (boolean) the function will return the database object.

So, your code should instead be:

$DB2=$this->load->database($config, TRUE);

That will make it work.


The best way is to use different database groups. If you want to keep using the master database as usual ($this->db) just turn off persistent connexion configuration option to your secondary database(s). Only master database should work with persistent connexion :

Master database

$db['default']['hostname'] = "localhost";
$db['default']['username'] = "root";
$db['default']['password'] = "";
$db['default']['database'] = "database_name";
$db['default']['dbdriver'] = "mysql";
$db['default']['dbprefix'] = "";
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = FALSE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = "";
$db['default']['char_set'] = "utf8";
$db['default']['dbcollat'] = "utf8_general_ci";
$db['default']['swap_pre'] = "";
$db['default']['autoinit'] = TRUE;
$db['default']['stricton'] = FALSE;

Secondary database (notice pconnect is set to false)

$db['otherdb']['hostname'] = "localhost";
$db['otherdb']['username'] = "root";
$db['otherdb']['password'] = "";
$db['otherdb']['database'] = "other_database_name";
$db['otherdb']['dbdriver'] = "mysql";
$db['otherdb']['dbprefix'] = "";
$db['otherdb']['pconnect'] = FALSE;
$db['otherdb']['db_debug'] = FALSE;
$db['otherdb']['cache_on'] = FALSE;
$db['otherdb']['cachedir'] = "";
$db['otherdb']['char_set'] = "utf8";
$db['otherdb']['dbcollat'] = "utf8_general_ci";
$db['otherdb']['swap_pre'] = "";
$db['otherdb']['autoinit'] = TRUE;
$db['otherdb']['stricton'] = FALSE;

Then you can use secondary databases as database objects while using master database as usual :

// use master dataabse
$users = $this->db->get('users');

// connect to secondary database
$otherdb = $this->load->database('otherdb', TRUE);
$stuff = $otherdb->get('struff');
$otherdb->insert_batch('users', $users->result_array());

// keep using master database as usual, for example insert stuff from other database
$this->db->insert_batch('stuff', $stuff->result_array());

Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to database

Implement specialization in ER diagram phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' cannot be loaded Room - Schema export directory is not provided to the annotation processor so we cannot export the schema SQL Query Where Date = Today Minus 7 Days MySQL Error: : 'Access denied for user 'root'@'localhost' SQL Server date format yyyymmdd How to create a foreign key in phpmyadmin WooCommerce: Finding the products in database TypeError: tuple indices must be integers, not str

Examples related to codeigniter

DataTables: Cannot read property 'length' of undefined How to set time zone in codeigniter? php codeigniter count rows CodeIgniter: 404 Page Not Found on Live Server Only variable references should be returned by reference - Codeigniter How to pass a parameter like title, summary and image in a Facebook sharer URL How to JOIN three tables in Codeigniter how to get the base url in javascript How to get id from URL in codeigniter? How to disable PHP Error reporting in CodeIgniter?