Generate JSON with header and detail of multiple records

Question

Generate JSON with header and detail of multiple records

Asked 5 years, 7 months ago

Viewed 490 times

3

I have the following tables:

orcamento storing information under the heading of the budget;
orcamentoItens that stores budget items;
itens that stores the details of the items.

_{(I’ll put the speakers in query that I use).}

I can generate JSON in the following format for a single budget:

{
    "id": "3",
    "titular": "CARVALHO",
    "data": "2019-04-02",
    "status": "A",
    "itens": [
        {
            "id": "2",
            "nome": "PRODUCT SAMPLE 2",
            "quantidade": "2",
            "preco": "127.74"
        },
        {
            "id": "4",
            "nome": "PRODUCT SAMPLE 5",
            "quantidade": "1",
            "preco": "215.45"
        }
    ]
}

To generate this JSON I use this code:

$id_orcamento = $_GET['id'];
$stmt = $con->query("SELECT id, titular, data, status FROM orcamentos WHERE status = 'A' and id = $id_orcamento LIMIT 1");
$orcamento=$stmt->fetch(PDO::FETCH_ASSOC);
$stmt = $con->query("SELECT i.id, i.nome, oi.quantidade, oi.preco FROM orcamentoItens oi JOIN itens i ON i.id = oi.item WHERE oi.orcamento = $id_orcamento");
$itens = $stmt->fetchAll(PDO::FETCH_ASSOC);
$orcamento['itens']=$itens;
echo json_encode($orcamento);

How to generate the same JSON format for multiple budgets at once?

_{I know I can make one query list all the information from various budgets doing the JOIN of all the tables involved (I already have this query), however as it will repeat the information of the budget header, I do not know how to generate JSON in the format I need.}

Puts all returns in an array, however you need it, then json_enconde everything

– Otto

2019/04/05 at 13:57
Okay, but how do you put all budget items within the key items in the array? I need JSON to maintain the format I put in the question, if you do the json_encode of the query with the JOIN's will generate multiple records with repeated header.

– Laércio Lopes

2019/04/05 at 14:02
So let’s go.... the bank you know is Voce, it is much easier to assemble it within an array as your need .... Once ready you convert this array to json. Here you will find a path to solving your problem. Not his solution, hardly anyone will do for you.

– Otto

2019/04/05 at 14:10
Really? Why did I ask that question right here? Oh man, thank you so much!

– Laércio Lopes

2019/04/05 at 14:17

1 answer

Browser other questions tagged php mysql json

You are not signed in. Login or sign up in order to post.

by Diego Andrade • **695** points · Answer 1 · 2019-04-05T14:34:19+00:00

First you need to do in a single query, to optimize

select  
    o.id, o.titular, o.data, o.status, 
    i.id, i.nome, oi.quantidade, oi.preco 
from orcamentos o
inner join orcamentoItens oi
on oi.orcamento = o.orcamento
inner join itens i on i.id = oi.item
where o.id = $id_orcamento;

After you loop the result, creating an array with the keys being their contents, +- like this:

    foreach ($result as $row) {
        $toJson[$row['o.id']]['id'] = $row["id"];
        $toJson[$row['o.id']]['titular'] = $row["titular"];
        $toJson[$row['o.id']]['data'] = $row["data"];
        $toJson[$row['o.id']]['status'] = $row["status"];
        $toJson[$row['o.id']]['itens'][] = [
            "id" => $row["i.id"],
            "nome" => $row["i.nome"],
            "quantidade" => $row["oi.quantidade"],
            "preco" => $row["oi.preco"]
        ];
    }

Then just return the json

    json_encode($toJson);

Hug