Foreach inside the while on the Json exit

Question

I’ve been searching for days how to do a foreach inside a while to print in the json the data related to that query. My scenario is this:

<?php
require_once('../Connections/db.php');
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");

$tabelabd = "carros";

$con = @mysqli_connect($hostname_db, $username_db, $password_db, $database_db);

if (!$con) {
 trigger_error('Could not connect to MySQL: ' . mysqli_connect_error());
}
$var = array();
$sql = "SELECT * FROM carros WHERE idusuario='1' AND ativo='1' ORDER BY id DESC";
$result = mysqli_query($con, $sql);

while($obj = mysqli_fetch_object($result)) {
$var[] = $obj;
}
echo '{"'.$tabelabd.'":'.json_encode($var).'}';
?>

Json’s way out of this:

[  
   {  
      "id":"1",
      "idusuario":"1",
      "data_cadastro":"2015-09-11",
      "ativo":"S",
      "titulo":"Gol 2009",
      "galeria":"20",
      "destaque":"S"
   },
   {  
      "id":"2",
      "idusuario":"1",
      "data_cadastro":"2015-09-11",
      "ativo":"S",
      "titulo":"Ford Ka 2002",
      "galeria":"21",
      "destaque":"S"
   }
]

// PRECISO QUE A SAIDA SEJA COM A LISTA DE FOTOS(FOREACH) ASSIM:

[  
   {  
      "id":"1",
      "idusuario":"1",
      "data_cadastro":"2015-09-11",
      "ativo":"S",
      "titulo":"Gol 2009",
      "cor":"Prata",
      "galeria":[  
         {  
            "foto_id":1,
            "opcional_nome":"Foto 01"
         },
         {  
            "foto_id":2,
            "opcional_nome":"Foto 03"
         },
         {  
            "foto_id":3,
            "opcional_nome":"Foto 03"
         }
      ],
      "destaque":"S",
   },
   {  
      "id":"2",
      "idusuario":"1",
      "data_cadastro":"2015-09-11",
      "ativo":"S",
      "titulo":"Ford Ka 2002",
      "galeria":[  
         {  
            "foto_id":7,
            "opcional_nome":"Foto 01"
         }
      ],
      "destaque":"S",
   }
]

Could someone please instruct me how to make this appointment? My table is "Gallery" and has the list of photos and each one is saved with a relationship id (in this case, that id that I can print in Json today).

Answer 1

Within WHILE(), for each car you need to make a new query in the gallery table and include these objects in the car. An example:

while($obj = mysqli_fetch_object($result)) {
    $sql_galeria = "SELECT * FROM galeria WHERE idcarro='". $obj->id ."'";
    $result_galeria = mysqli_query($con, $sql_galeria);
    while ($galeria = mysqli_fetch_object($result_galeria)) {
        $obj->galeria[] = $galeria;
    }
    $var[] = $obj;
}
echo '{"'.$tabelabd.'":'.json_encode($var).'}';

• Corrected from your comment.