Question 40118 in PHP Question posted in
Official manual can be found here.

How to connect multiple database in php project

I am trying to connect multiple database in my project. Since i am using single database and it’s working properly, now I would like to connect multiple 2 or 3 databse in my exisiting project, which will helpful for me. See my existing project code below for better understanding:

include_once(dirname(__FILE__) . '/config.php');

Class Database{
 public $host   = DB_HOST;
 public $user   = DB_USER;
 public $pass   = DB_PASS;
 public $dbname = DB_NAME;
 
 
 public $link;
 public $error;
 
 public function __construct(){
  $this->connectDB();
 }
 
private function connectDB(){
 $this->link = new mysqli($this->host, $this->user, $this->pass, 
  $this->dbname);
 if(!$this->link){
   $this->error ="Connection fail".$this->link->connect_error;
  return false;
 }
 }
}

Here is config.php file, where i will provide my multiple database connection credentials. Hence now here is single information given as I am using.

define("DB_HOST", "localhost");
define("DB_USER", "user");
define("DB_PASS", "pass");
define("DB_NAME", "db");

I have another file name called main.php where all the query operations functions are written. Here is the sample code:

include_once(dirname(__FILE__) . '/database.php');

    class Model
    {
        private $db;
    
        public function __construct()
        {
            
            $this->db = new Database();
        }
        public function employee_list()
        {
            $query = "SELECT * FROM emp_list ORDER BY emp_id DESC";
    
            return mysqli_query($this->db->link,$query);
        }
    }

Now I want to add multiple database with this existing project, so can you tell me whats need to update in this code. Thank you

Tags:

2

Answers


  1. 0

    Since you’ve already initialized the data members in your Database class with default values, you can make your constructor optionally accept the connection parameters, for example:

    class Database
{
    public $host   = DB_HOST;
    public $user   = DB_USER;
    public $pass   = DB_PASS;
    public $dbname = DB_NAME;


    public $link;
    public $error;

    public function __construct(
        $host = NULL,
        $user = NULL,
        $pass = NULL,
        $dbname = NULL
    )
    {
        $this->host = $host ?? $this->host;
        $this->user = $user ?? $this->user;
        $this->pass = $pass ?? $this->pass;
        $this->dbname = $dbname ?? $this->dbname;
        
        $this->connectDB();
    }

    private function connectDB()
    {
        $this->link = new mysqli(
            $this->host,
            $this->user,
            $this->pass,
            $this->dbname
        );
        if (!$this->link) {
            $this->error = "Connection fail" . $this->link->connect_error;
            return false;
        }
    }
}

    This way, only changed values will be updated, so if you have two databases on the same host you can simply pass the $dbname parameter and leave the others unchanged.

    $connection_1 = new Database();
$connection_2 = new Database(dbname: "my_other_database");

    Another solution would be to leave your original code as is without changing it, but to include a different configuration file each time, it would still work fine.

    Login or Signup to reply.
  2. 0

    If you were to store all the database connection details in a single file ( as JSON ) you could do something like this.

    <?php
    /*
        connection details for each database
        in JSON format. This could be saved
        as, for instance, db-credentials.json 
        -------------------------------------
        
        {
            "db1":{
                "dbuser":"dbo-764931",
                "dbhost":"localhost",
                "dbpwd":"xxx",
                "dbname":"blackrock"
            },
            "db2":{
                "dbuser":"dbo-984633",
                "dbhost":"localhost",
                "dbpwd":"xxx-yyy",
                "dbname":"area51"
            }
        }
        
    */
    
    
    
    
    class Database{
        private static $instance=false;
        private $db;
        
        private function __construct( $dbhost, $dbuser, $dbpwd, $dbname ){
            mysqli_report( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
            $this->db=new mysqli( $dbhost, $dbuser, $dbpwd, $dbname );
        }
        public static function initialise( $dbhost, $dbuser, $dbpwd, $dbname ){
            if( !self::$instance ) self::$instance=new self( $dbhost, $dbuser, $dbpwd, $dbname );
            return self::$instance;
        }
    }
    
    class Credentials{#class to read the db connection details for given $db
        private $json;
        private $db;
        
        public function __construct( $file, $db ){
            $this->json=file_exists( $file ) ? json_decode( file_get_contents( $file ) ) : false;
            $this->db=$db;
        }
        public function fetch(){
            return is_object( $this->json ) && property_exists( $this->json,$this->db ) ? $this->json->{$this->db} : false;
        }
    }
    
    class Model{
        private $db;
        private $config;
        
        public function __construct( $config,$db ){
            $credentials=new Credentials( $config,$db );
            $obj=$credentials->fetch();
            $this->db=Database::initialise( $obj->dbhost, $obj->dbuser, $obj->dbpwd, $obj->dbname );
        }
        public function employee_list(){
            $sql='SELECT * FROM emp_list ORDER BY emp_id DESC';
            return $this->db->query( $sql )->fetch_all( MYSQLI_ASSOC );
        }
    }








    # only to show result
    function debug($obj){
        printf('<pre>%s</pre>',print_r($obj,true));
    }








    $config = __DIR__ . '/db-credentials.json';

    $model_1=new Model( $config, 'db1' );
    $model_2=new Model( $config, 'db2' );
    
    
    debug( $model_1 );
    debug( $model_2 );
?>

    Testing the above yields:

    Model Object
(
    [db:Model:private] => Database Object
        (
            [db:Database:private] => mysqli Object
                (
                    [affected_rows] => 0
                    [client_info] => mysqlnd 5.0.12-dev - 20150407 - $Id: 38fea24f2847fa7519001be390c98ae0acafe387 $
                    [client_version] => 50012
                    [connect_errno] => 0
                    [connect_error] => 
                    [errno] => 0
                    [error] => 
                    [error_list] => Array
                        (
                        )

                    [field_count] => 0
                    [host_info] => localhost via TCP/IP
                    [info] => 
                    [insert_id] => 0
                    [server_info] => 5.5.8
                    [server_version] => 50508
                    [stat] => Uptime: 2210764  Threads: 3  Questions: 702637  Slow queries: 11  Opens: 12000  Flush tables: 1  Open tables: 256  Queries per second avg: 0.317
                    [sqlstate] => 00000
                    [protocol_version] => 10
                    [thread_id] => 52276
                    [warning_count] => 0
                )

        )

    [config:Model:private] => 
)

    … output for $model_2 omitted.

    With an approach like that you can edit the connection details without needing to modify the classes later and can use any number of different databases if required.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search