How to count post meta key values for all posts in database

Question

In my post meta data I have the following 'key' for every post: "image_details" a typical value of which is an array such as:

a:3:{s:23:"info_window_bg_image_id";s:3:"128";s:12:"colour_start";s:7:"#000000";s:10:"colour_end";s:7:"#b7b7b7";}

In the above example - info_window_bg_image_id is equal to 128

Ideally I would like to find out how many posts have the image_details['info_window_bg_image_id'] set to 128 but I would settle to find out if any of the posts have that value set.

The only way that I can think to do it (with my limited knowledge) is something along the lines of:

1. Retrieve an array of every post in the database

2. Foreach through the array using the ID's

3. Use get_post_meta(ID, Key) to get the "image_details" key

4. Check the value of the 'info_window_bg_image_id' element of the result to see if it matches the value.

This seems like it would be a heavy load because I would have to do this multiple times to check for other values.

Is there an easier way of doing this? Perhaps with a direct SQL query via the WP_Query class?

Just to reiterate my ideal would be to get a result that lets me know there are:

• 6 posts have post_meta image_details 'info_window_bg_image_id' set to 128
• 10 posts have post_meta image_details 'info_window_bg_image_id' set to 127
• 0 posts have post_meta image_details 'info_window_bg_image_id' set to 126

etc etc

But I would also be happy with a result that just says:

128 is used, 127 is used, 126 isn't used etc

Answer 1

You have a structural problem with your data. Serialized data in the database is terrible if you need to search over pieces of that serialized data.

• There is no reliable, efficient, and certainly no easy, SQL query to search over serialized data. "serialization" is a PHP mechanism. It isn't SQL. To the database that is just a string. Your only SQL choice is a regex on the string.
• WP_Query certainly won't do it. That functionality is not built in, probably for the reason listed above.

Iterating over the data is about the only solution you have given the data structure you are dealing with.

However, the correct solution in my opinion is to alter your data structure so that you are saving granular pieces of data in the database. In other words, use a lot of different keys for the values that you need to search over.