How to mount a JSON return?

Question

I have this code, below, where I manipulate some data. But after, I need to transform into a JSON object, as the example below.

<?php
function mostraContasEntrada($id_empresa){
    $pdo = conectar();
    $this->mostraDadosEntrada=$pdo->prepare(
        "SELECT c.categoria, sc.subcategoria, data, valor 
         FROM entrada e 
         JOIN cat_entradas c 
         on c.id_categoria = e.categoria 
         JOIN sub_cat_entrada sc 
         on sc.id_subcategoria 
         WHERE id_empresa=:id_empresa 
         ORDER BY data DESC");
    $this->mostraDadosEntrada->bindValue(":id_empresa", $id_empresa);
    $this->mostraDadosEntrada->execute();

    while ($r = $this->mostraDadosEntrada->fetch(PDO::FETCH_ASSOC)) {
        $dataP = explode("-", $r['data']);
        $data = $dataP[2].'/'.$dataP[1].'/'.$dataP[0];

        echo $data.'  '.$r['categoria'].'  '.utf8_encode($r['subcategoria']).'
        '.number_format($r['valor'],2,',','.')."<br>";
    }
?>

I want to do this:

<?php
header('Content-Type: application/json');
$return = array();
while ($linha=$buscarUsuario->fetch(PDO::FETCH_ASSOC)) {
    array_push($return, $linha);
}
echo json_encode($return);
?>

Answer 1

Format the date by the bank with date_format() so you don’t have to do it by php. Create a new array, format the currency value and 'stack' the items at the end of the function return the a string(json).

function mostraContasEntrada($id_empresa){    
    $pdo = conectar();
    $this->mostraDadosEntrada=$pdo->prepare(
        "SELECT c.categoria, sc.subcategoria, date_formart(data, '%d/%m/%Y'),  valor 
         FROM entrada e 
         JOIN cat_entradas c 
         on c.id_categoria = e.categoria 
         JOIN sub_cat_entrada sc 
         on sc.id_subcategoria 
         WHERE id_empresa=:id_empresa 
         ORDER BY data DESC");
    $this->mostraDadosEntrada->bindValue(":id_empresa", $id_empresa);
    $this->mostraDadosEntrada->execute();

    $lista = array();
    while ($r = $this->mostraDadosEntrada->fetch(PDO::FETCH_ASSOC)) {
        $r['valor'] = number_format($r['valor'],2,',','.');
        $r['subcategoria'] = utf8_encode($r['subcategoria']);
        $lista[] = $r;
    }

    return json_encode($lista);
}   

echo mostraContasEntrada($id);

Answer 2

The most direct way is to create an array of arrays, then convert to JSON in the same way as in the second code:

$return = array();
while ($r = $this->mostraDadosEntrada->fetch(PDO::FETCH_ASSOC)) {
    $dataP = explode("-", $r['data']);
    $data = $dataP[2].'/'.$dataP[1].'/'.$dataP[0];

    array_push($return, array($data, $r['categoria'], utf8_encode($r['subcategoria']), 
                       number_format($r['valor'],2,',','.')));
}
echo json_encode($return);

This will generate a JSON with the following structure:

[["01/02/2015", "cat1", "sub1", 1234], [...], ...]

If you prefer a JSON with objects, instead of arrays, like:

[{
    "data":"01/02/2015",
    "categoria":"cat1",
    "subcategoria":"sub1", 
    "valor":1234
 }, 
 {...}, 
 ...
]

Then just push an associative array on $return, instead of a common array:

    array_push($return, array(
        "data" => $data,
        "categoria" => $r['categoria'],
        "subcategoria" => utf8_encode($r['subcategoria']),
        "valor" => number_format($r['valor'],2,',','.')
    ));

You can adapt the keys of the above example as you think best.