mysql - PHP Mysqli 'dynamically' saying what connection (variable) should be used -

what best way use multiple (dynamic) php mysqli connections?

currently have single mysql server. data on server in few databases. in not distant future storage needs larger server can handle. have place databases on 1 server , on another. , in further future more servers come play.

in effort of 'future proving' scripts thought class point right server might best solution. send 'key' of data want , says connection should using because stored on server. instance if want logs send key 'logs' , class says need use predefined connection 'connection_a'.

the class looks like:

class databaseconnection {     public $connection_name;      function __construct($key){         if($key=='logs'){             $connection_name = 'connection_a';         }         elseif($key=='userdetails'){             $connection_name = 'connection_b';         }     } } 

so is:

$connection_a = new mysqli($dbhosta, $dbusera, $dbpassa, $dbnamea); $connection_b = new mysqli($dbhostb, $dbuserb, $dbpassb, $dbnameb);  $databaseconnection = new databaseconnection('logs'); $sql = "select * `something` `timestamp`>='12378912798'"; $result = ${$databaseconnection->connection_name}->query($sql); 

this works fine. although isn't 'dynamic' wish was. run problems when functions concerned. required me do:

function something(){     global $connection_a, $connection_b;      $databaseconnection = new databaseconnection('logs');     $sql = "select * `something` `timestamp`>='12378912798'";     $result = ${$databaseconnection->connection_name}->query($sql); } 

or

function something(){     $databaseconnection = new databaseconnection('logs');     global ${$databaseconnection->connection};      $sql = "select * `something` `timestamp`>='12378912798'";     $result = ${$databaseconnection->connection_name}->query($sql); } 

neither elegant , first 1 quite work if add more connections or shuffle things around.

so thought doing this:

class databaseconnection {     public $connection_name;      function __construct($key){         if($key=='logs'){             $connection_name = 'connection_a';         }         elseif($key=='userdetails'){             $connection_name = 'connection_b';         }     }      function connection(){         $this->{$this->connection_name}();     }      function connection_a(){         $connection_a = new mysqli($dbhosta, $dbusera, $dbpassa, $dbnamea);         return $connection_a;     }      function connection_b(){         $connection_b = new mysqli($dbhostb, $dbuserb, $dbpassb, $dbnameb);         return $connection_b;     } } 

and using like:

$databaseconnection = new databaseconnection('logs'); $sql = "select * `something` `timestamp`>='12378912798'"; $result = $databaseconnection->connection->query($sql); 

but there functions in scripts run few hundred times each time script runs. (inserts , stuff). wouldn't open 300 connections or that?

my knowledge of mysqli limited consequences of approach. read somewhere mysqli pools connections if scripts runs function first time uses set of credentials login server_a, , tries again in same script few moments later, doesn't creates new connection uses same one. right or mixing things up?

what think best way address need of 'dynamically' saying server should contacted sort of data? or have idea?

you use tools http://php.net/manual/en/book.mysqlnd-ms.php

that allow masters , read slaves. other option @ amazon rds other answer suggests.