How to Connect to Multiple Databases in CodeIgniter

Installing CodeIgniter

If you don't have CodeIgniter installed yet, you can use this link to download the latest version of CodeIgniter which is 3.1.7 that I've used in this source code. After downloading, extract the file in the folder of your server. Since I'm using XAMPP as my localhost server, I've put the folder in htdocs folder of my XAMPP. Then, you can test whether you have successfully installed codeigniter by typing your app name in your browser. In my case, I named my app as codeigniter_multiple_db so I'm using the below code.
  1. localhost/codeigniter_multiple_db
codeigniter successful install

Creating our Database

Next, we're going to create the databases that we are going to use in this tutorial. I've included in the downloadable of this tutorial the databases. All you need to do is import them in your phpMyAdmin. If you have no idea on how to import, please refer to my tutorial How import .sql file to restore MySQL database. After successful import, we should have two databases named mydatabase and mysecond.

Configuring our Database Connection

Next, we're going to connect our codeigniter application to the databases that we created earlier. 1. In your codeigniter app folder, open database.php located in application/config folder. 2. Create our databases by creating the ff configurations: For default db
  1. $db['default'] = array(
  2. 'dsn' => '',
  3. 'hostname' => 'localhost',
  4. 'username' => 'root',
  5. 'password' => '',
  6. 'database' => 'mydatabase',
  7. 'dbdriver' => 'mysqli',
  8. 'dbprefix' => '',
  9. 'pconnect' => TRUE,
  10. 'db_debug' => (ENVIRONMENT !== 'production'),
  11. 'cache_on' => FALSE,
  12. 'cachedir' => '',
  13. 'char_set' => 'utf8',
  14. 'dbcollat' => 'utf8_general_ci',
  15. 'swap_pre' => '',
  16. 'encrypt' => FALSE,
  17. 'compress' => FALSE,
  18. 'stricton' => FALSE,
  19. 'failover' => array(),
  20. 'save_queries' => TRUE
  21. );
For second db
  1. $db['second'] = array(
  2. 'dsn' => '',
  3. 'hostname' => 'localhost',
  4. 'username' => 'root',
  5. 'password' => '',
  6. 'database' => 'mysecond',
  7. 'dbdriver' => 'mysqli',
  8. 'dbprefix' => '',
  9. 'pconnect' => TRUE,
  10. 'db_debug' => (ENVIRONMENT !== 'production'),
  11. 'cache_on' => FALSE,
  12. 'cachedir' => '',
  13. 'char_set' => 'utf8',
  14. 'dbcollat' => 'utf8_general_ci',
  15. 'swap_pre' => '',
  16. 'encrypt' => FALSE,
  17. 'compress' => FALSE,
  18. 'stricton' => FALSE,
  19. 'failover' => array(),
  20. 'save_queries' => TRUE
  21. );

Configuring our Base URL

Next, we configure our base url to tell codeigniter that this is the URL of our site/application. We're gonna be using this a lot that's why we need to configure this. 1. In your codeigniter app folder, open config.php located in application/config folder. 2. Find and edit the ff line:
  1. $config['base_url'] = 'http://localhost/codeigniter_multiple_db';
where codeigniter_multiple_db is the name of your app folder.

Creating our Models

Next, we create the models for our databases. Create the ff files in application/models folder of our app. Members_model.php This is from our default db.
  1. <?php
  2. class Members_model extends CI_Model {
  3. function __construct(){
  4. parent::__construct();
  5. $this->load->database();
  6. }
  7.  
  8. public function getUsers(){
  9. $query = $this->db->get('members');
  10. return $query->result();
  11. }
  12.  
  13. }
  14. ?>
Seconddb_model.php This is from our second db
  1. <?php
  2. class Seconddb_model extends CI_Model {
  3. function __construct(){
  4. parent::__construct();
  5. //load our second db and put in $db2
  6. $this->db2 = $this->load->database('second', TRUE);
  7. }
  8.  
  9. public function getsecondUsers(){
  10. $query = $this->db2->get('members');
  11. return $query->result();
  12. }
  13.  
  14. }
  15. ?>

Creating our Controller

Next, we create our controller. Create a file named Main.php in application/controllers folder of our app and put the ff codes.
  1. <?php
  2. defined('BASEPATH') OR exit('No direct script access allowed');
  3.  
  4. class Main extends CI_Controller {
  5. function __construct(){
  6. parent:: __construct();
  7. $this->load->helper('url');
  8. //load default database
  9. $this->load->model('members_model');
  10. //load second database
  11. $this->load->model('seconddb_model');
  12. }
  13.  
  14. public function index(){
  15. //default database data
  16. $data['firsts'] = $this->members_model->getUsers();
  17. //second database data
  18. $data['seconds'] = $this->seconddb_model->getsecondUsers();
  19. $this->load->view('show', $data);
  20. }
  21.  
  22. }

Cofiguring our Default Controller

Next, we are going to set our default controller so that whenever we haven't set up a controller to use, this default controller will be used instead. Open routes.php located in application/config folder and set the default route to our user controller. Note: While we name controllers using CAPITAL letter in this first letter, we refer to them in SMALL letter.
  1. $route['default_controller'] = 'main';

Creating our View

Lastly, we create the views of our app. Take note that I've use Bootstrap in the views. You may download bootstrap using this link. Create the ff files inside application/views folder. show.php
  1. <!DOCTYPE html>
  2. <html>
  3. <head>
  4. <meta charset="utf-8">
  5. <title>How to connect to multiple database in CodeIgniter</title>
  6. <link rel="stylesheet" type="text/css" href="<?php echo base_url(); ?>bootstrap/css/bootstrap.min.css">
  7. </head>
  8. <body>
  9. <div class="container">
  10. <h1 class="page-header text-center">Connecting to Multiple Databases</h1>
  11. <div class="row">
  12. <div class="col-sm-6 col-sm-offset-3">
  13. <h3>Default Database Data</h3>
  14. <table class="table table-bordered table-striped">
  15. <thead>
  16. <tr>
  17. <th>ID</th>
  18. <th>Firstname</th>
  19. <th>Lastname</th>
  20. <th>Address</th>
  21. </tr>
  22. </thead>
  23. <tbody>
  24. <?php
  25. //$data['firsts'] from our controller
  26. foreach($firsts as $first){
  27. ?>
  28. <tr>
  29. <td><?php echo $first->id; ?></td>
  30. <td><?php echo $first->firstname; ?></td>
  31. <td><?php echo $first->lastname; ?></td>
  32. <td><?php echo $first->address; ?></td>
  33. </tr>
  34. <?php
  35. }
  36. ?>
  37. </tbody>
  38. </table>
  39. </div>
  40. </div>
  41. <div class="row">
  42. <div class="col-sm-6 col-sm-offset-3">
  43. <h3>Second Database Data</h3>
  44. <table class="table table-bordered table-striped">
  45. <thead>
  46. <tr>
  47. <th>ID</th>
  48. <th>Firstname</th>
  49. <th>Lastname</th>
  50. <th>Address</th>
  51. </tr>
  52. </thead>
  53. <tbody>
  54. <?php
  55. //$data['seconds'] from our controller
  56. foreach($seconds as $second){
  57. ?>
  58. <tr>
  59. <td><?php echo $second->id; ?></td>
  60. <td><?php echo $second->firstname; ?></td>
  61. <td><?php echo $second->lastname; ?></td>
  62. <td><?php echo $second->address; ?></td>
  63. </tr>
  64. <?php
  65. }
  66. ?>
  67. </tbody>
  68. </table>
  69. </div>
  70. </div>
  71. </div>
  72. </body>
  73. </html>
That ends thsi tutorial. Happy coding :)

Comments

Add new comment