Using multiple MySQL databases with Codeigniter?

Question

I have multiple websites based on CI and they all use their own default DBs and work just fine.

Now I have a requirement where one of the websites connect to the DB of another website to get some info (they are on same server) but maintain the native DB connection.

I'm using following code to connect to other website DB

$dsn = 'mysql://econnect_base_user:econnect_base_pass@localhost/econnect_base_db';
$city_db = $this->load->database($dsn, TRUE);

the problem is this resets the default DB and closing the 'city_db' and trying to reconnect the default DB won't work (may be it's the dumbest thing to do but I don't have any other idea!)

Please help.

Thanks.

Check this link --- codeigniter.com/user_guide/database/connecting.html at "Connecting to Multiple Databases" — swapnesh
– swapnesh, Commented Jun 18, 2012 at 12:48

prodigitalson · Accepted Answer · 2012-06-18 12:46:17Z

2

From the documentation:

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.

When you connect this way, you will use your object name to issue commands rather than the syntax used throughout this guide. In other words, rather than issuing commands with:
$this->db->query(); 
$this->db->result(); 
//etc...
You will instead use:
$DB1->query(); 
$DB1->result();

answered Jun 18, 2012 at 12:46

prodigitalson

60.6k10 gold badges106 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Anupam Over a year ago

yeah well I don't think I can do that, I know and am using the same on some of the websites. but here the DB details in the DSN string comes from a database and not from database.php and it's based on the request which can come from any of the multiple websites and I kind of require an quick connection to other DB, grab the data and done!

prodigitalson Over a year ago

So instead jsut supply the DSN to one of them: $remote = $this->load->database($dsn, true); $local = $this->load->database('default');

Anupam Over a year ago

yes I tried that but it doesn't seem to work! I'll try again.

Themroc · Accepted Answer · 2012-06-18 12:56:12Z

0

Just use plain php db stuff:

$other_dbh = new PDO("mysql:host=localhost;dbname=$other_db_name", $other_db_user, $other_db_pass);
foreach ($other_dbh->query('SELECT * from FOO') as $row)
    process_info($row);

answered Jun 18, 2012 at 12:56

Themroc

4952 silver badges5 bronze badges

1 Comment

NDBoost Over a year ago

why would you use plain php when CI has a wrapper for this already?

prab · Accepted Answer · 2017-05-19 09:39:41Z

At First setup two database connections in config/database.php. By default you will see the following one.

$active_group = 'default';
    $active_record = TRUE;
    $db['default']['hostname'] = '';
    $db['default']['username'] = '';
    $db['default']['password'] = '';
    $db['default']['database'] = '';
    $db['default']['dbdriver'] = 'mysql';
    $db['default']['dbprefix'] = '';
    $db['default']['pconnect'] = TRUE;
    $db['default']['db_debug'] = TRUE;
    $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;

Add another db connection settings as $db['another_db'] as following

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

Then for the default database, you just do the normal query after loading the database.

$this->load->database();
$this->db->query($sql); 
$this->db->result();

For loading another_db and making query,

$another_db= $this->load->database('another_db', TRUE);
$another_db->query($sql); 
$another_db->result();

score 0 · Accepted Answer · 2017-05-19 12:50:04Z

0

Include this code in your config/database.php Hope this work for you.

$active_group = 'default';
$active_record = TRUE;

// first db configuration
$db['default']['hostname'] = 'localhost'; // Hostname
$db['default']['username'] = 'root'; // username
$db['default']['password'] = ''; // password
$db['default']['database'] = 'first_db'; // replace your database name
$db['default']['dbdriver'] = 'mysqli';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$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;


// second db configuration
$db['db2']['hostname'] = 'localhost'; // Hostname
$db['db2']['username'] = 'root'; // username
$db['db2']['password'] = ''; // password
$db['db2']['database'] = 'second_db'; // replace your database name
$db['db2']['dbdriver'] = 'mysqli';
$db['db2']['dbprefix'] = '';
$db['db2']['pconnect'] = FALSE;
$db['db2']['db_debug'] = TRUE;
$db['db2']['cache_on'] = FALSE;
$db['db2']['cachedir'] = '';
$db['db2']['char_set'] = 'utf8';
$db['db2']['dbcollat'] = 'utf8_general_ci';
$db['db2']['swap_pre'] = '';
$db['db2']['autoinit'] = TRUE;
$db['db2']['stricton'] = FALSE;

You can use both database anywhere by loading database

$this->load->database();
$this->db->database;
$this->db->database2;
$this->db->query('your query');

edited May 19, 2017 at 12:50

answered May 19, 2017 at 9:49

user7467483

2 Comments

Tom Over a year ago

While the code you've provided may include the answer to the OP's problem - the post is not very useful without an explanation of how you've solved their issue.

Tom Over a year ago

Well lets close down Stack Overflow now then.

Collectives™ on Stack Overflow

Using multiple MySQL databases with Codeigniter?

4 Answers 4

3 Comments

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related