跨双数据库多表关联查询仅返回单条结果的解决方案求助

问题背景 #

现有两个数据库：admin和users：

• admin库的user_accounts表包含course字段；
• users库包含booked_courses（字段user、course_name）和user_accounts（字段email）表。

需求：从admin库user_accounts获取当前登录用户的course值，匹配users库booked_courses的course_name，取出所有对应的user（邮箱值），再关联users库user_accounts的email字段，展示所有匹配的用户信息。

原代码仅返回一条结果，核心原因是用mysqli_fetch_assoc()只获取了booked_courses的单条数据，未遍历所有匹配记录。

原代码 #

<?php

// Include the connection for the admin database

include_once 'handlers/db_conn_admin.php';

// Select and fetch the information from user_accounts table in the admin database

$sql = $conn->prepare("SELECT * FROM user_accounts WHERE username = ?");
$sql->bind_param("s", $username);

$username = $_SESSION['username'];

$sql->execute();
$result = $sql->get_result();
$trainer = mysqli_fetch_assoc($result);

// Now include the connection for the users database

include_once 'handlers/db_conn_users.php';

// Select and fetch the information from booked_courses table in the users database where the course_name is whatever is the value of "course" column in user_accounts table in the admin database

$sql_booked_courses = $conn->prepare("SELECT * FROM booked_courses WHERE course_name=?");
$sql_booked_courses->bind_param("s", $course_name);

$course_name = $trainer['course'];

$sql_booked_courses->execute();
$result_booked_courses = $sql_booked_courses->get_result();
$email = mysqli_fetch_assoc($result_booked_courses);

// Now select and fetch the information from user_accounts table in the users database where the email is whatever is the value of "user" column in booked_courses table in the users database

$sql_user_accounts = $conn->prepare("SELECT * FROM user_accounts WHERE email=?");
$sql_user_accounts->bind_param("s", $user);

$user = $email['user'];

$sql_user_accounts->execute();
$result_user_accounts = $sql_user_accounts->get_result();

// And now for the end make the loop to display all the information about the users of the selected email addresses (in this case "email@example.com" and "email2@example.com")

while($user_info = $result_user_accounts->fetch_assoc()) {

?>

<h1><?php echo $user_info['email'] ?></h1>

<?php

}

?>

解决方案 #

方案1：修改原代码，遍历所有匹配的booked_courses记录 #

核心逻辑是循环遍历booked_courses的所有结果，针对每个user邮箱查询并展示用户信息：

<?php
include_once 'handlers/db_conn_admin.php';

// 获取当前登录用户的course值（仅查询需要的字段）
$sql = $conn->prepare("SELECT course FROM user_accounts WHERE username = ?");
$sql->bind_param("s", $_SESSION['username']);
$sql->execute();
$result = $sql->get_result();
$trainer = mysqli_fetch_assoc($result);
$course_name = $trainer['course'];

include_once 'handlers/db_conn_users.php';

// 查询所有匹配course_name的booked_courses记录
$sql_booked_courses = $conn->prepare("SELECT user FROM booked_courses WHERE course_name=?");
$sql_booked_courses->bind_param("s", $course_name);
$sql_booked_courses->execute();
$result_booked_courses = $sql_booked_courses->get_result();

// 预编译用户查询语句，避免重复编译
$sql_user_accounts = $conn->prepare("SELECT * FROM user_accounts WHERE email=?");

// 遍历所有booked记录，逐个查询用户信息
while($booked = mysqli_fetch_assoc($result_booked_courses)) {
    $user_email = $booked['user'];
    $sql_user_accounts->bind_param("s", $user_email);
    $sql_user_accounts->execute();
    $user_result = $sql_user_accounts->get_result();
    
    // 输出用户信息（处理可能不存在的用户）
    if($user_info = $user_result->fetch_assoc()) {
?>
        <h1><?php echo htmlspecialchars($user_info['email']) ?></h1>
        <!-- 可添加更多用户字段展示，如姓名、ID等 -->
<?php
    }
}
?>

方案2：使用跨数据库JOIN查询（更高效） #

如果数据库用户有权限访问两个库，可以直接用一条SQL完成关联，减少多次查询的开销：

<?php
include_once 'handlers/db_conn_admin.php';

// 获取当前用户的course值
$sql = $conn->prepare("SELECT course FROM user_accounts WHERE username = ?");
$sql->bind_param("s", $_SESSION['username']);
$sql->execute();
$result = $sql->get_result();
$trainer = mysqli_fetch_assoc($result);
$course_name = $trainer['course'];

// 切换到users库的连接
include_once 'handlers/db_conn_users.php';

// 跨库关联查询：直接关联booked_courses和user_accounts
$sql = $conn->prepare("
    SELECT ua.* 
    FROM booked_courses bc
    JOIN user_accounts ua ON bc.user = ua.email
    WHERE bc.course_name = ?
");
$sql->bind_param("s", $course_name);
$sql->execute();
$result = $sql->get_result();

// 遍历所有结果输出
while($user_info = $result->fetch_assoc()) {
?>
    <h1><?php echo htmlspecialchars($user_info['email']) ?></h1>
    <!-- 展示更多用户信息字段 -->
<?php
}
?>

关键优化点 #

1. 替换单条数据获取为循环遍历所有booked_courses匹配记录；
2. 方案2通过JOIN减少数据库查询次数，大幅提升执行效率；
3. 添加htmlspecialchars()防止XSS攻击，增强代码安全性；
4. 仅查询需要的字段（如只查course和user），减少不必要的数据传输。

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