How can I update json data in MySQL with Laravel framework?

Question

I want to create a ticketing system with Laravel. I store the chat data's in json data type. when user want to send her/his ticket i get her/his data and store its with these code:

$messages = array([
    'message_title' => post('message_title'),
    'message_body' => post('message_body'),
    'type' => 'q',
    'created_at' => date('Y/m/d H:i:s'),
]);

$messagesToJson = json_encode($messages);
$query = DB::table('test')->insert([
    'level_id' => post('message_level'),
    'status' => 0,
    'ticket_id' => post('user_id') . time(),
    'user_id' => post('user_id'),
    'backend_user_id' => '',
    'messages' => $messagesToJson,
]);

these data store in MySQL:

[
    {"message_title":"This is title","message_body":"Some text ... .","type":"q","created_at":"2020\/02\/29 20:42:26"}
]

But my problem is: when the user want ask a new question in this ticket how can i insert a new object in this array like this:

[
    {"message_title":"This is title","message_body":"Some text ... .","type":"q","created_at":"2020\/02\/29 20:42:26"},
    {"message_title":"Question 2","message_body":"Some text in question 2 ... .","type":"q","created_at":"2020\/02\/29 21:42:26"}
]

My update code is:

$messages = array(
    'message_body' => post('sendText'),
    'type' => 'q',
    'created_at' => date('Y/m/d H:i:s'),
);

$messagesToJson = json_encode($messages);
$updateQuery = DB::update("UPDATE synon_subsystem_ticketing
                  SET messages = JSON_SET(messages, '$[$countMessages]', '$messagesToJson') WHERE id = '$id'");

please post your current update function.

Hafez Divandari
– Hafez Divandari

2020-02-29 17:40:51 +00:00
Commented Feb 29, 2020 at 17:40 — Hafez Divandari
– Hafez Divandari, Commented Feb 29, 2020 at 17:40
Add update code in my question.

Farzin Bidokhti
– Farzin Bidokhti

2020-02-29 17:44:54 +00:00
Commented Feb 29, 2020 at 17:44 — Farzin Bidokhti
– Farzin Bidokhti, Commented Feb 29, 2020 at 17:44

Hafez Divandari · Accepted Answer · 2020-02-29 18:42:25Z

3

You have 2 options:

Get current value, append your message and update:

$tiket = DB::table('synon_subsystem_ticketing')->find($id);

$messages = json_decode($ticket->messages) ?? []; //json_decode is not necessary if you're using attribute casting on your model
$messages[] = array(
    'message_body' => post('sendText'),
    'type' => 'q',
    'created_at' => date('Y/m/d H:i:s'),
);
$messages = json_encode($messages); //json_encode is not necessary if you're using attribute casting on your model

DB::table('synon_subsystem_ticketing')
    ->where('id', $id)
    ->update(['messages' => $messages]);

Note: Check Laravel docs on json column on migrations and updating json column

Use MySql JSON_ARRAY_APPEND function on a raw query.

edited Feb 29, 2020 at 18:42

answered Feb 29, 2020 at 17:51

Hafez Divandari

9,1475 gold badges51 silver badges66 bronze badges

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

2 Comments

Farzin Bidokhti Over a year ago

Dear Hafez, i used the 1 way, but i get this error: [] operator not supported for strings, this error point to $messages[] = ...

Hafez Divandari Over a year ago

@FarzinBidokhti seem you need to json_decode your messages, check my updated answer.

Afraz Ahmad · Accepted Answer · 2020-02-29 17:50:51Z

1

MySQL supports json columns. You can create and update json columns in laravel as shown below.

$table->json('tickets_user_info'); // in your migration create json column.

//Update it as shown below.
$affected = DB::table('users')
          ->where('id', 1)
          ->update(['tickets_user_info' => 'what_ever_you_want_to_save_here']);

You can create new array including old data and update your json column with all data.

Read laravel's Query Json Columns and Updating JSON Columns also

answered Feb 29, 2020 at 17:50

Afraz Ahmad

5,41633 silver badges40 bronze badges

Collectives™ on Stack Overflow

How can I update json data in MySQL with Laravel framework?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related