PHP with Json + SQL

Question

I'm having trouble correctly display, in JSON, a result originated from two tables in my database.

The situation is this: I have orders in a table (A) and the products these orders in another table (B).

My code:

$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;

The JSON result 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 is only one product in each order. I needed the result to be as below (Detalhes TAG):

{
    "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"
                }
               ]
             }
            ]
}

Answer 1

A quick example of what FirstOne suggests would look like this. See how the orders are selected first and then a 2nd query is used to gather additional information about that order.

$sqlcode2 = mysql_query("Select a.numero as numOrc, a.nomeclie, a.valortotal, a.formapagto, a.emissao from orcamento a");

$jsonObj= array();

if($something == 'all')
{  
    while($result=mysql_fetch_object($sqlcode2))
    {

        $sqlcode3 = mysql_query("Select b.codprod, b.qtdade, b.valorunit, b.tipopreco
           FROM prodorc b WHERE b.numOrc = " . $result->numOrc);

        $Detalhes = array();
        while($orderResult=mysql_fetch_object($sqlcode3))
        {
             $Detalhes[] = $orderResult;
        }
        $result->Detalhes = $Detalhes;

        $jsonObj[] = $result;

    }
}
$teste= array('pedidos' => $jsonObj);

$final_res =json_encode($teste);
echo $final_res;