CODEWITHSUNDEEP
phpmysqljson
robomano
robomano

Reputation: 51

Store json object in database using php

Quick question, I have a JSON data object for example { "name":"John", "age":30, "car":null } , is it possible to store this just as it is in a mysql field?, and in turn read it?

Upvotes: 0

Views: 1671

Answers (3)

Markus AO
Markus AO

Reputation: 4889

You can store basically whatever you want in a MySQL field, the only constraints are the datatype and maximum size of the field. Whether that makes sense is another matter, and depends on the your use case. Two cases that make sense are as follows:

If you simply need a place to store a serialized array/object somewhere, and you don't need to query the fields in the serialized data, you can store it into e.g. a varchar or a text field like any other string, and simply json_encode on the way in and json_decode when reading it.

On the other hand, e.g. for a use case where you have variable data structures associated with your main table entries, which you need to query, you can use the JSON datatype (available since MySQL 5.7.8). The JSON datatype only accepts valid JSON strings for input, and there are several useful functions for querying the contents.

Whatever you do, don't store JSON (or other serialized data) into a regular text field, and then try to query it with LIKE '%"field":"value"%' style hacks. They yield misery and eventually fail.

Upvotes: 4

Jesse Desjardins
Jesse Desjardins

Reputation: 77

This should be helpful: https://dev.mysql.com/doc/refman/8.0/en/json.html

MySQL does support JSON objects, so something a little like this:

mysql> CREATE TABLE t1 (jdoc JSON);
Query OK, 0 rows affected (0.20 sec)

mysql> INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT jdoc FROM t1;
+---------------------------------------------------------+
| jdoc                                                    |
+---------------------------------------------------------+
| {"key1": "value1", "key2": "value2"}                    |
+---------------------------------------------------------+

There is also a lot more functionality using JSON, so take a look at the documentation.

Upvotes: 1

Troaca Mihai
Troaca Mihai

Reputation: 56

Yes you should define the column properly (I usually define them as text). After that you can use json_decode() and that's it

Upvotes: 1

Related Questions