0

So I'm working on quite a big school project and our goal is to show the presence or absence of all students at our school, we have to show this data using chartJS, now we made a query which selects all classes and a foreach-loop runs through the classes and the the query to select the presence and convert it to JSON, I've been working on this function for quite a few days but I just can't seem to make it work...

Here is my QueryManager which activates the query:

public function getAanwezigheid($klassen){
    $result = $this->dbconn->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 = '".$klassen."' 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 = '".$klassen."' 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 = '".$klassen."' AND vak.code = 'WFHBOICT.M032.16' AND college.college = '8'
GROUP BY klas.code");
    $data = array();
    foreach ($result as $row) {
    $data[] = $row;
    }
    print json_encode($data);
}

Here is the code which gets the query(the unserialize gets all classes which are available which are class A-F):

if($_GET['action']=='getAanwezigheid'){
    header('Content-Type: application/json');
    $klas = unserialize($_SESSION['klas']);

    /*This loop runs the query for every class which has been found (A-F)*/
    foreach($klas as $klas){
    $klassen = $klas->getCode();
    $result = $q->getAanwezigheid($klassen); 
    }
}

Here is the result I get:

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

And I want the result to be this:

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

Now I get why I get all the brackets, it's because I loop through data[] every class too, but that's what I'm stuck at. I just need to print 1 JSON result

Any help would be really appreciated.

sebas2201
  • 145
  • 3
  • 17
  • `Security warning at line 8: possible MySQL injection near WHERE klas.code = '".$klassen."' AND`. Please consider using [MySQLi](https://stackoverflow.com/a/549000/1493235) instead. MySQL is deprecated within PHP. – JohannesB May 26 '17 at 11:40
  • @JohannesB Yeah I was working on improving some other things regarding the system and your suggestions but my main problem is just that the code won't work, it really bothers me cause it's still quite a big task – sebas2201 May 26 '17 at 11:54

0 Answers0