Mysql Update, replace and concatenate

Question

I have a php array like the one below.

$arr = array(
                array(
                    "date"=>date('Y-m-d'),
                    "message"=>"test message 1",
                    "from_id"=>21,
                    "to_id"=>14
                ),
                array(
                    "date"=>date('Y-m-d'),
                    "message"=>"test message 2",
                    "from_id"=>23,
                    "to_id"=>12
                )               
            );

I do a json_encode on this array to convert it json.

$jsonarr =  json_encode($arr);

Output of $jsonarr:

[
 {"date":"2012-11-22","message":"test message 1","from_id":21,"to_id":14},
 {"date":"2012-11-22","message":"test message 2","from_id":23,"to_id":12}
]

After that i insert this in mysql table. Everything is fine till here.

Now i have to update this with another json by concatenation.

Another JSON:

[
 {"date":"2012-11-22","message":"test message 3","from_id":28,"to_id":2},
 {"date":"2012-11-22","message":"test message 4","from_id":53,"to_id":72}
]

And i want the field in my mysql table to display like this:

[
 {"date":"2012-11-22","message":"test message 1","from_id":21,"to_id":14},
 {"date":"2012-11-22","message":"test message 2","from_id":23,"to_id":12},
 {"date":"2012-11-22","message":"test message 3","from_id":28,"to_id":2},
 {"date":"2012-11-22","message":"test message 4","from_id":53,"to_id":72}
]

How can i write an UPDATE query for this.

I can do this by using 2 queries. One select query then formatting the field and later updating. But can this be achieved with one query ?

it couldnt be this simple could it? update test1 SET sample=CONCAT(sample,' some extra stuff') where id=1 — RiquezJP
– RiquezJP, Commented Nov 22, 2012 at 6:38
The brackets will get messed up..that was my initial thought on the query.. — Abhishek Saha
– Abhishek Saha, Commented Nov 22, 2012 at 6:41
oh yes, i see. the [ ] part. maybe just omit [] & add them when you read the data back out. or do a mysql substring on the field in your CONCAT — RiquezJP
– RiquezJP, Commented Nov 22, 2012 at 6:47
update table1 SET fieldname=CONCAT("[",SUBSTRING(fieldname,2,LENGTH(fieldname)-2),'some extra stuff',']') where id=1 — RiquezJP
– RiquezJP, Commented Nov 22, 2012 at 6:52
Thanks @RiquezJP for your solution. This will be really helpful in one of my other table where i have few configuration fields in json. I decided to follow the below solution for my current scenario. — Abhishek Saha
– Abhishek Saha, Commented Nov 22, 2012 at 7:12

Botond Balázs · Accepted Answer · 2012-11-22 06:36:29Z

1

The solution to your immediate problem is this:

MySQL does not natively support JSON values. You need to fetch the value from the database:

SELECT FOR UPDATE my_json_field
FROM my_table
WHERE id = 555;

Then modify it in the PHP script:

$my_array = json_decode($value_of_my_json_field);
$my_array = array_merge($my_array, $arr);
$value_of_my_json_field = json_encode($my_array);

Then update it in the DB:

UPDATE my_table
SET my_json_field = :value_of_my_json_field
WHERE id = 555;

Make sure this happens in a transaction. Also, you need to use the InnoDB storage engine.

But the more interesting question is why do you need to store JSON in the DB? Why not use a separate table:

Table: messages
- id integer
- date timestamp
- from_id integer (this should be a foreign key)
- to_id integer (this should be a foreign key)

Then you could simply insert into that table. That's the correct way of doing this. If you don't have a very good reason to use JSON, I would suggest getting rid of it.

answered Nov 22, 2012 at 6:36

Botond Balázs

2,5001 gold badge25 silver badges35 bronze badges

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

6 Comments

Abhishek Saha Over a year ago

Thanks for your reply. I understand what you mean and i had the same thought earlier. But the problem is there can be 1000 users. so if i am chatting with one user for 1 hour and lets say we exchanged 100 messages, the table will get filled with 100 rows. If 1000 users are chatting with each other, there will be a huge number of rows. So i want to keep the entire conversation between 2 users on one row, one field

Botond Balázs Over a year ago

Don't worry, the database can easily handle a huge number of rows in that table. It is highly optimized to do exactly that kind of task. Make sure to use proper indexing though (read this excellent website: use-the-index-luke.com).

Botond Balázs Over a year ago

If you insist on keeping a conversation in one place, look into MongoDB (mongodb.org) or Redis (redis.io). Both are high-performance NoSQL databases - though using them involves certain trade-offs that should be carefully considered.

Abhishek Saha Over a year ago

Great then !! I was thinking if that kind of approach would hamper the performance. I am also thinking to run a cron job and delete messages older than 1 week..I read about MongoDB but nah, dont want to get into them now. Thanks a lot for your suggestion.

Botond Balázs Over a year ago

You're welcome :) The cron job is a good idea. Alternatively, you can archive the old messages in a separate table instead of deleting them, and that can be a JSON field.

|

Collectives™ on Stack Overflow

Mysql Update, replace and concatenate

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related