PHP执行MySQL多表关联查询返回null，但var_dump有结果的问题排查

在MySQL客户端执行一条多表关联查询语句可得到预期结果，但使用PHP代码传入相同参数执行该查询时，返回结果为null；不过通过var_dump($data)和var_dump($_GET)能看到$data中存在与SQL查询一致的正确数据，请问如何让PHP代码正确返回查询结果？

原SQL语句 #

SELECT user_pictures.picture,users.username,
                    star,quest1.answer1,quest2.answer2,
                    quest3.answer3,quest4.answer4,quest5.answer5,
                    votes.created_at 
            FROM votes 
                LEFT JOIN user_pictures ON votes.voted_image_id=user_pictures.ID 
                LEFT JOIN users ON votes.voting_id=users.ID 
                LEFT JOIN quest1 ON votes.q1=quest1.ID 
                LEFT JOIN quest2 ON votes.q2=quest2.ID 
                LEFT JOIN quest3 ON votes.q3=quest3.ID 
                LEFT JOIN quest4 ON votes.q4=quest4.ID 
                LEFT JOIN quest5 ON votes.q5=quest5.ID 
            WHERE voted_id = "123456" 
            AND voting_id = "123" 

PHP代码 #

<?php
require_once('connection.php');

if($_GET){
    $voted_id = $_GET['voted_id'];
    $voting_id = $_GET['voting_id'];

    $sql = "SELECT user_pictures.picture,users.username,
                    star,quest1.answer1,quest2.answer2,
                    quest3.answer3,quest4.answer4,quest5.answer5,
                    votes.created_at 
            FROM votes 
                LEFT JOIN user_pictures ON votes.voted_image_id=user_pictures.ID 
                LEFT JOIN users ON votes.voting_id=users.ID 
                LEFT JOIN quest1 ON votes.q1=quest1.ID 
                LEFT JOIN quest2 ON votes.q2=quest2.ID 
                LEFT JOIN quest3 ON votes.q3=quest3.ID 
                LEFT JOIN quest4 ON votes.q4=quest4.ID 
                LEFT JOIN quest5 ON votes.q5=quest5.ID 
            WHERE voted_id = ? 
            AND voting_id = ? ";
    $stmt = $connect->prepare($sql);
    $stmt->bind_param("ii", $voted_id, $voting_id);
    $stmt->execute();
    $result = $stmt->get_result();
    $data = $result->fetch_all(MYSQLI_ASSOC);   
    
    header('Content-Type:application/json;charset=urf-8');
    echo json_encode($data);
}
?>

var_dump输出结果 #

array(2) {
    ["voting_id"]=>
  string(6) "123456"
  ["voted_id"]=>
  string(3) "123"
}
array(2) {
    [0]=>
  array(9) {
        ["picture"]=>
    string(4) "pic2"
        ["username"]=>
    string(18) "Cihangir Karabulut"
        ["star"]=>
    string(1) "2"
        ["answer1"]=>
    string(6) "Patron"
        ["answer2"]=>
    string(5) "?tici"
        ["answer3"]=>
    string(4) "Sexy"
        ["answer4"]=>
    string(9) "Karadeniz"
        ["answer5"]=>
    string(5) "50-59"
        ["created_at"]=>
    string(19) "2023-01-24 14:16:24"
    }
  [1]=>
  array(9) {
        ["picture"]=>
    string(4) "pic2"
        ["username"]=>
    string(18) "Cihangir Karabulut"
        ["star"]=>
    string(1) "4"
        ["answer1"]=>
    string(7) "�?renci"
        ["answer2"]=>
    string(9) "G�venilir"
        ["answer3"]=>
    string(10) "At H?rs?z?"
        ["answer4"]=>
    string(6) "Do?ulu"
        ["answer5"]=>
    string(5) "25-29"
        ["created_at"]=>
    string(19) "2023-01-24 19:20:28"
    }
}

问题原因与解决办法 #

1. 参数绑定类型不匹配
   从var_dump($_GET)可以看到，voted_id和voting_id都是字符串类型，但你在bind_param中使用了"ii"（表示两个整数类型）。如果数据库中这两个字段是字符串类型，整数类型的绑定会导致参数不匹配，查询无法返回结果。
   修正代码：将绑定类型改为"ss"（字符串类型）

   $stmt->bind_param("ss", $voted_id, $voting_id);
   

2. 响应头编码拼写错误
   代码中header('Content-Type:application/json;charset=urf-8')里的urf-8是拼写错误，正确应为utf-8，错误的编码声明会导致JSON输出异常，前端可能识别为null。
   修正代码：

   header('Content-Type:application/json;charset=utf-8');
   

3. 特殊字符转义问题
   从var_dump结果能看到数据包含土耳其语特殊字符，直接使用json_encode会导致特殊字符被转义或乱码，需要添加JSON_UNESCAPED_UNICODE参数保留原始字符。
   修正代码：

   echo json_encode($data, JSON_UNESCAPED_UNICODE);
   

修改后的完整PHP代码 #

<?php
require_once('connection.php');

if($_GET){
    $voted_id = $_GET['voted_id'];
    $voting_id = $_GET['voting_id'];

    $sql = "SELECT user_pictures.picture,users.username,
                    star,quest1.answer1,quest2.answer2,
                    quest3.answer3,quest4.answer4,quest5.answer5,
                    votes.created_at 
            FROM votes 
                LEFT JOIN user_pictures ON votes.voted_image_id=user_pictures.ID 
                LEFT JOIN users ON votes.voting_id=users.ID 
                LEFT JOIN quest1 ON votes.q1=quest1.ID 
                LEFT JOIN quest2 ON votes.q2=quest2.ID 
                LEFT JOIN quest3 ON votes.q3=quest3.ID 
                LEFT JOIN quest4 ON votes.q4=quest4.ID 
                LEFT JOIN quest5 ON votes.q5=quest5.ID 
            WHERE voted_id = ? 
            AND voting_id = ? ";
    $stmt = $connect->prepare($sql);
    $stmt->bind_param("ss", $voted_id, $voting_id);
    $stmt->execute();
    $result = $stmt->get_result();
    $data = $result->fetch_all(MYSQLI_ASSOC);   
    
    header('Content-Type:application/json;charset=utf-8');
    echo json_encode($data, JSON_UNESCAPED_UNICODE);
}
?>

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