如何实现登录验证后根据机构参数切换数据库？

我的Web应用登录表单包含机构、用户名、密码三个字段。我希望实现以下逻辑：

• 先通过一个统一的登录数据库完成用户登录验证
• 验证通过后关闭该登录数据库连接
• 根据用户选择的机构，打开对应的独立数据库，实现每个机构拥有专属数据库及数据表的需求

登录页面包含上述三个字段的输入框及提交按钮。

配置文件（config.php） #

<?php
session_start();

// 连接统一的登录验证数据库
$connLogin = mysqli_connect('localhost','root','', 'login_users');

// 此处仅作示意：实际无法直接用变量$agency初始化连接，需在登录验证后动态创建
$conn = mysqli_connect('localhost', 'root', '', $agency); 
?>

登录处理代码（login.php） #

<?php
@include 'config.php';

if(!isset($_SESSION)) { 
    session_start(); 
} 

if(isset($_POST['submit'])){
    // 获取表单提交参数并转义
    $agency = mysqli_real_escape_string($connLogin, $_POST['agency']);
    $username = mysqli_real_escape_string($connLogin, $_POST['username']);
    $password = mysqli_real_escape_string($connLogin, $_POST['password']);

    // 查询登录数据库验证用户身份
    $select = " SELECT * FROM login_users WHERE agency = '$agency' && username = '$username' && password = '$password' ";
    $result = mysqli_query($connLogin, $select);

    if(mysqli_num_rows($result) > 0){
        $row = mysqli_fetch_array($result);

        // 检查用户是否处于激活状态
        if ($row['active_inactive'] > 0) {
            // 关闭登录数据库连接
            mysqli_close($connLogin);
            
            // 动态连接当前用户所属机构的专属数据库
            $conn = mysqli_connect('localhost', 'root', '', $agency);
            // 建议添加连接失败的错误处理逻辑
            
            // 存储用户会话信息
            $_SESSION['user_name'] = $row['username'];
            // 存储机构信息到会话，供后续页面调用
            $_SESSION['agency'] = $agency;
            
            header('location:worker.php');
            exit; // 跳转后终止脚本执行，避免后续代码干扰
        }
    }else{   
        echo '<script>alert("无效的登录凭证。如果您认为这是错误，请联系系统管理员。")</script>'; 
    }
}
?>

用户及机构变量设置文件（示例：worker.php 公共引入文件） #

<?php
@include 'config.php';

// 未登录用户强制跳转至登录页
if(!isset($_SESSION['user_name'])){
    header('location:login.php');
    exit;
}

$currentUser = $_SESSION['user_name'];
$currentAgency = $_SESSION['agency'];

// 重新连接当前机构的专属数据库（config.php初始化时无法获取有效$agency值）
$conn = mysqli_connect('localhost', 'root', '', $currentAgency);
// 建议添加连接失败的错误处理逻辑

// 获取用户基础信息
$select = " SELECT * FROM user_db WHERE username = '$currentUser' ";
$result = mysqli_query($conn, $select);
$row = mysqli_fetch_assoc($result);

$userid = $row['id'];
$firstname = $row['firstname'];
$position = $row['position'];
$lastname = $row['lastname'];
$email = $row['email'];
$agency = $row['agency'];
$department_name = $row['department_name'];
$phone = $row['phone_number'];
$address = $row['address'];
$city = $row['city'];
$state = $row['state'];
$zip = $row['zip'];
$country = $row['country'];
$profile_picture = $row['profile_picture'];
$default_picture = $row['default_pic'];

// 获取用户已发放装备信息
$selectIssuedGear = " SELECT * FROM user_issued_gear WHERE member_first_name = '$firstname' AND member_last_name = '$lastname' ";
$resultIssuedGear = mysqli_query($conn, $selectIssuedGear);
$rowIssuedGear = mysqli_fetch_assoc($resultIssuedGear);

$gearID = $rowIssuedGear['id'];
$turnoutCoat = $rowIssuedGear['coat_size'];
$turnoutPants = $rowIssuedGear['pants_size'];
$boots = $rowIssuedGear['boots_size'];
$fireGloves = $rowIssuedGear['fire_gloves_size'];
$streamlite = $rowIssuedGear['streamlite'];
$ScbaSize = $rowIssuedGear['scba_mask'];
$safetyVest = $rowIssuedGear['safety_vest_size'];
$extGloves = $rowIssuedGear['ext_gloves_size'];
$pagerSerial = $rowIssuedGear['pager'];
$pagerBaseSerial = $rowIssuedGear['pager_charging_base'];
$winterCoat = $rowIssuedGear['reflective_winter_coat'];

// 获取机构设备信息
$selectApparatus = " SELECT * FROM apparatus ";
$resultApparatus = mysqli_query($conn, $selectApparatus);
$rowApparatus = mysqli_fetch_assoc($resultApparatus);

$apparatus1 = $rowApparatus['apparatus_1'];
$apparatus2 = $rowApparatus['apparatus_2'];
$apparatus3 = $rowApparatus['apparatus_3'];
$apparatus4 = $rowApparatus['apparatus_4'];
$apparatus5 = $rowApparatus['apparatus_5'];
$apparatus6 = $rowApparatus['apparatus_6'];
$apparatus7 = $rowApparatus['apparatus_7'];
$apparatus8 = $rowApparatus['apparatus_8'];
$apparatus9 = $rowApparatus['apparatus_9'];
$apparatus10 = $rowApparatus['apparatus_10'];
?>

关键优化说明 #

• 修复数据库连接逻辑：原代码中mysqli_connect($conn);为错误用法，需重新创建连接并赋值给$conn
• 会话存储机构信息：登录成功后将机构名称存入会话，避免后续页面重复传递参数
• 跳转后终止脚本：使用exit;确保跳转后脚本不再执行，防止冗余代码干扰
• 安全优化建议：
  • 禁止明文存储密码，使用password_hash()加密存储，登录时用password_verify()验证
  • 替换字符串拼接SQL为预处理语句，彻底规避SQL注入风险
  • 移除@include的错误抑制符，便于调试排查问题

内容的提问来源于stack exchange，提问作者Datapoynt Systems