TransWikia.com

Multiple table to encode json and display

Stack Overflow Asked by J.Wujeck on February 3, 2021

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

One Answer

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

Answered by RiggsFolly on February 3, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP