Query a WordPress Custom Field Stored As an Array

Querying a WordPress database is pretty straightforward using WP_Query or get_posts(). However, I ran into a problem when I was trying to search for all Posts with a certain custom field value that was stored as an array.

The Problem

I was using the popular Advanced Custom Fields plugin. The field type I was using was the “Relationship” field type. I had set it up to store related posts as an array of IDs. There are other ACF fields which are stored as arrays such as Page Link and Post Object.

The problem is, there was no way for me to search for all posts containing a certain value in the array. For example, if the field contained the ID’s, “5, 34, 96, 123”, and I wanted to search for ID=34, I wasn’t able to do it using normal WP_Query parameters.

How PHP Arrays are Stored in WordPress

I found out that the reason for the difficulty is that arrays are stored as serialized strings in WordPress. One way to get to them is to use the LIKE MySQL operation with WP_Query or get_posts(). This worked, but I realized a problem. You could get erroneous results if, for example, you were searching for post with ID=4, the LIKE would also give you all other posts containing a 4, such as 45, 142, 204, and so forth, since it was simply searching for the character “4” in a long series of numbers stored as a string.

To fix this, you have to see how serialized data is stored in the database. Here’s an example of serialized data:

a:2:{i:0;s:2:"44";i:1;s:3:"309";}

Just as a side note, the first “a” is used for serialized arrays, with the “2” being the number of elements in the array. Inside the curly braces, the “i”s are used for indices of the array, and the “s”s to indicate the number of characters in each string.

The Solution

From here, you can see the solution. Instead of searching for 4, we need to search for “4” with the double quotes. That will insure that we don’t get other numbers that contain a 4, such as 41, 214, etc.

I tried to do this using WP_Query with no success. The query would not work properly with the quotes added.

So, the solution is to do a full custom MySQL query (note, much of this was taken from this entry in the WordPress codex.

    $querystr = "
        SELECT $wpdb->posts.*
        FROM $wpdb->posts, $wpdb->postmeta
        WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id
        AND $wpdb->postmeta.meta_key = 'relationship'
        AND $wpdb->postmeta.meta_value LIKE '%\"".$this_id."\"%'
        AND $wpdb->posts.post_status = 'publish'
        AND $wpdb->posts.post_type = 'post'
        AND $wpdb->posts.post_date < NOW()
        ORDER BY $wpdb->posts.post_date DESC
        ";

    $results = $wpdb->get_results($querystr, OBJECT);

    foreach ($results as $result)
        {
        $related_verb[] = 'READ';
        $related_title[] = '<a href="'.$result->ID.'">'.$result->post_title.'</a>';
        }

In this example, I’m searching for posts that contain ID=$this_id in its Relationship custom field array.

The key is this line which shows how the double quotes can be included in the query:

AND $wpdb->postmeta.meta_value LIKE '%\"".$this_id."\"%'

After I discovered that, I was able to complete the query with no spurious results returned!

The Caveat

I will say that this is not a super efficient way to store and retrieve things in MySQL. It is best to not search through serialized data. Instead put each element in its own MySQL column, then search it. But, I was tied to existing plugins which stored values in this way, and the size of the database I was searching was not huge, so it was fine.

Let me know what you think, and if you have found a better way to solve this problem. – Brian

Brian Shim

I am a freelance web developer and consultant based in Santa Monica, CA. I’ve been designing websites using WordPress and from scratch using HTML, CSS, PHP, and JavaScript since 2010. I create websites and web applications for businesses, nonprofits, and other organizations. I have a degree in Electrical Engineering (BSEE) from California Institute of Technology and a degree in Engineering Management (MSEM) from Stanford University.

brianshim.com