左连接空值表

Question

我正在使用此查询来连接我的学生表和考勤表，
我的问题是，有时候，考勤表没有任何价值。
它没有返回任何值。

<?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); 
} 


?> 

我想才达到的，即使考勤表有没有价值，
我仍然可以看到学生的领域。
它甚至可能与SQL查询？谢谢

Answer 1

你必须从where移动台attendance领域的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';"; 

因为语句将首先被执行的连接，然后在那里，如果你在访问表tbl_attendance其中ANS所有列是空的，它们将被过滤掉。

提示：了解准备好的发言提供SQL注入

Answer 2

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'; 

试试上面的代码。希望这会有所帮助。 正如您在WHERE子句中使用attendance.date='$date'条件在学生表上做出的条件，它排除了不满足此条件的记录。 所以，而不是我通过ON条款LEFT JOIN条款通过该条件。 这将实现您的目标。