How to search data from json array in mysql and php?

Question

{"email": "afadf%40gmail.com", 
"phone": "2525", 
"rooms": "4", 
"floors": "2", 
"user_id": "2", 
"ad_title": "sdafasfdasf", 
"features": "['Elevator','Center Stairs']", 
"car_space": "5", 
"full_name": "adaf", 
"sortOrder": "10000", 
"build_year": "2018"
}

I would like to searh data where features='Elevator' using above json format.

And what version of MySQL are you using? Newer versions have specific JSON processing commands — RiggsFolly, Jul 12 '18 at 10:35
This question is a little vague, is that data stored in your database or are you asking how to search a database using a term from the JSON? What exactly are you trying to do, what have you tried and what isn't working? — CD001, Jul 12 '18 at 10:43
php version is 7.0.30. and i want to search data from mysql. like "SELECT data from properties WHERE json_extract(data,'$[*].features')='Elevator' — Bilal Ali Akbar, Jul 12 '18 at 11:05
Even with JSON support in newer versions it's generally not a good idea to store unnormalised data in an SQL database. Can you refactor the data into a structure that's more easily queried by SQL? (Hint: Dependant tables are usually how you deal with non-atomic fields) — GordonM, Jul 12 '18 at 11:24

score -1 · Answer 1 · answered Jul 12 '18 at 10:50

-1

You can use json_decode() function. This function will return an array from JSON.

Ex:

$data = '{
    "email": "afadf%40gmail.com", 
    "phone": "2525", 
    "rooms": "4", 
    "floors": "2", 
    "user_id": "2", 
    "ad_title": "sdafasfdasf", 
    "features": "['Elevator','Center Stairs']", 
    "car_space": "5", 
    "full_name": "adaf", 
    "sortOrder": "10000", 
    "build_year": "2018"
}';
$arr = json_decode($data);
//The $arr variable will be an array contains the data from $data variable

Then, you can use array_search() function to search. Some references :

Stack Overflow

W3Schools

answered Jul 12 '18 at 10:50

Abdalhaqq Muhammad Saih

51
3

php version is 7.0.30. and i want to search data from mysql. like "SELECT data from properties WHERE json_extract(data,'$[*].features')='Elevator' – Bilal Ali Akbar Jul 12 '18 at 11:11
The OP wants to only fetch records that meet a search criteria. This will require you to fetch all records then filter them in PHP, which can get horribly inefficient very quickly for big datasets – GordonM Jul 12 '18 at 11:24
Ok but can we search with in json array of mysql? – Bilal Ali Akbar Jul 13 '18 at 09:40

How to search data from json array in mysql and php?

1 Answers1