Editing a string / json formatted MYSQL cell using Laravel

Question

I'm developing a site in which there is a reward chart table type thing (if you've never seen one, it's just a checklist for certain activities each day of the week to ensure your kids are doing their tasks, you can search one up for better visual).

I'm developing using Laravel 11 and MYSQL, and to store the table I was planning on using one of two methods, either storing it in a string like this:

    'tasks' => 'Brush Teeth, Make bed, Wash, Make table',
    'task_states_all' => 'Basketball,Flower_Blue,False,Plane,False,Plane,False;False,Flower_Pink,Mushroom,False,Basketball,Plane,Mushroom;Flower_Blue,False,False,Flower_Pink,False,Unicorn,False;False,Unicorn,Plane,False,Flower_Pink,Flower_Blue,Basketball',

and then using Laravel's blade PHP tools, seperate the task_states_all by semicolons, and then commas to get the specfic value for each cell, (Basketball, Flower_Blue, Plane etc are just name of the stickers which will appear in its place). Another option was using json like this:

{
    "tasks": [
        {
            "name": "Task 1",
            "schedule": {
                "Monday": true,
                "Tuesday": false,
                "Wednesday": true,
                "Thursday": false,
                "Friday": true,
                "Saturday": false,
                "Sunday": true
            }
        },
        {
            "name": "Task 2",
            "schedule": {
                "Monday": false,
                "Tuesday": true,
                "Wednesday": false,
                "Thursday": true,
                "Friday": false,
                "Saturday": true,
                "Sunday": false
            }
        },
        {
            "name": "Task 3",
            "schedule": {
                "Monday": true,
                "Tuesday": true,
                "Wednesday": true,
                "Thursday": true,
                "Friday": true,
                "Saturday": true,
                "Sunday": true
            }
        }
    ]
}

My issue comes in editing the string/json. Obviously, the table is editable by the user. When they choose a different sticker, or a different task name, it needs to be reflected in the string or json, but as they are stored in one string, I'm not sure on how to change the string in such a way that one word is altered whilst the rest remain the same, and so that the altered word is the correct one, so another sticker isnt change etc.

I could have made like 70 columns in my table, each with a name and state for that specific task and day of the week, but i felt that wouldnt be efficient. Anyone have any ideas? If you need any other code snippets, please let me know.

It's not clear how you would end up with 70 columns in a table? As you showed, you have tasks that has a title and a schedule, even if you save each day in its own column, why would you need 70 columns ? — N69S
– N69S, Commented Mar 25, 2024 at 13:20
@N69S the tasks i provided were just a shorter example. If I were to use a table, I would have to set a hard limit of around 10 tasks per chart, as there would be a hard limit on columns. Task Name + State for each day of the week = 7 columns per task, with 10 tasks leaving 70 columns, 71 if you have to include the users username to track whose column is whose. It was just an approximation though. There could be less or more, but I was more saying how it would be ineffective and was looking for an answer on how to edit the specific section in the string rather than the column haha. — turtles
– turtles, Commented Mar 25, 2024 at 13:24
why make 70 columns for 10 tasks ? it would be 10 entries in a 9 columns table (name, weekdays, user_id) !!! — N69S
– N69S, Commented Mar 25, 2024 at 18:32

Abdulrahman · Accepted Answer · 2024-03-25 19:09:05Z

0

use accessor to json_decode to convert to array then you can edit normal array then jsone_encode after that use save method ex:

$model = Model::find($id);
$arrayData = json_decode($model->json_column, true);
$arrayData['key'] = 'new value';
$model->json_column = json_encode($arrayData);
$model->save();

answered Mar 25, 2024 at 19:09

Abdulrahman

12 bronze badges

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

1 Comment

mickmackusa Over a year ago

stackoverflow.com/a/54342759/2943403 and stackoverflow.com/a/57842277/2943403 and stackoverflow.com/a/61060017/2943403

Collectives™ on Stack Overflow

Editing a string / json formatted MYSQL cell using Laravel

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related