1

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

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.

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.

  • Thank you for the information. I'll look into changing this value into it's own key. This is a completely new website so it shouldn't be a major problem to alter the structure of the database. Thanks again. – John T Apr 6 '14 at 20:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.