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

• FirstOne: Can you do this with 2 queries? First select the pedidos (hue br), then the products from that order.

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;