how to display only the pupils whose remark='Failed' in each grade_level

Question

I hope anyone can help me please. I want to display only the pupils whose remark='Failed' in each grade_level. The problem with this code is that even the pupils with remark='Passed' in grade_level=3, it still displays the list of failed pupils in every grade_level since it has remark='Failed' in grade_level=2.

enroll_function_old.php

<?php  
include "php/database.php";  

$data=array();

$sel_grade_level = $_SESSION['grade_level'] - 1;


$school_year = $_SESSION['sy_start']."-".$_SESSION['sy_end'];


$q = mysqli_query($con, "SELECT * FROM students, students_enrolled WHERE 
                        students.student_id = students_enrolled.student_id AND 
                        students_enrolled.grade_level = '".$sel_grade_level."' AND 
                        students_enrolled.remarks = 'Failed' ORDER BY students.lastname,  
                        students.firstname ASC")or die(mysql_error());

while ($row = mysqli_fetch_object($q)){

    $exist = mysqli_query($con, "SELECT * FROM students_enrolled WHERE 
                                student_id = '".$row->student_id."' AND school_year = '".$school_year."' AND 
                                grade_level = '".$_SESSION['grade_level']."' ")or die(mysql_error());
    if (mysqli_num_rows($exist) == 0){
        $data[] = $row;
    }

}

echo json_encode($data);

?>

and here's some part of the html code

<script type="text/javascript">
counter = 1;
cc = 0;

function refreshr() {
    cc = cc + 1;
    if (cc > counter) { 
        cc = 0;
        window.location='enroll_old.php';
    }
}


$("#clicker").keyup(function () { cc = 0; });

$(document).ready(function() {
    var refff = setInterval("refreshr()", 5000); 
    var url = "enroll_function_old.php";
    $.getJSON(url, function(result) {
        console.log(result);
        $.each(result, function(i, field) {

            var student_id = field.student_id;
            var id = field.id;
            var lastname = field.lastname;
            var firstname = field.firstname;
            var gender = field.gender;
            var birthday = field.birthday;
            var address_barangay = field.address_barangay;
            var remarks = field.remarks;

            $("#listview").append("" + 
                "<tr>" +
                    "<td></td>" +
                    "<td>" + firstname + " " + lastname + "</td>" +
                    "<td>" + gender + "</td>" +
                    "<td>" + birthday + "</td>" +
                    "<td>" + address_barangay + "</td>" +
                    "<td><a href='php/enroll_old_old.php?id=" + student_id + "'><button type='button' class='btn btn-info btn-xs'>Add to Class</button></a></td>" +
                "</tr>" +
                "");

        });
    });
});
</script>

Unrelated to the logic error, you're using `mysql_error` with `mysqli_query`, which will not work. See http://stackoverflow.com/questions/17498216/can-i-mix-mysql-apis-in-php — Don't Panic, May 11 '17 at 17:11
Please provide some example contents of the tables, what your code produces, and what you would prefer it to produce. From your description it's not entirely clear what you want and what is wrong. — Wodin, May 11 '17 at 20:30

score 0 · Answer 1 · answered May 12 '17 at 02:38

If I understood correctly, you to display the list of students (pupils, same thing) that are failing ALL the grades. If they have at least one "passed", then you do not want to display them.

The way I would do that is with a subquery in the NOT IN clause of the MySQL Query:

SELECT * FROM students WHERE student_id NOT IN (SELECT student_id FROM students_enrolled WHERE remarks <> 'Failed');

First, we get all the students that have a remark different than failed. Then, we get all the students EXCEPT (NOT IN) those that we selected earlier.

how to display only the pupils whose remark='Failed' in each grade_level

1 Answers1