Mysql JSON Multi-dimensional Array

Question

which form should have json code in mysql field ? I need users datas (user_id is the key) with 3 values (3 informations : name , age, sex)

145:"name,age,sex",
148:"name,age,sex",
200:"name,age,sex"

I am using mysql version 5.6 and the datas will be inserted with SQL code

is it correct to store it in that way in mysql to retrieve with php and json_decode?

[{236:"paul,26,1"},{2515:"fred,42,1"},{2515:"jane,21,0"}]

thank you

Chris Lear · Accepted Answer · 2016-10-19 10:50:02Z

1

Do you ever want to be able to write a query like select * from users where sex=1? If so, don't store the JSON as text. Store each value (id, name, age, sex) in a column of its own.

Even if you do want to store the JSON as a string, it would probably be better organised like this:

[
{"id":236,"name":"paul","age":26,"sex":1},
{"id":2515,"name":"fred","age":42,"sex":1},
{"id":2516:"jane","age":21,"sex":0}
]

You would need to manipulate it a bit after querying, but you would have more meaningful data.

But if all you want is to store that text, so you can retrieve it as text later, then what you have is fine.

answered Oct 19, 2016 at 10:50

Chris Lear

6,7601 gold badge20 silver badges29 bronze badges

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

3 Comments

Erick Boileau Over a year ago

I need json in that case . I can split the text , but i'll try your method anyway . Thanks !

Nabster Over a year ago

I was just writing a similar answer @ChrisLear! @ErickBoileau I'll add to this good answer, it's good practice to ensure that you have a class that this JSON can be de-serialised into server side. This means that you can deal with the data in a consistent way before you persist. E.g. you can validate the data is good. Then you would typically have some form of a repository class that would manage persistence and further more retrieval ready for that class to eventually be serialised into a clean JSON object ready for transmission back to your client.

Erick Boileau Over a year ago

Thanks for your answer , do you have a more precise example of that class ? or how it should be ? I am using it with Jooma 3.6

Erick Boileau · Accepted Answer · 2016-10-19 12:12:42Z

0

with datas in this form

[{236:"paul,26,1"},{2515:"fred,42,1"},{2515:"jane,21,0"}]

and json_decode($myfield); I get NULL

if I echo $myfield I get exatly what is stored in the database

what is wrong with N

[{236:"paul,26,1"},{2515:"fred,42,1"},{2515:"jane,21,0"}]

If I parse it here http://json.parser.online.fr/ I get Syntax error

edited Oct 19, 2016 at 12:12

answered Oct 19, 2016 at 12:06

Erick Boileau

4946 silver badges16 bronze badges

Collectives™ on Stack Overflow

Mysql JSON Multi-dimensional Array

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related