php condition to filter results from json data

Question

I am trying to get grand_total result from a database where payment_status = paid please have a look below my database structure and code it's not returning paid status payment results returning all results

Here is database structure and data

   ref_id |            payment_status          |    grand_total 
     19   |     [{"admin":"","status":"due"}]  |        100
     19   |     [{"admin":"","status":"paid"}] |        50
     19   |     [{"admin":"","status":"paid"}] |        500

Here is my function so far

function total_referral_purchase($referral_id)
{
    $return = 0;
    $sales  = $this->db->get('sale')->result_array();
    foreach ($sales as $row) {
         $payment_status = json_decode($row['payment_status'],true);
         $status = $payment_status['status'];

        if ($row['ref_id'] == $referral_id && $status == 'paid' ) {
            $return += $row['grand_total'];
        }
    }
    return $this->cart->format_number($return);
}

Result I am getting = 0

Expected result = 550

Edit :

var_dump array(1) { 
    [0]=> array(20) { 
        ["sale_id"]=> string(3) "202" 
        ["sale_code"]=> string(9) "201906202" 
        ["buyer"]=> string(1) "1" 
        ["guest_id"]=> NULL 
        ["ref_id"]=> string(2) "19" 
        ["commission"]=> string(4) "8.83" 
        ["grand_total"]=> string(5) "83.63"
        ["payment_status"]=> string(30) "[{"admin":"","status":"paid"}]" 
        ["payment_details"]=> string(4) "none" 
        ["payment_timestamp"]=> NULL 
        ["sale_datetime"]=> string(10) "1559492618" 
        ["delivary_datetime"]=> string(0) "" 
        ["delivery_status"]=> string(65) "[{"admin":"","status":"pending","comment":"","delivery_time":""}]" 
        ["viewed"]=> string(2) "ok" 
    }
}

Nothing looks wrong so far to me, can you var_dump($sales); ? and descride what is the format_number function ? — Frankich
– Frankich, Commented Jun 3, 2019 at 8:39
Can you please debug each line of "total_referral_purchase($referral_id)" function? Verify that "if condition" fulfills your requirement. — Dhara Bhatti
– Dhara Bhatti, Commented Jun 3, 2019 at 8:41
You can also use JSON functions on mysql to avoid the extra code for that it's different from what u asked but might also help you :) dev.mysql.com/doc/refman/8.0/en/json-search-functions.html — Did
– Did, Commented Jun 3, 2019 at 8:42
If you add some var_dump() in your function, do you get what you need everywhere ? One for the $referral_id for example, one for $sales and in the foreach loop check $payment_status, $status and your $return before and after the if, check the $row['ref_id'] too maybe. — Mickaël Leger
– Mickaël Leger, Commented Jun 3, 2019 at 8:42
@Frankich => i am getting this result when i did var_dump array(1) { [0]=> array(20) { ["sale_id"]=> string(3) "202" ["sale_code"]=> string(9) "201906202" ["buyer"]=> string(1) "1" ["guest_id"]=> NULL ["ref_id"]=> string(2) "19" ["commission"]=> string(4) "8.83" ["grand_total"]=> string(5) "83.63" — Troy Roy
– Troy Roy, Commented Jun 3, 2019 at 9:23

Frankich · Accepted Answer · 2019-06-03 09:45:53Z

3

The json_decode function return an array based on your json format. So the result of your current code

json_decode($row['payment_status'],true);

return a array with this format

array(1) {
[0]=>
  array(2) {
    ["admin"]=>
    string(0) ""
    ["status"]=>
    string(4) "paid"
  }
}

because of the '[]' around the json which means it would have multiple record and it parses it as an array. So the $status is always null and should be either be replace by

$status = $payment_status[0]['status'];

Or change the format from the database like '{"admin":"","status":"paid"}' to work in your current way.

edited Jun 3, 2019 at 9:45

answered Jun 3, 2019 at 9:41

Frankich

8529 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim · Accepted Answer · 2020-04-06 07:59:09Z

3

The JSON that you are using is an array, as indicated by the square brackets.

There are two ways to fix this:

You change the JSON from [{"admin":"","status":"due"}] to {"admin":"","status":"due"} in your database. This makes it a single JSON object instead of an array.
Instead of $status = $payment_status['status']; use $status = $payment_status[0]['status']; This selects the value of 'status' from the first object in the array.

edited Apr 6, 2020 at 7:59

answered Jun 3, 2019 at 9:45

Tim

1392 gold badges3 silver badges15 bronze badges

Collectives™ on Stack Overflow

php condition to filter results from json data

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related