Saving JSON into MySQL using PHP overwrites rather than adds a new sub array-CodePudding

I'm trying to save a json array into a MySQL field using PHP. The idea is that when someone submits a new comment, the comment is added into a json array of all comments and saved into the database as a multidimensional array. I've searched StackOverflow - but when any new comment submitted it just overwrites the existing array - rather than adding to it. And I can't see what I'm doing wrong. Here is my code:

The form has a hidden field, where I echo the json from the database:

<input type="hidden" name="json" value=" <?php echo json_encode($item->issue_comments);?> "/>

Then, when the form submits using POST, I have the following code to decode and add the new sub array (date/name/comment) into the main array and reencode.

$comments = json_decode($_POST['json'], true);
$comments['comment'][] = array('date'=>$comment_date, 'name'=>$issue_commenter, 'comment'=>$_POST['issue_comment']);
$json = json_encode($comments);

This is what is saved in the database:

{"comment": [{"date": "2022-02-12 18:02:02", "name": 1, "comment": "comment text"}]}

Thanks for any help

CodePudding user response：

Your application design requires a transactional read - modify - write cycle to the database column containing the JSON. If you will enlarge the JSON array in a column of the table, you'll need something like this.

START TRANSACTION;
SELECT json_column FROM tbl WHERE id=? FOR UPDATE;

Then you'll modify the JSON data in your php app. Then you'll do, to the database:

UPDATE tbl SET json_column = ? WHERE id=?;
COMMIT;

If you use the design where you stash the JSON object to be modified in browser state (in your hidden field) and two users of your app update the same ticket concurrently, one of the comments, at least, will get lost.

Bill K's suggestion of a separate comments table gets you out of this possible problem. You can do INSERTs concurrently with no risk of data loss.

CodePudding user response：

If you want to store data as JSON in MySQL, you should read about MySQL's JSON data type and builtin JSON functions that you can use to manipulate JSON documents.

Here's a demo of appending an object to an array:

I create a test table to use:

mysql> create table mytable (id serial primary key, comment_thread json);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into mytable set comment_thread = '{"comment": [{"date": "2022-02-12 18:02:02", "name": 1, "comment": "comment text"}]}';
Query OK, 1 row affected (0.01 sec)

Now I can show that the JSON is in the table:

mysql> select json_pretty(comment_thread) from mytable;
 -------------------------------------------------------------------------------------------------------------------------- 
| json_pretty(comment_thread)                                                                                              |
 -------------------------------------------------------------------------------------------------------------------------- 
| {
  "comment": [
    {
      "date": "2022-02-12 18:02:02",
      "name": 1,
      "comment": "comment text"
    }
  ]
} |
 -------------------------------------------------------------------------------------------------------------------------- 
1 row in set (0.00 sec)

One can use the builtin JSON_ARRAY_APPEND() function to add a new element to an array. The element I append is itself a JSON object, which I create with the JSON_OBJECT() function from name/value pairs.

mysql> select json_pretty(json_array_append(comment_thread, '$.comment',
 json_object('date', now(), 'name', 2, 'comment', 'new comment'))) as newdoc from mytable;
 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
| newdoc                                                                                                                                                                                                                            |
 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
| {
  "comment": [
    {
      "date": "2022-02-12 18:02:02",
      "name": 1,
      "comment": "comment text"
    },
    {
      "date": "2022-02-12 11:19:20.000000",
      "name": 2,
      "comment": "new comment"
    }
  ]
} |
 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
1 row in set (0.00 sec)

That demonstrates using SELECT so we can see the result. When you're satisfied that you have the expression coded to produce the result you want, use that expression in an UPDATE statement:

mysql> update mytable set comment_thread = json_array_append(
    comment_thread, 
    '$.comment', 
    json_object('date', now(), 'name', 2, 'comment', 'new comment')) 
  where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

If that seems like a lot of complexity, then I'd suggest using normal rows and columns instead of JSON. That is, store comments one per row, and use a separate column for the date, name and comment text. Then adding a new comment is a lot more conventional and straightforward:

INSERT INTO comments (date, name, comment) VALUES (NOW(), 2, 'new comment');