在关联查询中使用搜索条件：跨多表数据的高效检索指南

SELECT
    tcr.qr_id,
    tcr.idNum,
    tcr.date,
    tcr.time,
    tur.firstName,
    tur.lastName
FROM
    tb_ctsreport AS tcr
LEFT JOIN
    tb_usersreg AS tur ON tcr.idNum = tur.idNum;

SELECT
    tcr.qr_id,
    tcr.idNum,
    tcr.date,
    tcr.time,
    tur.firstName,
    tur.lastName
FROM
    tb_ctsreport AS tcr
LEFT JOIN
    tb_usersreg AS tur ON tcr.idNum = tur.idNum
WHERE
    CONCAT(
        tcr.qr_id,
        tcr.idNum,
        tcr.time,
        tcr.date,
        tur.lastName,
        tur.firstName
    ) LIKE :searchBox;

// 假设 $searchBox 是直接来自用户输入的变量
$query = "SELECT * FROM tb_ctsreport LEFT JOIN tb_usersreg ON tb_ctsreport.idNum=tb_usersreg.idNum WHERE CONCAT(tb_ctsreport.qr_id, tb_ctsreport.idNum, tb_ctsreport.time, tb_ctsreport.date, tb_usersreg.lastName, tb_usersreg.firstName) LIKE '%" . $searchBox . "%'";
// 这种拼接方式极易受到SQL注入攻击

<?php
// 假设数据库连接已建立
$host = 'localhost';
$db   = 'your_database_name';
$user = 'your_username';
$pass = 'your_password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

// 获取用户输入的搜索关键词
$searchBox = isset($_GET['search']) ? $_GET['search'] : '';
$searchParam = '%' . $searchBox . '%'; // 为LIKE操作符添加通配符

$sql = "SELECT
            tcr.qr_id,
            tcr.idNum,
            tcr.date,
            tcr.time,
            tur.firstName,
            tur.lastName
        FROM
            tb_ctsreport AS tcr
        LEFT JOIN
            tb_usersreg AS tur ON tcr.idNum = tur.idNum
        WHERE
            CONCAT(
                tcr.qr_id,
                tcr.idNum,
                tcr.time,
                tcr.date,
                tur.lastName,
                tur.firstName
            ) LIKE :searchBox";

$stmt = $pdo->prepare($sql);
$stmt->bindParam(':searchBox', $searchParam, PDO::PARAM_STR); // 绑定参数
$stmt->execute();

$results = $stmt->fetchAll();

// 打印结果
foreach ($results as $row) {
    echo "QR ID: " . $row['qr_id'] . ", Name: " . $row['firstName'] . " " . $row['lastName'] . ", Date: " . $row['date'] . "<br>";
}

?>