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

by

The 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

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 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.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment