Help with php and json

Asked

Viewed 44 times

0

I’m having trouble properly displaying in JSON, a result originated from two tables in my database.

The situation is as follows: I have orders in a table (A) and the products in that order in another table (B).

The JSON result I have is like this: 

{
    "pedidos": [
            {
              "numOrc": "1",
              "nomeclie": "CONSUMIDOR",
              "valortotal": "2.077,20",
              "formapagto": "2",
              "emissao": "2013-02-15 16:09:11",
              "codprod": "4775",
              "qtdade": "1",
              "valorunit": "500,00",
              "tipopreco": "B"
             },
             {
              "numOrc": "2",
              "nomeclie": "MARCELO AUGUSTO BOTURA",
              "valortotal": "2.077,20",
              "formapagto": "2",
              "emissao": "2013-02-15 16:21:56",
              "codprod": "4775",
              "qtdade": "1",
              "valorunit": "500,00",
              "tipopreco": "B"
             }
            ]
}

As you can see, the result I have is only one product in each order. I needed the result to be as below (Attention to "tag Detalhes"):

{
    "pedidos": [
             {
              "numOrc": "2",
              "nomeclie": "MARCELO AUGUSTO BOTURA",
              "valortotal": "2.077,20",
              "formapagto": "2",
              "emissao": "2013-02-15 16:21:56",
              "Detalhes": 
               [
                {
                 "codprod": "4775",
                 "qtdade": "1",
                 "valorunit": "500,00",
                 "tipopreco": "B"
                },
                {
                 "codprod": "5555",
                 "qtdade": "3",
                 "valorunit": "800,00",
                 "tipopreco": "A"
                }
               ]
             }
            ]
}

My PHP code looks like this:

$sqlcode2 = mysql_query("Select a.numero as numOrc, a.nomeclie, a.valortotal, a.formapagto, a.emissao, b.codprod, b.qtdade, b.valorunit, b.tipopreco from orcamento a, prodorc b");


$jsonObj= array();


if($something == 'all')
 {  
   while($result=mysql_fetch_object($sqlcode2))
  {
   $jsonObj[] = $result;
   $teste= array('pedidos' => $jsonObj);
  }
 }

$final_res =json_encode($teste);
echo $final_res;
php mysql json

1 answer

1

Good morning, depending on the structure of your tables you can make a Join in the product table using the budget table, if in the budget table you only keep the budget headings and have the products launched in separate budgets in another table, you can use the top tip, if not, if there is no separation of header and products, you will need to in the method that makes the assembly of the JSON read the header of the recovered budget in the query and in a loop check if the record where the cursor is corresponds to that captured budget, if matching you will need to add an object to the JSON budget with product information.

  • The same that I started, in this case is what I refer to at the beginning of the answer, I’m really sorry I read it here and it got a little vague anyway. But I mean the beginning when I say "if the budget table only holds the headings..."

  • As soon as I wrote, I understood what I meant rs. Thank you very much for your help.

  • Thanks even, even I after you spoke did not understand well what I wrote, but it is so living and learning. I hope to have helped. Good morning

  • I did INNER JOIN, now the result shows me all the products, but not in the format I needed... Now it’s "repeating the header" of the pain, every time you have a product... Can you help me?

  • Great, in case what you can do is: loop on the results that were collected mounted the JSON header and then add the products, it would be more or less like this Budget 1: Header : id = 1 client = 1 value = 10,00 products: id = 1 value = 5,00 id = 2 value = 5,00 in the case how the repeat header would have you mount the loop by checking if each record belongs to the header of the previous record, if it belongs you launch the product in the same header as a new JSON object

Browser other questions tagged php mysql json

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