Get json encode data from two tables based on foreign key

I have a question for you and I have not found a solution to this, So I have two tables which are sales_details and sales_payment. Sales details where you can see all the details and the other table is for payment info/transaction. Sales details have the primary key and the sales_payment table have the FK. To combine all the data I used the inner join statement. Thanks!

Here’s an example of my data. I run a sample query using join. All of the pictures are the result of the query. I just cropped it because I can’t take a long screenshot in landscape mode.

Sales Detail Table:

Sales Detail Table Continuation

Sales Payment Table

As you can see, all the data in tbl_sales_payment have returned and it seems redundant.

What I need is like this:

{
    sales_balance: "4601.60"
    sales_company: ""
    sales_cp: ""
    sales_date: "2021-01-12 01:26:33"
    sales_discount: "0.00"
    sales_dr: "5768"
    sales_height: "8.00"
    sales_id: "3"
    sales_media: "Sticker on Sintra"
    sales_net_amount: "8601.60"
    sales_particulars: "Authorized Personnel Only"
    sales_po: "100549"
    sales_price_unit: "4.00"
    sales_qty: "15.00"
    sales_si: "1794"
    sales_so: "1234"
    sales_total: "7680.00"
    sales_total_area: "128.00"
    sales_unit: "in"
    sales_vat: "921.60"
    sales_width: "16.00"
    "payments": [
      {
        payment_id: "3"
        payment_amount: "1000.00"
        payment_date: "2021-01-15"
        payment_remarks: ""
      },
      {
        payment_id: "4"
        payment_amount: "1000.00"
        payment_date: "2021-01-18"
        payment_remarks: ""
      },
      {
        payment_id: "5"
        payment_amount: "2000.00"
        payment_date: "2021-01-29"
        payment_remarks: ""
      }
    ]
}

This what I’ve tried:

public function get_payment_info_by_id($payment_info_id) {
    $return_data = [];
    $query = $this->db->query("SELECT * FROM tbl_sales_details AS tsd INNER JOIN tbl_sales_payments AS tsp ON tsp.sales_id = tsd.sales_id WHERE tsd.sales_id = $payment_info_id");
    
    while ($row = $query->fetch_object()) {
        $return_data[] = $row;
    }

    echo json_encode($return_data);
}

Answer

Similar code to your last question but now with the new result layout.

public function get_payment_info_by_id($payment_info_id) {
    $sql = "SELECT * 
            FROM tbl_sales_details AS tsd 
                INNER JOIN tbl_sales_payments AS tsp ON tsp.sales_id = tsd.sales_id 
            WHERE tsd.sales_id = ?";

    $stmt = $this->db->prepare($sql);
    $stmt->bind_param('i', $payment_info_id); 
    $stmt->execute();
    $result = $stmt->get_result();
    
    $last_salesid = NULL;
    $t = [];
    
    while($row = $result->fetch_assoc()) {
        if ( $last_salesid != $row['sales_id'] ) {  
            // get sales_details columns in this case     
            $t[] = [
                    "sales_id"          => $row['sales_id'],
                    "sales_date"        => $row['sales_date'],
                    "sales_po"          => $row['sales_po'],
                    "sales_so"          => $row['sales_so'],
                    "sales_dr"          => $row['sales_dr'],
                    "sales_si"          => $row['sales_si'],
                    "sales_company"     => $row['sales_company'],
                    "sales_cp"          => $row['sales_cp'],
                    "sales_particulars" => $row['sales_particulars'],
                    "sales_media"       => $row['sales_media'],
                    "sales_width"       => $row['sales_width'],
                    "sales_net_amount"  => $row['sales_net_amount'],
                    "sales_balance":    => $row['sales_balance']
                ];
            $last_salesid = $row['sales_id'];
        }
        if ( isset($row['payment_id']) ) {
            // then get the sales_payment info
            $t[count($t)-1]['payments'][] = 
                    [
                        'payment_id'        => $row['payment_id']
                        'payment_amount'    => $row['payment_amount',
                        'payment_date']     => $row['payment_date',
                        'payment_remarks']  => $row['payment_remarks'
                    ];
        } else {
            // make sire even if there are no payments yet
            // that there is always a payments array
            $t[count($t)-1]['payments'] = [];
        }
    }    
    header('Content-type: application/json');

    echo json_encode($t);
}