基于特定ID跨两表查询问题：表单搜索无返回结果排查

我需要通过表单输入学生ID，从Students表获取StudentID、StudentName，同时匹配HomeADD表对应的StudentADD。已确认表单变量$searchq已正确捕获输入值，但使用'%$searchq%'作为查询条件时无结果返回，将其直接替换为字面量'J34765'则查询正常。

问题代码 #

$sql = "SELECT     Students.StudentID, Students.StudentName, HomeADD.StudentADD
        FROM       Students
        INNER JOIN HomeADD
        WHERE      Students.StudentID = '%$searchq%'
        AND        HomeADD.StudentID  = '%$searchq%'";
$result = mysqli_query ($conn,$sql);
$count = mysqli_num_rows ($result);
$none = "";

测试代码 #

$sql = "SELECT     Students.StudentID, Students.StudentName, HomeADD.StudentADD
        FROM       Students
        INNER JOIN HomeADD
        WHERE      Students.StudentID = 'J34765'
        AND        HomeADD.StudentID  = 'J34765'";
$result = mysqli_query ($conn,$sql);
$count = mysqli_num_rows ($result);
$none = "";

问题原因及解决方案 #

1. 多余的通配符%：如果StudentID是精确匹配（如测试用例的J34765），不需要在变量前后加%——%是模糊查询的通配符，精确匹配直接使用变量本身即可。更关键的是，直接将变量拼接进SQL存在注入风险，必须使用预处理语句。
2. INNER JOIN缺少关联条件：原代码中INNER JOIN未指定表关联规则，虽然后续WHERE子句做了等价判断，但规范写法应使用ON子句明确关联逻辑，避免冗余条件。

修正后的安全代码：

// 定义带占位符的预处理SQL
$sql = "SELECT Students.StudentID, Students.StudentName, HomeADD.StudentADD
        FROM Students
        INNER JOIN HomeADD ON Students.StudentID = HomeADD.StudentID
        WHERE Students.StudentID = ?";

// 初始化预处理语句
$stmt = mysqli_prepare($conn, $sql);
// 绑定参数，"s"表示参数为字符串类型
mysqli_stmt_bind_param($stmt, "s", $searchq);
// 执行查询
mysqli_stmt_execute($stmt);
// 获取结果集
$result = mysqli_stmt_get_result($stmt);
$count = mysqli_num_rows($result);
$none = "";

预期结果 #

Student ID  Student Name     Student Address
----------  ---------------  -----------------------------------
J34765      John Doe         123 Main Street, Fresno, California  

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