php - Left Join With Null 值表

Question

我正在使用这个查询来连接我的学生表和出勤表，
我的问题是，有时出勤表没有值(value)。
它没有返回任何值。

<?php
if($_SERVER['REQUEST_METHOD']=="POST"){
    include('include/connection.php');
    showData();
}

function showData(){
    global $connect;
    $teacher_id = $_POST['teacher_id'];
    $subject_id = $_POST['subject_id'];
    $date = $_POST['date'];
    $query ="
SELECT s.student_name
     , s.student_number
     , s.student_section
     , s.subject_id
     , s.fingerprint_id
     , s.teacher_id
     , a.status
  FROM tbl_student s
  LEFT 
  JOIN tbl_attendance a 
    on s.subject_id=a.subject_id
 WHERE s.subject_id = '$subject_id' 
   and a.date='$date' 
   and s.teacher_id = '$teacher_id';";
    $result =mysqli_query($connect,$query);
    $number_of_rows = mysqli_num_rows($result);
    $temp_array=array();

    if($number_of_rows>0){
        while($row=mysqli_fetch_assoc($result)){
            $temp_array[]=$row;     
        }
    }
    header('Content-Type: application/json');
    echo json_encode(array("student"=>$temp_array));
    mysqli_close($connect);
}


?>

我要实现的是即使考勤表没有值(value)，
我仍然可以看到学生字段。
SQL查询甚至可能吗？谢谢

Answer 1

您必须将表 attendance 的字段从 where 移动到 on 条件:

$query ="SELECT student.student_name,student.student_number,student.student_section,student.subject_id,student.fingerprint_id,student.teacher_id,attendance.status
    FROM tbl_student student
    LEFT JOIN tbl_attendance attendance on student.subject_id=attendance.subject_id and attendance.date='$date'
    WHERE student.subject_id='$subject_id'  and student.teacher_id='$teacher_id';";

因为会先执行join语句，再执行where，如果访问表tbl_attendance，其中ans所有列都为null，就会被过滤掉。

提示:阅读准备好的语句以提供 SQL 注入(inject)