Codeigniter connect to second DB based on first DB

Question

I have an autoloaded DB which has all its var set in the config/database.php file as a default group:

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

In this default DB there is a table where I need to read an external DB, and I have all fields to get a new connection.

I need to connect both DB at the same time, but I cannot define the DB variables in the config/database.php as these are dynamic and may change depending on the DB (default) content.

My idea was this either to SET $db['external'] = [...] IN THE CONTROLLER and set the data from the default DB I read, or simply use a DNS:

Solution #1:

public function wordpress()
{
    $DB = $this->load->database('default', true);
    $wp_db = $DB->get_where('dbtable', ['type_needed' => 'wordpress'])->row();

    $db['wp_db'] = array(
        'dsn'          => '',
        'hostname'     => $wp_db->mysql_host,
        'username'     => $wp_db->mysql_user,
        'password'     => $wp_db->mysql_password,
        'database'     => $wp_db->mysql_db,
        '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,
    );

    $this->WPDB = $this->load->database('wp_db', true);
}

Solution #1 gives "You have specified an invalid database connection group (wp_db) in your config/database.php file." error

Solution #2:

$DB = $this->load->database('default', true);
$wp_db = $DB->get_where('dbtable', ['type_needed' => 'wordpress'])->row();

$wp_dns = "mysql://$wp_db->mysql_user:$wp_db->mysql_password@$wp_db->mysql_host/$wp_db->mysql_db";
$this->WPDB = $this->load->database($wp_dns, true);

Solution #2 gives a "Invalid DB Connection String" error

Ps: I'm moving to Laravel, but this project was built with CI already :)

This answer might help you https://stackoverflow.com/a/18557976/2310830 — RiggsFolly, Jun 22 '18 at 12:12
Note : You don’t need to create separate database configurations if you only need to use a different database on the same connection. You can switch to a different database when you need to by using : `db_select` — Pradeep, Jun 22 '18 at 12:12

score 1 · Accepted Answer · answered Jun 25 '18 at 08:30

the only thing you've to change in your function is the following

public function wordpress()
{
    $DB = $this->load->database('default', true);
    $wp_db = $DB->get_where('dbtable', ['type_needed' => 'wordpress'])->row();

    $arrDbData = array(
        'dsn'          => '',
        'hostname'     => $wp_db->mysql_host,
        'username'     => $wp_db->mysql_user,
        'password'     => $wp_db->mysql_password,
        'database'     => $wp_db->mysql_db,
        '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,
    );

    $this->WPDB = $this->load->database($arrDbData, true);
}

score 0 · Answer 2 · answered Jun 22 '18 at 12:16

0

I found out Solution #2 is working but due to the dns connection which is a STRING you are to make sure the password is made of letters and numbers and NO SYMBOLS otherwise it screws up the string and does not read properly.

In my case the password was this one iidf#q0RDTh#)CrPo5PDLeVe so dashes and parenthesis created a problem where CI could not read the whole password.

answered Jun 22 '18 at 12:16

Mr.Web

6,992
8
51
86

if you pass your params as `array` you shouldn't have any troubles at all - as described under https://www.codeigniter.com/user_guide/database/connecting.html?highlight=database#manually-connecting-to-a-database – Atural Jun 25 '18 at 06:11
@sintakonte. Ok, but how do you do it without setting $db array? Can you show me an example? – Mr.Web Jun 25 '18 at 06:19
what do you mean with _without setting $db array_ ? In your function `wordpress()` you simply can use `$this->WPDB = $this->load->database($db['wp_db'], true);` and thats it. – Atural Jun 25 '18 at 06:23
@sintakonte ok but my question clearly states the data ARE NOT taken from the database.php file but from the DB table itself, so this is not possible with the code you wrote. – Mr.Web Jun 25 '18 at 08:03
ofc it's possible - i'll post an answer – Atural Jun 25 '18 at 08:29

Codeigniter connect to second DB based on first DB

2 Answers2