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?

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 — RiggsFolly
– RiggsFolly, Commented Apr 16, 2016 at 16:56
I think I understand you, the point is that I need that JSON format to be able to generate graphs. — kelsen
– kelsen, Commented Apr 16, 2016 at 17:21

Andrew · Accepted Answer · 2016-04-16 16:55:12Z

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.

Perfect! it seems that I do not need to initialize the array as well ? $info["aval"] = array();
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": []}.

Collectives™ on Stack Overflow

PHP Array json format

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related