PHP动态网页多数据库连接_PHP动态网页多数据源连接切换详解

// 数据库连接信息
$db_config = [
    'db1' => [
        'host' => 'localhost',
        'user' => 'user1',
        'password' => 'password1',
        'database' => 'database1'
    ],
    'db2' => [
        'host' => 'localhost',
        'user' => 'user2',
        'password' => 'password2',
        'database' => 'database2'
    ]
];

// 创建数据库连接函数
function connect_db($db_name, $db_config) {
    try {
        $dsn = "mysql:host={$db_config[$db_name]['host']};dbname={$db_config[$db_name]['database']};charset=utf8mb4";
        $pdo = new PDO($dsn, $db_config[$db_name]['user'], $db_config[$db_name]['password']);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // 错误报告模式
        return $pdo;
    } catch (PDOException $e) {
        echo "Connection failed: " . $e->getMessage();
        return null;
    }
}

// 连接到两个数据库
$pdo1 = connect_db('db1', $db_config);
$pdo2 = connect_db('db2', $db_config);

// 从数据库1查询数据
if ($pdo1) {
    $stmt1 = $pdo1->query("SELECT * FROM table1");
    $results1 = $stmt1->fetchAll(PDO::FETCH_ASSOC);
    // 处理结果
    print_r($results1);
}

// 从数据库2查询数据
if ($pdo2) {
    $stmt2 = $pdo2->query("SELECT * FROM table2");
    $results2 = $stmt2->fetchAll(PDO::FETCH_ASSOC);
    // 处理结果
    print_r($results2);
}

// 关闭数据库连接 (可选，PHP会在脚本结束时自动关闭)
$pdo1 = null;
$pdo2 = null;

mysqli_*

class DatabaseManager {
    private static $connections = [];
    private static $config;

    public static function setConfig($config) {
        self::$config = $config;
    }

    public static function getConnection($db_name) {
        if (!isset(self::$connections[$db_name])) {
            try {
                $dsn = "mysql:host=" . self::$config[$db_name]['host'] . ";dbname=" . self::$config[$db_name]['database'] . ";charset=utf8mb4";
                $pdo = new PDO($dsn, self::$config[$db_name]['user'], self::$config[$db_name]['password']);
                $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                self::$connections[$db_name] = $pdo;
            } catch (PDOException $e) {
                echo "Connection failed: " . $e->getMessage();
                return null;
            }
        }
        return self::$connections[$db_name];
    }

    public static function closeConnection($db_name) {
        if (isset(self::$connections[$db_name])) {
            self::$connections[$db_name] = null;
            unset(self::$connections[$db_name]);
        }
    }

    public static function closeAllConnections() {
        foreach (self::$connections as &$connection) {
            $connection = null;
        }
        self::$connections = [];
    }
}

// 使用示例
DatabaseManager::setConfig($db_config);
$pdo1 = DatabaseManager::getConnection('db1');
$pdo2 = DatabaseManager::getConnection('db2');

// ... 使用 $pdo1 和 $pdo2 进行数据库操作

DatabaseManager::closeConnection('db1');
DatabaseManager::closeAllConnections(); // 或者关闭所有连接

DatabaseManager

// 获取用户角色 (假设已经实现了用户身份验证)
$user_role = $_SESSION['user_role'];

// 根据用户角色选择数据库
switch ($user_role) {
    case 'admin':
        $db_name = 'db_admin';
        break;
    case 'user':
        $db_name = 'db_user_' . $_SESSION['user_id']; // 每个用户一个数据库
        break;
    default:
        $db_name = 'db_public';
}

// 连接到选定的数据库
$pdo = DatabaseManager::getConnection($db_name);

// ... 使用 $pdo 进行数据库操作