I have done master and slave setup for my database server, so In my codigniter application what I want is to execute all write options on master server and all read operation on slave server.
Can anybody tell me how can I achieve this in codigniter version3.
Thanks.
What you have to update system/database/DB_Driver.php file.
Its very simple what you have to make 3 small changes in this file.
1) Inside Construction add your Read and Write server credentials.
$this->server_details['local_server']['hostname'] = "localhost";
$this->server_details['local_server']['username'] = "select_user";
$this->server_details['local_server']['password'] = "password";
$this->server_details['live_server']['hostname'] = "192.192.223.22";
$this->server_details['live_server']['username'] = "write_user";
$this->server_details['live_server']['password'] = "password";
2) Create New function will switch the database connection for select and write query.
private function ebrandz_switch_db_for_read_write($sql) {
if( $this->hostname == $this->server_details['local_server']['hostname'] && $this->username == $this->server_details['local_server']['username'] && $this->password == $this->server_details['local_server']['password'] ) {
//echo $sql.'<br/>';
if(stristr($sql, 'SELECT')) {
foreach($this->server_details['local_server'] as $index_key => $index_value ) {
$this->$index_key = $index_value;
}
$this->conn_id = null; //unset resource link
$this->initialize(); //Reinitialize connnection with new parameters
} else {
//die('write operation is not allowed.');
foreach($this->server_details['live_server'] as $index_key => $index_value ) {
$this->$index_key = $index_value;
}
$this->conn_id = null ; //unset resource link
$this->initialize(); //Reinitialize connnection with new parameters
}
} else if( $this->hostname == $this->server_details['live_server']['hostname'] && $this->username == $this->server_details['live_server']['username'] && $this->password == $this->server_details['live_server']['password'] ) {
if(stristr($sql, 'SELECT')) {
foreach($this->server_details['local_server'] as $index_key => $index_value ) {
$this->$index_key = $index_value;
}
$this->conn_id = null ; //unset resource link
$this->initialize(); //Reinitialize connnection with new parameters
} else {
//die('write operation is not allowed.');
foreach($this->server_details['live_server'] as $index_key => $index_value ) {
$this->$index_key = $index_value;
}
$this->conn_id = null ; //unset resource link
$this->initialize(); //Reinitialize connnection with new parameters
}
}
//Code to re initialize the connection
}
3) Inside Query function of this file you have to call the prevous defined function.
// Verify table prefix and replace if necessary
if ($this->dbprefix !== '' && $this->swap_pre !== '' && $this->dbprefix !== $this->swap_pre)
{
$sql = preg_replace('/(\W)'.$this->swap_pre.'(\S+?)/', '\\1'.$this->dbprefix.'\\2', $sql);
}
/**
* @author Anant Waykar
* if query is read only then load some other database
*/
$this->ebrandz_switch_db_for_read_write($sql);
//Code to re initialize the connection
You should provide the second database information in `application/config/database.php´
Normally, you would set the default database group, like so:
$db['default']['hostname'] = "localhost";
$db['default']['username'] = "root";
$db['default']['password'] = "";
$db['default']['database'] = "database_name";
$db['default']['dbdriver'] = "mysql";
$db['default']['dbprefix'] = "";
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = FALSE;
$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;
Notice that the login information and settings are provided in the array named $db['default'].
You can then add another database in a new array - let's call it 'otherdb'.
$db['otherdb']['hostname'] = "localhost";
$db['otherdb']['username'] = "root";
$db['otherdb']['password'] = "";
$db['otherdb']['database'] = "other_database_name";
$db['otherdb']['dbdriver'] = "mysql";
$db['otherdb']['dbprefix'] = "";
$db['otherdb']['pconnect'] = TRUE;
$db['otherdb']['db_debug'] = FALSE;
$db['otherdb']['cache_on'] = FALSE;
$db['otherdb']['cachedir'] = "";
$db['otherdb']['char_set'] = "utf8";
$db['otherdb']['dbcollat'] = "utf8_general_ci";
$db['otherdb']['swap_pre'] = "";
$db['otherdb']['autoinit'] = TRUE;
$db['otherdb']['stricton'] = FALSE;
If you need to connect to more than one database simultaneously you can do so as follows:
$readDB = $this->load->database('otherdb', TRUE);
//For read:
$query = $readDB->select('first_name, last_name')->get('person');
var_dump($query);
//For write:
$this->db->insert('tablename', $insert_array);
