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 stackoverflow.com/a/18557976/2310830 — RiggsFolly
– RiggsFolly, Commented Jun 22, 2018 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
– Pradeep, Commented Jun 22, 2018 at 12:12

Atural · Accepted Answer · 2018-06-25 08:30:12Z

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

Mr.Web · Accepted Answer · 2018-06-22 12:16:49Z

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, 2018 at 12:16

Mr.Web

7,2078 gold badges56 silver badges90 bronze badges

5 Comments

Atural Over a year ago

if you pass your params as array you shouldn't have any troubles at all - as described under codeigniter.com/user_guide/database/…

Mr.Web Over a year ago

@sintakonte. Ok, but how do you do it without setting $db array? Can you show me an example?

Atural Over a year ago

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.

Mr.Web Over a year ago

@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.

Atural Over a year ago

ofc it's possible - i'll post an answer

Collectives™ on Stack Overflow

Codeigniter connect to second DB based on first DB

2 Answers 2

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related