How to join fields from a php array

Question

Good evening, so I’m facing a problem with an array, my idea is the following, create an array $MESA[IDMESA] and within that array I would have all information about a particular table that is identified with the variable $json[IDCARTAO] but my Inner Join returns the separate data as follows:

{
    "IDTICKET": 714,
    "NOMEFANTASIA": "ABOBORA",
    "PRECO": "3.5000",
    "IDCARTAO": 3537
}{
    "IDTICKET": 714,
    "NOMEFANTASIA": " ALCOOL EM GEL",
    "PRECO": "5.5900",
    "IDCARTAO": 3537
}{
    "IDTICKET": 714,
    "NOMEFANTASIA": "ABS.ENLACE GE.M.S.C\/AB.",
    "PRECO": "2.0000",
    "IDCARTAO": 3537
} 

My idea is by the common denominator that is the IDTICKET, that is, would be:

MESA[IDMESA]{
    "IDTICKET": 714,
    "NOMEFANTASIA": "ABOBORA",
    "PRECO": "2.0000",
    "NOMEFANTASIA": "ABS.ENLACE GE.M.S.C\/AB.",
    "PRECO": "3.5000",
    "NOMEFANTASIA": " ALCOOL EM GEL",
    "PRECO": "5.5900",
    "IDCARTAO": 3537
}

It would group all products in the same array, however I’m not getting any way to think about how to do, I thought instead of using an Inner Join I would use a cross Join, follow my code below

<?php

  header('Content-Type: application/json'); 
  $json = array();
  $db = 'localhost:C:\baseking\TGA.FDB';
  $username = 'SYSDBA';
  $password = 'masterkey';

  $con = ibase_connect($db, $username, $password);
  $sql = "SELECT TTICKET.idticket, TTICKETPROD.codprd, TPRODUTO.NOMEFANTASIA, TPRODUTO.preco1, TTICKET.idcartao from tticket
  inner join TTICKETPROD on (TTICKETPROD.idticket = TTICKET.IDTICKET)
  inner join TPRODUTO on (TTICKETPROD.codprd = tproduto.codprd)where TTICKET.status = 'A' 
  ORDER BY TTICKET.idticket ASC, TTICKETPROD.codprd ASC";
  $rc = ibase_query($con, $sql);
  while ($row = ibase_fetch_object($rc)) { 
      
     // print_r($row);
   // echo json_encode($row);
 /*   foreach ($row as $resultado=> $valorresultado) {
         $result['CODPRD'][ $resultado -> IDTICKET] = $valorresultado;
         echo json_encode($result, JSON_PRETTY_PRINT);
    }*/
   $result['IDTICKET'] = $row -> IDTICKET;
    $result['NOMEFANTASIA'] = $row -> NOMEFANTASIA;
    $result['PRECO'] = $row -> PRECO1;
    $result['IDCARTAO'] = $row -> IDCARTAO;
    $json['MESA'] = $result; 
    echo json_encode($result, JSON_PRETTY_PRINT);


     // echo "".$json['CODPRD'];
    }

  /*if($json['CODPRD']){
    $string = implode(",",$json['CODPRD']);
    $sql = "SELECT * FROM TPRODUTO where CODPRD = '{$string}'";
    $rc = ibase_query($con, $sql);
    while ($row = ibase_fetch_object($rc)) { 
      $json['NOMEFANTASIA'][] = $row -> NOMEFANTASIA;
      echo json_encode($json['NOMEFANTASIA']);

    }  */

  ibase_free_result($rc);
  ibase_close($con);
?>      

The initial idea that I had, would be to use a loop and an if, which would be as follows

for( i = $result['IDTICKET]; i < NUMEROS DE LINHAS QUE TEM; i++)
if($result['IDTICKET'][i] == $result['IDTICKET'][i]){

  //AQUI NESSA CONDIÇÃO ELE PEGARIA TODOS DADOS DE LINHAS QUE TEM O IDTICKET
}

However I still do not know the syntax of php in the background and I do not know how to do this within the language, since I am very grateful.

@EDIT So it worked partially, to get the data directly from select

$foo = [  $json['MESA']];
$mesas = [];
foreach ($foo as $value) {
  $mesas[$value['IDTICKET']][] = $value;
}

however the way it shows the data in the query was not so structured

 (
    [714] => Array
        (
            [0] => Array
                (
                    [IDTICKET] => 714
                    [NOMEFANTASIA] =>  ALCOOL EM GEL
                    [PRECO] => 5.5900
                    [IDCARTAO] => 3537
                )

        )

)
{
    "IDTICKET": 714,
    "NOMEFANTASIA": "ABS.ENLACE GE.M.S.C\/AB.",
    "PRECO": "2.0000",
    "IDCARTAO": 3537

Answer 1

Your idea is almost right, you don’t need to make one if just create a new array to group by IDTICKET passing as an index of array, so the data with the same id will be grouped.

$foo = [
    [
        'IDTICKET' => 714,
        'NOMEFANTASIA' => 'ABOBORA',
        'PRECO' => '3.5000',
        'IDCARTAO' => 3537
    ],
    [
        'IDTICKET' => 714,
        'NOMEFANTASIA' => 'ABOBORA',
        'PRECO' => '3.5000',
        'IDCARTAO' => 3537
    ],
    [
        'IDTICKET' => 714,
        'NOMEFANTASIA' => 'ABOBORA',
        'PRECO' => '3.5000',
        'IDCARTAO' => 3537
    ],
    [
        'IDTICKET' => 714,
        'NOMEFANTASIA' => ' ALCOOL EM GEL',
        'PRECO' => '5.5900',
        'IDCARTAO' => 3537
    ],
    [
        'IDTICKET' => 714,
        'NOMEFANTASIA' => 'ABS.ENLACE GE.M.S.C\/AB.',
        'PRECO' => '2.0000',
        'IDCARTAO' => 3537
    ]
];

$mesas = [];
foreach ($foo as $value) {
    $mesas[$value['IDTICKET']][] = $value;
}

// Print do resultado
echo '<pre>';
print_r($mesas);
die;

// Resultado:
Array
(
    [714] => Array
        (
            [0] => Array
                (
                    [IDTICKET] => 714
                    [NOMEFANTASIA] => ABOBORA
                    [PRECO] => 3.5000
                    [IDCARTAO] => 3537
                )

            [1] => Array
                (
                    [IDTICKET] => 714
                    [NOMEFANTASIA] => ABOBORA
                    [PRECO] => 3.5000
                    [IDCARTAO] => 3537
                )

            [2] => Array
                (
                    [IDTICKET] => 714
                    [NOMEFANTASIA] => ABOBORA
                    [PRECO] => 3.5000
                    [IDCARTAO] => 3537
                )

            [3] => Array
                (
                    [IDTICKET] => 714
                    [NOMEFANTASIA] =>  ALCOOL EM GEL
                    [PRECO] => 5.5900
                    [IDCARTAO] => 3537
                )

            [4] => Array
                (
                    [IDTICKET] => 714
                    [NOMEFANTASIA] => ABS.ENLACE GE.M.S.C\/AB.
                    [PRECO] => 2.0000
                    [IDCARTAO] => 3537
                )

        )

)