Darshak DRC Darshak DRC - 2 months ago 9
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

address/city/country
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

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.