php - Multiple table to encode json and display

Question

Can someone help me to return JSON data with join tables? I have two tables which are sales_details and sales_payment. I want to return the data like this:

{
    "sales_id":"3",
    "sales_date":"2021-01-11 23:41:58",
    "sales_po":"100549",
    "sales_so":"1234",
    "sales_dr":"5768",
    "sales_si":"1794",
    "sales_company":"",
    "sales_cp":"",
    "sales_particulars":"Authorized Personnel Only",
    "sales_media":"Sticker on Sintra",
    "sales_width":"16.00",
    "sales_net_amount":"8601.60",
    "sales_balance":"6601.60",
},
{
    "payment_amount":"1000.00",
    "payment_date":"2021-01-15",
    "payment_remarks":""
},
{
    "payment_amount":"1000.00",
    "payment_date":"2021-01-18",
    "payment_remarks":""
}

This what I've tried:

public function get_payment_info_by_id($payment_info_id) {
    $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");
    
    $jsonArray = array();
    foreach($query as $row) {
        $jsonArrayItem = array();
        $jsonArrayItem['payment_amount'] = $row['payment_amount'];
        $jsonArrayItem['payment_date'] = $row['payment_date'];
        $jsonArrayItem['payment_remarks'] = $row['payment_remarks'];
        array_push($jsonArray, $jsonArrayItem);
    }    
    header('Content-type: application/json');

    echo json_encode($jsonArray);
}

Answer 1

You can use the joined query but you must look at the result you get back and work out which parts are what you need in what part of the output

I am assuming you are using PDO and have converted the query to use perpared bound parameters.

Update Ahh I see you are using MYSQLI_ and not PDO, so I have changed the database access code. That will probably fix the undefined index errors

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'];
        }
        // then get the sales_payment info
        $t[] = [
                'payment_amount' => $row['payment_amount',
                'payment_date'] => $row['payment_date',
                'payment_remarks'] => $row['payment_remarks'
                ];
    }    
    header('Content-type: application/json');

    echo json_encode($t);
}