How to update Json data type in MySql?

I have column in my table that is Json data type. In this column I record Phones data like this:

{"0": "044-33565388", "1": "044-33565399", "2": "044-33565311"}

For store data like this form, I use textarea and enter each phone number in a new line. Next pass data to php document that parse data something like this:

$phoneList = json_encode( explode("\r\n", $input), JSON_FORCE_OBJECT)

And insert data in mysql. Now I want to update or remove some JSON data. I try something like this but got error:

Query:

UPDATE `sellers` SET `seller_phone` = JSON_SET(`seller_phone`, {"0":"33565388","1":"33565399"}) WHERE `seller_id` = 8

Error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"0":"33565388","1":"33565399","2":"33565311"}) WHERE seller_id = 8' at line 1

MySql version: 5.7.9

Now, What can I do to solve this problem?

Answers 1

  • I found a solution:

    1. $Query: 'UPDATE sellers SET seller_phone = "[]" WHERE seller_id = '.$seller_id;
    2. $Query: 'UPDATE sellers SET seller_phone = JSON_SET(seller_phone, '.$seller_phones.') WHERE seller_id = '.$seller_id;

    Note:

    function explode_lines($seller_phones_input) {
        return explode("\r\n", $seller_phones_input);
    }
    
    function array_to_query($seller_phones) {
    
        $query = null;
    
        for ($i = 0; $i < count($seller_phones); $i++){
    
            if ($i!=0){
                $query .= ", ";
            }
    
            $query .= '"$['.$i.']", "' . $seller_phones[$i] . '"';
        }
    
        return $query;
    }
    

Related Articles