PHP Array json format

Question

I made a function to retrieve some informations from a database table and store it in array, then I need to encode it in json specific format. This is the function:

public function json() {
 $query = "SELECT ax.nome as auxiliar, t.assiduidade, t.normas, 
 t.eficiencia, t.relacionamento, t.iniciativa, 
 t.visao, ( 
 SELECT truncate(avg(t.assiduidade + t.normas + t.eficiencia + t.relacionamento + t.iniciativa + t.visao)/6, 2) 
 FROM topico t 
 WHERE a.id = t.idAval 
 ) AS media 
 FROM avaliacao a 
 INNER JOIN supervisor s ON a.supervisor_id = s.id 
 INNER JOIN auxiliar ax ON a.auxiliar_id = ax.id 
 INNER JOIN departamento d ON a.departamento_id = d.id 
 inner join topico t on a.id = t.idAval 
 WHERE a.departamento_id = ?
 and date(a.data) between ? and last_day(?)";
 $stmt = $this->conn->prepare($query);
 $info["aval"] = array();
 $stmt->bindParam(1, $this->departamento);
 $stmt->bindParam(2, $this->data);
 $stmt->bindParam(3, $this->data);
 $stmt->execute();
 while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
 extract($row);
 $info2 = ['name' => $auxiliar, 'y' => $media, 'drilldown' => $auxiliar, 'data' => [['assiduidade', floatval($assiduidade)], ['normas', floatval($normas)], ['eficiencia', floatval($eficiencia)], ['relacionamento', floatval($relacionamento)], ['iniciativa', floatval($iniciativa)], ['visao', floatval($visao)]]];
 array_push($info, $info2);
 }
 //var_dump($info);
 $fp = fopen('data.json', "w");
 fwrite($fp, json_encode($info, JSON_PRETTY_PRINT));
 fclose($fp);
}

this is the output of data.json:

{
 "aval": [
 ],
 "0": {
 "name": "Isratshawatt Sousa",
 "y": "7.83",
 "drilldown": "Isratshawatt Sousa",
 "data": [
 [
 "assiduidade",
 8
 ],
 [
 "normas",
 7
 ],
 [
 "eficiencia",
 8
 ],
 [
 "relacionamento",
 9
 ],
 [
 "iniciativa",
 6
 ],
 [
 "visao",
 9
 ]
 ]
 },
 "1": {
 "name": "Jo\u00e3o Batista J\u00fanior",
 "y": "7.85",
 "drilldown": "Jo\u00e3o Batista J\u00fanior",
 "data": [
 [
 "assiduidade",
 9.8
 ],
 [
 "normas",
 7.5
 ],
 [
 "eficiencia",
 8.8
 ],
 [
 "relacionamento",
 6.6
 ],
 [
 "iniciativa",
 5.5
 ],
 [
 "visao",
 8.9
 ]
 ]
 } }

And this is the format I need:

{
 "aval": [
 {
 "name": "Isratshawatt Sousa",
 "y": "7.83",
 "drilldown": "Isratshawatt Sousa",
 "data": [
 [
 "assiduidade",
 8
 ],
 [
 "normas",
 7
 ],
 [
 "eficiencia",
 8
 ],
 [
 "relacionamento",
 9
 ],
 [
 "iniciativa",
 6
 ],
 [
 "visao",
 9
 ]
 ]
 },
 {
 "name": "Jo\u00e3o Batista J\u00fanior",
 "y": "7.85",
 "drilldown": "Jo\u00e3o Batista J\u00fanior",
 "data": [
 [
 "assiduidade",
 9.8
 ],
 [
 "normas",
 7.5
 ],
 [
 "eficiencia",
 8.8
 ],
 [
 "relacionamento",
 6.6
 ],
 [
 "iniciativa",
 5.5
 ],
 [
 "visao",
 8.9
 ]
 }
 ]
 }

My question is how can I format the $info array to reproduce the json format above?

• RiggsFolly: If you ever get the urge to use extract fight against it until the MADNESS passes This would do the same thing $info2[] = $row; if you had names the columns using as the way you want them in your JSON
• kelsen: I think I understand you, the point is that I need that JSON format to be able to generate graphs.

Answer 1

You are using array_push with wrong array. If you want to add items to "aval", you can change you code like this:

public function json() {
$query = "SELECT ax.nome as auxiliar, t.assiduidade, t.normas, t.eficiencia, t.relacionamento, t.iniciativa, t.visao, ( SELECT truncate(avg(t.assiduidade + t.normas + t.eficiencia + t.relacionamento + t.iniciativa + t.visao)/6, 2) FROM topico t WHERE a.id = t.idAval ) AS media FROM avaliacao a INNER JOIN supervisor s ON a.supervisor_id = s.id INNER JOIN auxiliar ax ON a.auxiliar_id = ax.id INNER JOIN departamento d ON a.departamento_id = d.id inner join topico t on a.id = t.idAval WHERE a.departamento_id = ?
 and date(a.data) between ? and last_day(?)";
$stmt = $this->conn->prepare($query);
$info["aval"] = array();
$stmt->bindParam(1, $this->departamento);
$stmt->bindParam(2, $this->data);
$stmt->bindParam(3, $this->data);
$stmt->execute();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
 extract($row);
 $info2 = ['name' => $auxiliar, 'y' => $media, 'drilldown' => $auxiliar, 'data' => [['assiduidade', floatval($assiduidade)], ['normas', floatval($normas)], ['eficiencia', floatval($eficiencia)], ['relacionamento', floatval($relacionamento)], ['iniciativa', floatval($iniciativa)], ['visao', floatval($visao)]]];
 $info["aval"][] = $info2;
}
$fp = fopen('data.json', "w");
fwrite($fp, json_encode($info, JSON_PRETTY_PRINT));
fclose($fp);
}

Note the line

$info["aval"][] = $info2;

Also, using array[] instead of array_push is a little bit faster, because there is no function call performed.

• kelsen: Perfect! it seems that I do not need to initialize the array as well ? $info["aval"] = array();
• Andrew: It depends of your app logic. If you haven't initialize array and for some reason query will return zero rows, then you will have result JSON like {"aval": null} Possibly it's better to have empty array in this case: {"aval": []}.