Darshak DRC Darshak DRC - 1 year ago 66
MySQL Question

How to order by particular records from field in serialize format in mysql

I'm in big trouble in mysql/php.

I've stored

etc, in database using serialize method.
But now, I don't know how to find particular city from serialize data and how to sort this records in mysql.

My serialize data is :

a:6:{s:12:"country_code";s:2:"IT";s:10:"country_id";s:3:"105";s:7:"Address";s:16:"Via Emilia 234/B";s:3:"Zip";s:5:"40068";s:4:"City";s:21:"San Lazzaro Di Savena";s:8:"Province";s:2:"BO";}

I want to find province = "BO" from above data and also sort this data by province in asc or desc order.

I don't want to use like.

Please help me!

Answer Source

When you choosed to store php serialized value in a db column (schemaless paradigm?) which is not understod by mysql engine (that sees it as a text value), you locked out yourself to use sql based on that values (except string functions).

What you can do is for those fields you want order by where etc. create columns.

With postgresql you may choose JSONB to stay schemaless but use sql on value , i am not familiar with mysql's json support.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download