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

Question

我希望能够根据子域名连接到不同的数据库。目前我有以下代码，但它并不是解决问题的好方法。请指导我在CodeIgniter中实现这一点的最佳方法。

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

- Desire

---

你的意思是，你希望在每个页面请求中发生切换，这样你就不必在每个模型中定义了吗？ - prabeen giri

某些其他地方指的是当用户首次访问页面时，例如子域名.dummysite.com，然后它将选择所需的子域数据库。 - Desire

你的代码看起来还不错，如果是我，我不会切换连接，而是会建立两个连接，并通过在MY_Controller中定义并将其设置为公共，确保第二个连接对所有模型都可用。 - ahmad

@ahmad，有没有办法可以解决这个问题？连接只会在开始时与客户端的数据库建立一次，而不是在每次调用某个模型类时都建立连接。 - Desire

你可以尝试将它作为post_controller_constructor钩子添加，检查已加载的模型，然后使其对它们可用。 - ahmad

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 */

方法二

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

- MonkeyZeus

---

我在寻找最佳实践！你的方法看起来很棒！ - Desire

我很高兴你喜欢它！如果出现任何问题，请告诉我。 - MonkeyZeus

谢谢，它正在工作！您是否建议采用其他方法使其更好、更安全和更高效？ - Desire

我不确定你所说的“更安全”是什么意思。你想要保护什么？ - MonkeyZeus

如果您想使其更有效率，那么您可以保持我所概述的方法，但通过硬编码子域的不同设置来排除PDO()调用。 - MonkeyZeus