CodeIgniter Forums
Connect to multiple database server by type of query (delete, insert, select, update) - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: Best Practices (https://forum.codeigniter.com/forumdisplay.php?fid=12)
+--- Thread: Connect to multiple database server by type of query (delete, insert, select, update) (/showthread.php?tid=62360)

Connect to multiple database server by type of query (delete, insert, select, update) - chandara - 07-05-2015

I would like to ask one question with database connection in CodeIgniter 3.

I check in database configuration, it provide setting database connection values for specific environments by placing database.php such as:

PHP Code:
$db['default'] = array(
        'dsn'   => '',
        'hostname' => 'localhost',
        'username' => 'root',
        'password' => '',
        'database' => 'database_name',
        'dbdriver' => 'mysqli',
        'dbprefix' => '',
        'pconnect' => TRUE,
        'db_debug' => TRUE,
        'cache_on' => FALSE,
        'cachedir' => '',
        'char_set' => 'utf8',
        'dbcollat' => 'utf8_general_ci',
        'swap_pre' => '',
        'autoinit' => TRUE,
        'encrypt' => FALSE,
        'compress' => FALSE,
        'stricton' => FALSE,
        'failover' => array()
); 

But my requirement is separate query select, insert, delete and update to connect to server, for example, select.server.com, insert.server.com, delete.server.com and update.server.com respectively.

My question: how can I configure database connection depend one query type (insert, delete, update, select) in CodeIgniter 3?

Thanks.

RE: Connect to multiple database server by type of query (delete, insert, select, update) - kilishan - 07-06-2015

The easiest way to do this is to create a MY_Model that handles this for you by creating 4 separate database connections, and then has basic CRUD methods that you use that use the correct individual connections. Something like the following (though this might not be the best performing example):

Code:
class MY_Model extends CI_Model {

    protected $db_select;
    protected $db_insert;
    protected $db_update;
    protected $db_delete;

    public function __construct()
    {
        $this->db_select = $this->load->database('select', true);
        $this->db_insert = $this->load->database('insert', true);
        $this->db_update = $this->load->database('update', true);
        $this->db_delete = $this->load->database('delete', true);
    }

    //--------------------------------------------------------------------

    public function create($data)
    {
        return $this->db_insert->insert($data);
    }
    
    //--------------------------------------------------------------------
    
    // an so on...

}

Then in your database config file, you would create multiple groups with the appropriate names and connection details:

Code:
$db['insert'] = array(
        'dsn'   => '',
        'hostname' => 'localhost',
        'username' => 'root',
        'password' => '',
        'database' => 'database_name',
        'dbdriver' => 'mysqli',
        'dbprefix' => '',
        'pconnect' => TRUE,
        'db_debug' => TRUE,
        'cache_on' => FALSE,
        'cachedir' => '',
        'char_set' => 'utf8',
        'dbcollat' => 'utf8_general_ci',
        'swap_pre' => '',
        'autoinit' => TRUE,
        'encrypt' => FALSE,
        'compress' => FALSE,
        'stricton' => FALSE,
        'failover' => array()
);
$db['delete'] = array(
        'dsn'   => '',
        'hostname' => 'localhost',
        'username' => 'root',
        'password' => '',
        'database' => 'database_name',
        'dbdriver' => 'mysqli',
        'dbprefix' => '',
        'pconnect' => TRUE,
        'db_debug' => TRUE,
        'cache_on' => FALSE,
        'cachedir' => '',
        'char_set' => 'utf8',
        'dbcollat' => 'utf8_general_ci',
        'swap_pre' => '',
        'autoinit' => TRUE,
        'encrypt' => FALSE,
        'compress' => FALSE,
        'stricton' => FALSE,
        'failover' => array()
);


RE: Connect to multiple database server by type of query (delete, insert, select, update) - CroNiX - 07-06-2015

I'd do it slightly different than Kilishan, in only that I wouldn't load all 4 connections in the constructor. I'd load them individually in the respective methods that they are used since each request will only need one connection.

RE: Connect to multiple database server by type of query (delete, insert, select, update) - kilishan - 07-06-2015

(07-06-2015, 10:46 AM)CroNiX Wrote: I'd do it slightly different than Kilishan, in only that I wouldn't load all 4 connections in the constructor. I'd load them individually in the respective methods that they are used since each request will only need one connection.

Very true! I did mention that my example wasn't the best performance Smile It was intended as a concept, but you're absolutely correct that's the best way to handle it.