Paranoid Paranoid - 2 years ago 61
SQL Question

Putting JSON string as field data on MySQL

I have this idea where I make a multi/non-multi dimensional array into a JSON string using PHP's

and store the data into my SQL database.

For example, I have a table called 'users'. Table 'users' have 3 fields: id, name, data

Using php, I would like to fetch user John's data:
SELECT data FROM users WHERE name='john'

Now the value/text for 'data' field will be like this:

I will decode the 'data' field using PHP's
and then I will convert the stdClass object into an array using one of my self-made PHP functions. And then I can show John's information wherever I want like this:

This saves me from the hazel to create extra fields on database for country, city, birthday, etc. But is it safe? Is there any disadvantages or problem with using this method to store data on MySQL.

Answer Source

Proper escaping and you are fine but I must add that this is where XML is the better format than json since it will also allow you to do also use the data inside the xml in your queries

<?xml version="1.0" encoding="UTF-8" ?>


SELECT ExtractValue(data, '//gender') AS gender FROM users WHERE name='john' AND EXTRACTVALUE(data, '//country') != 'UK';

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download