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

It seems as though you're fetching joined data, but later want to manually separate it? — El_Vanja, Jan 11 '21 at 16:33
Run the query in phpMyAdmin or similiar, look at the output, see how you are going to have to process the result rows slightly differently for each row in order to get that output — RiggsFolly, Jan 11 '21 at 16:35
I run the query and the table look like this: https://ibb.co/cwDpTH7, https://ibb.co/KXSHcXR, https://ibb.co/chGWRh1 — J.Wujeck, Jan 11 '21 at 16:43
Seems like two queries (one for sales details and one for payment details) would make it much easier to achieve the result you wish. — El_Vanja, Jan 11 '21 at 16:45
Also, please not that directly injecting parameters into your query leaves you open to SQL injection. Consider switching to [prepared statements](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) to amend this issue. — El_Vanja, Jan 11 '21 at 16:46

RiggsFolly · Accepted Answer · 2021-01-12T11:10:17.583

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

Why I'm getting these errors: https://ibb.co/jgt6B7H Can you check it? Thanks! — J.Wujeck, Jan 12 '21 at 05:57
Hello! Is it possible to make my return json data like this: https://stackoverflow.com/questions/65680002/get-json-encode-data-from-two-tables-based-on-foreign-key — J.Wujeck, Jan 12 '21 at 09:52
I got an error: Undefined indexes: sales_id, payment_amount, payment_date, and payment_remarks — J.Wujeck, Jan 12 '21 at 10:35
Those are the names of your table columns. If they are not correct, fix them. I dont know what they are if theya re not what you put in your question — RiggsFolly, Jan 12 '21 at 10:43
Can you also show your connection code so I can put some error checking in, thanks — RiggsFolly, Jan 12 '21 at 10:51
Fix for the undefined errors, you might have told me you were not using PDO — RiggsFolly, Jan 12 '21 at 11:10
Wait. I'll try it now. Does it similar output to this one? https://stackoverflow.com/questions/65680002/get-json-encode-data-from-two-tables-based-on-foreign-key — J.Wujeck, Jan 12 '21 at 12:16
No that is not what this question asks for. Once this question is sorted iI will gladly look at the other one — RiggsFolly, Jan 12 '21 at 12:17

Multiple table to encode json and display

1 Answers1