Dealing with data stored as arrays in a MySQL DB

Question

So I know storing arrays in a DB field is wrong and would never do it myself, however a 3rd party plugin my company is using stores data in an array and I was wondering if you could help me try to deal with it.

It basically seems to link 2 tables and add a view count. Here is an example of the data:

a:4:{i:4;i:196;i:26;i:27;i:5;i:155;i:34;i:4;}

So I think this means there are 4 entries in the array, each with 2 attributes. The first - i.e. 4, 26, 5, 34 are "store codes". The second lot (196, 27, 155, 4) are number of plays. God knows why they are stored like this as there is already another table that links the video with the stores and they could've just stuck another column there for view count.

Anyhow, what I want to do is order by view count based on store id within that array. Do you think this is possible and does anyone have any ideas how to do this? If storing data like this is a standard, do you know the name for it as I could probably take it from there?

Thanks!

Answer 1

Just as I was about to post a comment that this looked like no serialization format I've ever seen, I had a lucky flash of insight. (What programming language would make it altogether too easy to stash an array in a string like that? Ah...)

I think you may find that what you're looking at is the output of the serialize() function in PHP... the companion function is unserialize().

I'm not aware of a mechanism for deserializing this natively in MySQL (but then again, I wasn't aware of the whole thing a few minutes ago)... but if I had to do it natively in MySQL, I would go to the source code of common_schema for ideas and helper functions like get_num_tokens(), which returns a count of the number of tokens found in a given string of delimited text. There is genuine outside-the-box genius lurking in common_schema.

Untwizzling scalar values into rows is not something easily done in MySQL but prettify_message() in common_schema provides an example of how it is technically possible... by splitting the strings and writing what you find in to a new table, it seems possible and maybe even borderline practical if you're not dealing with a massive data set. Figure that part out and you could theoretically even build a trigger to keep your more-properly-structured table syncronized whenever one of those serialized columns is updated.

Or you could write something in php to read from the database, deserialize the arrays, iterate them, and stuff a table, but doing it natively in MySQL would be much more fun and at least offers the potential to keep another table always-current when you need it without an external script.

Answer 2

Although that value is compound it does seem to be structured. You could treat it as nested delimited lists and (a bit like comma-text, but not using commas) and use one of the many techniques for splitting that in SQL. Here's one post on the subject, and here's another on SO.