php - 在运行时在 Codeigniter 中更改数据库连接

Question

我想连接到基于子域的不同数据库，目前我有这段代码，但它看起来不是解决问题的好方法。请指导正确的方向，在代码点火器中实现这一目标的最佳方法是什么。

class DBConnection extends CI_Model
{
    public function __construct()
    {
        parent::__construct();
    }

    public function dbConfig(){

        $efg="";
        $data = explode('.',$_SERVER['SERVER_NAME']);
        if (!empty($data[0])) {
            $efg = $data[0];
        }

        $sql="SELECT DbUsername,DbName,DbPassword FROM abc WHERE efg=?";
        $d_result=$this->db->query($sql,array($efg))->result_array();

        $this->db->close();

        $config['hostname'] = "localhost";
        $config['username'] = $d_result[0]["DbUsername"];
        $config['password'] = $d_result[0]["DbPassword"];
        $config['database'] = $d_result[0]["DbName"];
        $config['dbdriver'] = "mysql";
        $config['dbprefix'] = "";
        $config['pconnect'] = FALSE;
        $config['db_debug'] = TRUE;
        $config['cache_on'] = FALSE;
        $config['cachedir'] = "";
        $config['char_set'] = "utf8";
        $config['dbcollat'] = "utf8_general_ci";
        return $config;
    }
} 

然后在用户模型类中，我有类似这样的东西，它在每个请求上再次执行上述所有处理。我希望切换将在开始时进行，进一步的查询将推送到选定的数据库。

class User extends CI_Model
{
    private $clientDB;

    public function __construct()
    {
        parent::__construct();
        $db=new DBConnection();
        $this->clientDB=$this->load->database($db->dbConfig(),TRUE);
    }

    public function isUserExists($username,$password)
    {
        $sql="SELECT uid FROM `aaa` WHERE `uname`=? AND `upwd`=?";
        $d_result=$this->clientDB->query($sql,array($username,$password));
        return $d_result;
    }
} 

Answer 1

我个人会像这样在 /application/config/database.php 文件中运行 PDO 查询:

方法#1

<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');
/*
| -------------------------------------------------------------------
| DATABASE CONNECTIVITY SETTINGS
| -------------------------------------------------------------------
| This file will contain the settings needed to access your database.
|
| For complete instructions please consult the 'Database Connection'
| page of the User Guide.
|
| -------------------------------------------------------------------
| EXPLANATION OF VARIABLES
| -------------------------------------------------------------------
|
|   ['hostname'] The hostname of your database server.
|   ['username'] The username used to connect to the database
|   ['password'] The password used to connect to the database
|   ['database'] The name of the database you want to connect to
|   ['dbdriver'] The database type. ie: mysql.  Currently supported:
                 mysql, mysqli, postgre, odbc, mssql, sqlite, oci8
|   ['dbprefix'] You can add an optional prefix, which will be added
|                to the table name when using the  Active Record class
|   ['pconnect'] TRUE/FALSE - Whether to use a persistent connection
|   ['db_debug'] TRUE/FALSE - Whether database errors should be displayed.
|   ['cache_on'] TRUE/FALSE - Enables/disables query caching
|   ['cachedir'] The path to the folder where cache files should be stored
|   ['char_set'] The character set used in communicating with the database
|   ['dbcollat'] The character collation used in communicating with the database
|                NOTE: For MySQL and MySQLi databases, this setting is only used
|                as a backup if your server is running PHP < 5.2.3 or MySQL < 5.0.7
|                (and in table creation queries made with DB Forge).
|                There is an incompatibility in PHP with mysql_real_escape_string() which
|                can make your site vulnerable to SQL injection if you are using a
|                multi-byte character set and are running versions lower than these.
|                Sites using Latin-1 or UTF-8 database character set and collation are unaffected.
|   ['swap_pre'] A default table prefix that should be swapped with the dbprefix
|   ['autoinit'] Whether or not to automatically initialize the database.
|   ['stricton'] TRUE/FALSE - forces 'Strict Mode' connections
|                           - good for ensuring strict SQL while developing
|
| The $active_group variable lets you choose which connection group to
| make active.  By default there is only one group (the 'default' group).
|
| The $active_record variables lets you determine whether or not to load
| the active record class
*/

$active_group = 'default';
$active_record = TRUE;

$efg="";
$data = explode('.',$_SERVER['SERVER_NAME']);
if (!empty($data[0])) {
    $efg = $data[0];
}

$dbh = new PDO(YourPDOConnDetails);

$sql = "SELECT DbUsername,DbName,DbPassword FROM abc WHERE efg=?";

$sth = $dbh->prepare($sql);
$sth->execute(array($efg));
$d_result= $sth->fetchAll(PDO::FETCH_ASSOC);

// We are done with PDO for this purpose so free up some resources!
$dbh = null;
unset($dbh);

$db['default']['hostname'] = 'localhost';
$db['default']['username'] = $d_result[0]["DbUsername"];
$db['default']['password'] = $d_result[0]["DbPassword"];
$db['default']['database'] = $d_result[0]["DbName"];
$db['default']['dbdriver'] = 'mysql';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = FALSE;
$db['default']['db_debug'] = TRUE;
$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;


/* End of file database.php */
/* Location: ./application/config/database.php */

方法#2

$efg="";
$data = explode('.',$_SERVER['SERVER_NAME']);
if (!empty($data[0])) {
    $efg = $data[0];
}

$active_group = $efg; // this will choose from subdomain1/subdomain2 settings below. Add as many as you need
$active_record = TRUE;

$db['subdomain1']['hostname'] = 'localhost';
$db['subdomain1']['username'] = 'subd1User';
$db['subdomain1']['password'] = 'subd1Pass';
$db['subdomain1']['database'] = 'subd1DBName';
$db['subdomain1']['dbdriver'] = 'mysql';
$db['subdomain1']['dbprefix'] = '';
$db['subdomain1']['pconnect'] = FALSE;
$db['subdomain1']['db_debug'] = TRUE;
$db['subdomain1']['cache_on'] = FALSE;
$db['subdomain1']['cachedir'] = '';
$db['subdomain1']['char_set'] = 'utf8';
$db['subdomain1']['dbcollat'] = 'utf8_general_ci';
$db['subdomain1']['swap_pre'] = '';
$db['subdomain1']['autoinit'] = TRUE;
$db['subdomain1']['stricton'] = FALSE;

$db['subdomain2']['hostname'] = 'localhost';
$db['subdomain2']['username'] = 'subd2User';
$db['subdomain2']['password'] = 'subd2Pass';
$db['subdomain2']['database'] = 'subd2DBName';
$db['subdomain2']['dbdriver'] = 'mysql';
$db['subdomain2']['dbprefix'] = '';
$db['subdomain2']['pconnect'] = FALSE;
$db['subdomain2']['db_debug'] = TRUE;
$db['subdomain2']['cache_on'] = FALSE;
$db['subdomain2']['cachedir'] = '';
$db['subdomain2']['char_set'] = 'utf8';
$db['subdomain2']['dbcollat'] = 'utf8_general_ci';
$db['subdomain2']['swap_pre'] = '';
$db['subdomain2']['autoinit'] = TRUE;
$db['subdomain2']['stricton'] = FALSE;