From database via join to REST how does it work?

I have a database with two tables (orders & orderProducts) with 1:n-Relation. I would like to combine this two tables to give a JSON to my REST-Tool.

For this I have the following SQL-Query:

 $sqlQueryAll = "SELECT
                    t1.id,
                    t1.name,

                    t2.pId,
                    t2.anzahl,
                    t2.name as pName,
                    t2.preis,

                    FROM orders as t1
                  JOIN orderProducts as t2 ON t1.id = t2.orderId
                  WHERE t1.id <= ?";

Here I will get this Array back:

Array
(
    [0] => Array(
            [id] => 1000
            [name] => Nurten
            [pId] => 26
            [anzahl] => 1
            [pName] => Fitness-Teller
            [preis] => 12.90
        )
    [1] => Array(
            [id] => 1001
            [name] => Kutscha
            [pId] => 94
            [anzahl] => 1
            [pName] => Pizza Parma
            [preis] => 12.90
        )
    [2] => Array(
            [id] => 1001
            [name] => Kutscha
            [pId] => 75
            [anzahl] => 1
            [pName] => Pizza Margherita
            [preis] => 6.50
        )
)

I would like to have the following JSON:

Array (
    [0] => Array (
        [id] => 1000
        [name] => Nurten
        [products] => Array(
                [0] => Array(
                        [pId] => 26
                        [anzahl] => 1
                        [pName] => Fitness-Teller
                        [preis] => 12.90
                    )
           )
    )
    [1] => Array (
        [id] => 1001
        [name] => Kutscha
        [products] => Array(
                [0] => Array(
                        [pId] => 94
                        [anzahl] => 1
                        [pName] => Pizza Parma
                        [preis] => 12.90
                    )
                [1] => Array(
                        [pId] => 75
                        [anzahl] => 1
                        [pName] => Pizza Margherita
                        [preis] => 6.50
                    )
            )

    )
)

For this I’am using the following working PHP code:

$artikelFields = ['pId', 'anzahl', 'pName', 'preis'];
$id_="";  #new Product Kenner
$orders = [];
$i = -1;
foreach ($result2 as $row)  {

    if ($row['id'] != $id_) {

          # reset
          $i++;
          unset($order);
          $id_ = $row['id'];

          # write Array from Fields without $artikelFields
          foreach ($row as $field =>$value)  {
            if (!in_array($field, $artikelFields)) {
              $order[$field] = $value;
            }
          }

          # write Artikel to array[products]
          $newArtikel = array();
          foreach ($artikelFields as $a)  {
              $newArtikel[$a] = $row[$a];
          }
          $order['products'][] = $newArtikel;

    } else {

          # write only array[products]
          $newArtikel = array();
          foreach ($artikelFields as $a)  {
              $newArtikel[$a] = $row[$a];
          }
          $order['products'][] = $newArtikel;

    }

    $orders[$i] = $order;


} /* !foreach */

I’am a bit unsecure why this seems to be very complicated. Isn’t there any more convienience way to achive this? (With an other query maybe).

Answer

An easy way to group data is to use a “shared” key (here, the user id), initialize the row the first time you meet the user id, then, add the products to the ‘product’ key.

$out = [];
foreach ($results as $result)
{
    if (!isset($out[$result['id']])) {
       $out[$result['id']] = [
          'id'   => $result['id'],
          'name' => $result['name'],
       ];
    }

    $out[$result['id']]['products'][] = [
          'pId'    => $result['pId'],
          'anzahl' => $result['anzahl'],
          'pName'  => $result['pName'],
          'preis'  => $result['preis'],
    ];
}
var_export(array_values($out));

demo

You can also use array_filter :

$out = [];
foreach ($results as $result)
{
    if (!isset($out[$result['id']])) {
       $out[$result['id']] = array_filter($result, function($key) { 
        return in_array($key, ['id','name']); 
    }, ARRAY_FILTER_USE_KEY);
    }

    $out[$result['id']]['products'][] = array_filter($result, function($key) { 
        return in_array($key, ['pId','anzahl','pName','preis']); 
    }, ARRAY_FILTER_USE_KEY);
}
print_r(array_values($out));

demo