CODEWITHSUNDEEP
mysqlarraysjson
Erick Boileau
Erick Boileau

Reputation: 494

Mysql JSON Multi-dimensional Array

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

Upvotes: 0

Views: 544

Answers (2)

Erick Boileau
Erick Boileau

Reputation: 494

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

Upvotes: 0

Chris Lear
Chris Lear

Reputation: 6742

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.

Upvotes: 1

Related Questions