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?

回答1:

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

回答2:

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());

回答3:

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);

回答4:

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.

回答5:

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(); 
        }

    }
?>

回答6:

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.