Get the greatest value into serialized data with php into mysql column

Question

Hi

What is the way to get the greatest value into a serialized data. For example i have this in my column 'rating':

a:3:{s:12:"total_rating";i:18;s:6:"rating";i:3;s:13:"total_ratings";i:6;}

How can I select the 3 greatest 'rating' with a query?

thanks a lot

Answer 1

You're probably looking at a pile of SUBSTRING_INDEX(field,':',@offset) calls if you want to do it in SQL. It would be very grisly. Storing a serialized version of an object in the db is a convenience for persistance, but it should not be considered a permanent storage method. If you insist on using the serialized string for queries, you've lost all the power of a relational db and you might as well store the strings in a text file.

The best option is to use the serialized string only for persistance purposes (like remembering what the user was doing last time they visited), and store the data you need for calculations in properly normalized fields and tables. Then you can easily query what you need to know.

The other option is to select all the 'rating' strings from rows whos fields meet certain other criteria (e.g. the date_added field is within the last week), reinstantiate all the objects in your application layer and compare them there.