Retrive data which is save in json format using mysql Query (codeigniter)

Question

M saving the data in to db using json format such as example :

state_id city_id
["21"]   ["32,35,67"]

And now want to search using mysql query(codeigniter) where state_id = 21 AND city_id = 32.

Can any one please help me, While googling i found one stuff "json_extract" but it's unable to know how to use it in query.

Did you even try writing a query using json_extract? If so show us — RiggsFolly
– RiggsFolly, Commented Mar 19, 2017 at 11:57

Dev Environment · Accepted Answer · 2017-03-19 12:05:11Z

1

If I understood you correctly, this is the answer:

$json = '{"state_id": 21, "city_id": [32,35,67]}';
$decodedJson = json_decode($json, true);

/** @var $state_id int */
/** @var $city_id array */
extract($decodedJson);

$sql = printf("SELECT * FROM `table_name` WHERE `state_id` = %d AND `city_id` IN ( %s );", $state_id, join(',', $city_id));

echo $sql.PHP_EOL;

OUTPUT:

SELECT * FROM table_name WHERE state_id = 1 AND city_id IN ( 1,2,3 );

answered Mar 19, 2017 at 12:05

Dev Environment

719 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Swapnil K Over a year ago

Okay, Will try the above code and update u ... Thanks

hassan · Accepted Answer · 2017-03-19 12:21:08Z

0

using JSON_SEARCH

select * from `table` where JSON_SEARCH(`city_id`, 'all', '32') IS NOT NULL;

for codeigniter, simply use the query function -as i cant found another prober built-in way in codeigniter to do this- :

$results = $this->db->query('select * from `table` where JSON_SEARCH(`city_id`, "all", "' . $city_id . '") IS NOT NULL;');

edited Mar 19, 2017 at 12:21

answered Mar 19, 2017 at 12:14

hassan

8,3282 gold badges28 silver badges40 bronze badges

Collectives™ on Stack Overflow

Retrive data which is save in json format using mysql Query (codeigniter)

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related