如何连接php项目中的多个数据库

Question

我正在尝试连接我的项目中的多个数据库。由于我使用单一数据库，它的工作正常，现在我想连接多个2或3个数据库在我现有的项目，这将有助于我。为了更好地理解，请参阅下面我现有的项目代码:

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;
}
}
}

这里是config.php文件，我将提供我的多个数据库连接凭据。因此，现在这里是我使用的单个信息。

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

我有另一个名为main.php的文件名，其中编写了所有查询操作函数。下面是示例代码:

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);
}
}

现在我想用这个现有的项目添加多个数据库，所以你能告诉我在这个代码中需要更新什么吗?谢谢你

Answer 1

由于您已经用默认值初始化了Database类中的数据成员，因此您可以让构造函数选择性地接受连接参数，例如:

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;
}
}
}

这样，只有更改的值才会更新，所以如果在同一台主机上有两个数据库，您可以简单地传递$dbname参数，而保持其他数据库不变。

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

另一个解决方案是保持原始代码不变而不进行更改，但每次都包含不同的配置文件，它仍然可以正常工作。

Answer 2

如果您要存储所有将数据库连接细节保存在单个文件中(作为JSON)，您可以这样做。

<?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 );
?>

测试上述结果:

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] => 
)

…$model_2输出略。

使用这样的方法，您可以编辑连接详细信息，而无需在以后修改类，并且可以根据需要使用任意数量的不同数据库。