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

user_7577316 0
Viewed 3912

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'");
laravel mysql php json database
2 Answers

You have 2 options:

  1. 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

  2. Use MySql JSON_ARRAY_APPEND function on a raw query.

user_34770840

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

user_72357740
Related