CodeIgniter Forums

Full Version: How to retrieve data from json column?
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
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();
@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/7602...-via-mysql
(09-23-2019, 05:28 PM)php_rocs Wrote: [ -> ]@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/7602...-via-mysql
Thank you for your help, but I have the same a issue. Because if I write

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.
@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?
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.
@jaune,

I don't see the mockup sql file..