Update One JSON Field in Database - CodeIgniter

Question

I have a JSON field called 'spec' and there are about 10 other items in this in JSON format. I need to only update the quantity.

Although when I try this method, it deletes everything else in it and just sets spec = quantity.

Heres what I have so far.

$pass_coupon_id = $this->pass_coupon_id();

$coupon_array = $this->db->query("SELECT * FROM coupon WHERE coupon_id='$pass_coupon_id'")->result_array();

        foreach ($coupon_array as $row) {
            $spec = json_decode($row['spec'], true);
          }

          $quantity_new = $spec['quantity'] - 1;
          $data2 = array(
            'spec' => json_encode(array(
                            'quantity'=> $quantity_new
                          )));

        $this->db->where('coupon_id', $pass_coupon_id);
        $this->db->update('coupon', $data2);

You are not writing it back into the array, you are basically overwriting it. — Jesse Schokker, Apr 08 '17 at 17:05

score 1 · Answer 1 · edited May 23 '17 at 11:54

You need to overrite only this one field and update whole field in query.

<?php
$pass_coupon_id = $this->pass_coupon_id();
$coupon_array = $this->db->query("SELECT * FROM coupon WHERE coupon_id='$pass_coupon_id'")->result_array();
// i don't know what you're using, but using foreach to extract single row isn't good solution. Look for sth like result_row() maybe.

$coupon           = $coupon_array[0];
$spec             = json_decode($coupon, true);
$new_quantity     = $spec['quantity'] - 1;
$spec['quantity'] = $new_quantity;
$new_spec         = json_encode($spec);

$this->db->where('coupon_id', $pass_coupon_id);
$this->db->update('coupon', $new_spec);

Depending on the database, the best solution would be using specific function to ommit updating whole structure - https://stackoverflow.com/a/34987329/2926214

Update One JSON Field in Database - CodeIgniter

1 Answers1