如何在JOIN关联的多表场景下编写包含跨表字段的搜索查询

问题原因 #

1. 单独查询tb_ctsreport表本身不含firstName、lastName字段，因此PHP读取结果集时会报未定义索引错误
2. UNION语法要求前后两个查询返回的字段数量、字段类型顺序完全一致，两张表结构不同自然执行报错，该场景无需使用UNION

解决方法 #

直接保留原有LEFT JOIN关联逻辑，在关联后的结果集上追加搜索条件即可同时匹配两张表的字段：

// 基础写法（已做基础SQL注入转义）
$query = "SELECT 
            tb_ctsreport.qr_id,
            tb_ctsreport.idNum,
            CONCAT(tb_usersreg.lastName, tb_usersreg.firstName) AS full_name,
            tb_ctsreport.date,
            tb_ctsreport.time
          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,
              IFNULL(tb_usersreg.lastName, ''),
              IFNULL(tb_usersreg.firstName, '')
            ) LIKE '%".mysqli_real_escape_string($conn, $searchBox)."%'";

代码说明：

• 主动给拼接后的姓名定义别名full_name，PHP输出时直接读取$row['full_name']即可，不会再出现索引未定义问题
• 加入IFNULL处理无匹配用户的场景，避免关联结果为NULL时CONCAT返回NULL导致漏搜
• 用mysqli_real_escape_string转义搜索参数，避免基础SQL注入风险

更推荐使用预处理语句实现，安全性更高，示例如下（以mysqli为例）：

$stmt = $conn->prepare("SELECT 
            tb_ctsreport.qr_id,
            tb_ctsreport.idNum,
            CONCAT(tb_usersreg.lastName, tb_usersreg.firstName) AS full_name,
            tb_ctsreport.date,
            tb_ctsreport.time
          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,
              IFNULL(tb_usersreg.lastName, ''),
              IFNULL(tb_usersreg.firstName, '')
            ) LIKE ?");
$searchParam = "%".$searchBox."%";
$stmt->bind_param("s", $searchParam);
$stmt->execute();
$result = $stmt->get_result();

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