Foreach loop in MySQL query

Question

Let me explain a little bit of backstory, a teacher can set a student present or absent. The values of those students are put in a database, this query selects the class code and calculates the percentage of students present at a certain lesson, however I'm kind of stuck on what to do, the file has to be converted to JSON and put in a ChartJS bar graph but for some reason I just can't seem to figure this code out, each percentage has to be calculated for each class so I cannot use IN or something like that cause that would calculate the presence of the entire lesson in stead of the class per lesson (that's what the klas.code = '$klas' is for) does anyone know how I can get 1 result but still be able to calculate the percentage for each class seperately?

Thank you.

$klassen = array("WFHBOICT.V1E", "WFHBOICT.V1F");
foreach($klassen as $klas){
//query to get data from the table
$query = ("SELECT klas.code klas, ROUND(
(
    SELECT Count(aanwezigheid) 
    FROM aanwezigheid 
    JOIN college ON aanwezigheid.Ccode = college.code  
    JOIN klas ON college.Kcode = klas.code
    WHERE klas.code = '".$klas."' AND vak.code = 'WFHBOICT.M032.16' AND college.college = '8'
    AND aanwezigheid = '1'
) 
/
(
    SELECT Count(aanwezigheid) 
    FROM aanwezigheid 
    JOIN college ON aanwezigheid.Ccode = college.code  
    JOIN klas ON college.Kcode = klas.code
    WHERE klas.code = '".$klas."' AND vak.code = 'WFHBOICT.M032.16' AND college.college = '8'
) 
* 100)
    as percentage
FROM aanwezigheid 
JOIN college ON aanwezigheid.Ccode = college.code 
JOIN klas ON college.Kcode = klas.code 
JOIN vak ON college.Vcode = vak.code 
WHERE klas.code = '".$klas."' AND vak.code = 'WFHBOICT.M032.16' AND college.college = '8'
GROUP BY klas.code");

//execute query
$result = $mysqli->query($query);

//loop through the returned data
$data = array();
foreach ($result as $row) {
$data[] = $row;
}

print json_encode($data);

This is the result:

[{"klas":"WFHBOICT.V1F","percentage":"67"}]

It has to return both classes between the brackets in stead of just 1...

See meta.stackoverflow.com/questions/333952/…

Strawberry
– Strawberry

2017-05-26 18:20:06 +00:00
Commented May 26, 2017 at 18:20 — Strawberry
– Strawberry, Commented May 26, 2017 at 18:20

JazZ · Accepted Answer · 2017-05-26 18:22:03Z

The problem comes from the first foreach loop. Initialize the $data array before it and close the first foreach before the print.

$klassen = array("WFHBOICT.V1E", "WFHBOICT.V1F");

$data = array();
foreach($klassen as $klas){
    //query to get data from the table
    $query = ("SELECT klas.code klas, ROUND(
    (
        SELECT Count(aanwezigheid)
        FROM aanwezigheid
        JOIN college ON aanwezigheid.Ccode = college.code
        JOIN klas ON college.Kcode = klas.code
        WHERE klas.code = '".$klas."' AND vak.code = 'WFHBOICT.M032.16' AND college.college = '8'
        AND aanwezigheid = '1'
    )
    /
    (
        SELECT Count(aanwezigheid)
        FROM aanwezigheid
        JOIN college ON aanwezigheid.Ccode = college.code
        JOIN klas ON college.Kcode = klas.code
        WHERE klas.code = '".$klas."' AND vak.code = 'WFHBOICT.M032.16' AND college.college = '8'
    )
    * 100)
        as percentage
    FROM aanwezigheid
    JOIN college ON aanwezigheid.Ccode = college.code
    JOIN klas ON college.Kcode = klas.code
    JOIN vak ON college.Vcode = vak.code
    WHERE klas.code = '".$klas."' AND vak.code = 'WFHBOICT.M032.16' AND college.college = '8'
    GROUP BY klas.code");

    //execute query
    $result = $mysqli->query($query);

    //loop through the returned data

    foreach ($result as $row) {
        $data[] = $row;
    }
} // end first foreach
print json_encode($data);

Hope it helps.

Collectives™ on Stack Overflow

Foreach loop in MySQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related