+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: How to retrieve data from json column? (/showthread.php?tid=74437)
How to retrieve data from json column? - jaune - 09-23-2019
I’m a beginner with codeigniter. I’m trying to retrieve data from json column of my sql db.
The db structure is like this
Code:
DB NAME : order
——————————————————---------------------------------------------------
Id | description
——————————————————————————————————-----------------------------------
1 | {“pc”: [{ “brand”: “name”}], “description”: [“LL”, “DC”]}
————————————————————————————————————---------------------------------
2 | {“pc”: [{ “brand”: “name”}], “description”: [“TR”, “KK”]}
————————————————————————————————————---------------------------------
3 | {“pc”: [{ “brand”: “name”}], “description”: [“DC”, “JJ”]}
————————————————————————————————————---------------------------------For example, I want to retrieve all instances that has order = dc
Code:
$data = $this->db->select($select)->from(‘order’)
->where(“description”, “DC”) ->get()->result();RE: How to retrieve data from json column? - php_rocs - 09-23-2019
@jaune,
What field type is description? Is it a string? if so, then you could search it like a string. Using the LIKE condition.
I also did a search online and found numerous articles that should be able to assist you...
https://scotch.io/tutorials/working-with-json-in-mysql
https://stackoverflow.com/questions/7602204/php-how-to-store-and-read-json-data-via-mysql
RE: How to retrieve data from json column? - jaune - 09-24-2019
(09-23-2019, 05:28 PM)php_rocs Wrote: @jaune,Thank you for your help, but I have the same a issue. Because if I write
What field type is description? Is it a string? if so, then you could search it like a string. Using the LIKE condition.
I also did a search online and found numerous articles that should be able to assist you...
https://scotch.io/tutorials/working-with-json-in-mysql
https://stackoverflow.com/questions/7602204/php-how-to-store-and-read-json-data-via-mysql
SELECT id FROM order WHERE JSON_EXTRACT(description, "$.description[1]”) = 'DC'
but i f I write
SELECT id FROM order WHERE JSON_EXTRACT(description, "$.description[*]”) = 'DC'
[*]It doesn't work. Because I'm trying to retrieve all instances, so I can't know for each instance which is the index of the array description.
RE: How to retrieve data from json column? - php_rocs - 09-24-2019
@jaune,
Maybe, when I get a minute or two I can setup an online database with test data and see what we can come up with. Do you have any more test data? Also, what version of CI, MySQL and PHP are you using?
RE: How to retrieve data from json column? - jaune - 09-24-2019
Thank you for your help!
The settings are:
CI = 3.1.8
libmysql - 5.6.43
Php 7.2.7
I have attached a mockup .sql file.
RE: How to retrieve data from json column? - php_rocs - 09-27-2019
@jaune,
I don't see the mockup sql file..