INNER JOIN data mixed in php + json

Question

Good evening, so I’m trying to create a API, and I came across the following problem, I’m not able to group the data from my inner join, follows the code :

 <?php
    $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' ";
    $rc = ibase_query($con, $sql);
    while ($row = ibase_fetch_object($rc)) { 

        // print_r($row);
        // echo json_encode($row);

        $json['CODPRD'][] = $row -> CODPRD;
        $json['IDTICKET'][] = $row -> IDTICKET;
        $json['NOMEFANTASIA'][] = $row -> NOMEFANTASIA;
        $json['PRECO'][] = $row -> PRECO1;
        $json['IDCARTAO'][] = $row -> IDCARTAO;
        echo json_encode($json['CODPRD']);
        echo json_encode($json['IDTICKET']);
        echo json_encode($json['NOMEFANTASIA']);

        // 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);      

Then at the time I will see the data on the web appear as follows

["000415"][3]["000415","001607"][3,23]["000415","001607","001609"][3,23,513]["000415","001607","001609","001475"][3,23,513,558]["000415","001607","001609","001475","001328"][3,23,513,558,558]["000415","001607","001609","001475","001328","001223"][3,23,513,558,558,681]["000415","001607","001609","001475","001328","001223","000743"][3,23,513,558,558,681,714]["000415","001607","001609","001475","001328","001223","000743","000270"][3,23,513,558,558,681,714,714]["000415","001607","001609","001475","001328","001223","000743","000270","000333"][3,23,513,558,558,681,714,714,714]

Already in Firebird appears more organized:

So I’m not sure how to organize by the prevailing numbers, which is the IDCARTÃO and IDTICKET, there is an easier way to do this?

Answer 1

I suggest perfecting your code SQL:

SELECT tt.idticket, ttp.codprd, tp.nomefantasia, tp.preco1, tt.idcartao 
FROM TTICKET tt
INNER JOIN TTICKETPROD ttp
  ON ttp.idticket = tt.idticket
INNER JOIN TPRODUTO tp
  ON tp.codprd = ttp.codprd
WHERE tt.status = 'A'
ORDER BY tt.idticket ASC, ttp.codprd ASC

You can use nicknames for your table and use the ORDER BY to organize the order that is displayed your data, in this case I have sorted upwards ASC, but you can sort it down DESC.

You can also group the data, for this just inform the fields you want to group and add the others that will be part of the grouping (the results) through the GROUP BY:

SELECT tt.idticket, ttp.codprd, tp.nomefantasia, tt.idcartao 
SUM(tp.preco1) AS Total_precol
FROM TTICKET tt
INNER JOIN TTICKETPROD ttp
  ON ttp.idticket = tt.idticket
INNER JOIN TPRODUTO tp
  ON tp.codprd = ttp.codprd
WHERE tt.status = 'A'
GROUP BY tt.idticket, ttp.codprd, tp.nomefantasia, tt.idcartao 
ORDER BY tt.idticket ASC, ttp.codprd ASC

In this example I Agrupei (GROUP BY) the fields tt.idticket, ttp.codprd, tp.filename, tt.idcard and add up (SUM) the value field, note the GROUP BY at the end of the code.