CODEWITHSUNDEEP
phpmysqlsqlarrays
meowmeowcastle
meowmeowcastle

Reputation: 13

Dealing with data stored as JSON objects in a MySQL

I'm using opencart 2.1 and one of the field on the customer table is an text array. The data field (named custom, type text) look like this: {"2":"2015","1":"2000-11-19"} Basically, the 1st part is a birthday (not allow null), and the second part is today year (allow null). It was saved to the database using serialize($arr)

I need a query to get the data based on the "birthday" part. Basically, it's something like this:

SELECT * FROM CUSTOMER WHERE MONTH(custom.1) = MONTH(NOW()) AND DAY(custom.2) = DAY(NOW())

I don't know how to refer to part 1 or part 2 of the custom field. If I can get the query to work, I can bring it back to php and use $arr = unserialize($arr) to fetch it into an array and use it.

Also, later on, I would need to update the part 2 of the custom field (aka, the "this year" part. It can be null, or last year, and need to be update to this year). How could I do that and still keep the part 1 (aka, birthday) the same?

Does anybody know how to do this?

Thanks.

Upvotes: 1

Views: 165

Answers (1)

ins0
ins0

Reputation: 3928

Solution 1:

You could update to MySQL 5.7 where JSON column field types are now supported.

So your query would look like this:

SELECT * FROM CUSTOMER WHERE MONTH(jsn_extract(custom,"$.1")) = MONTH(NOW()) AND DAY(jsn_extract(custom,"$.2")) = DAY(NOW())

Solution 2:

Query the data normaly and filter your needed result with PHP functions - like the JSON Decode + DateTime classes.

http://php.net/manual/en/class.datetime.php

http://php.net/manual/en/function.json-decode.php

Solution 3:

You could use the MySQL functions like SUBSTRING_INDEX to extract your values directly from the JSON. But this leads to strange errors when the data structure is changing and is not very fail safe.

Upvotes: 1

Related Questions