This is Part 1. To skip ahead, check out to how I categorize the results of these 2 queries.

So, you have a WordPress site where you’re using custom fields on a custom post type. Great! When you display your custom post type, you can use a get_post_meta( $post_id, 'my_field_name' ) call to show your new field, echo it out and your’e done!  Easy-peasy. That’s great if you want the meta data for one item, but what if you want the custom field for multiple items? What if you want to get that custom field for ALL the items?

Custom fields, custom post types, custom problems

Spoiler alert: There is no magical function in WordPress that you can pass a key to and have it spit out all the data in a nice tidy array. Booo!

So, recently I was faced with a problem that I initially thought would be really easy, but it turned out to be more complicated than I thought.

I needed to get the data for several custom fields for every single product post type. Sounds easy, right? There must be some magical function in WordPress that you can just pass the key of a custom field and have it spit out all the data in a nice tidy array, right?

The data is not the problem.

So, let me lay out the current data structure. We have a custom post type called product and it has custom fields like so:

• product_profile ( url )
• product_profile_description ( plain text description of file )
• research_info
• research_info_description
• etc.

These fields are essentially links to downloadable assets (PDFs usually) related to the product that physicians may want to read.

The product_profile field would point to a uri where the file is located and the _description field is essentially an excerpt from the document so you know what you’re downloading.

What, no get_posts_metas?

My instinct told me that there should be some WordPress method like get_posts_metas which gets all the metas that I want. In my imaginary idealistic world, you would call it like this get_posts_metas( ['product_profile', 'product_profile_description'], 'product' ) and this unicorn function would magically return all the data that I need.

Note: There is no unicorn function called get_posts_metas that returns all the data that I need… but there should be.

The wrong way that will not scale

It turns out that the only easy WordPress way to do this, is both simple and inefficient. It’s pretty simple to do. 1st you get all the posts using get_posts and then use a foreach, calling get_post_meta( $post_id ) to get the specific data for each product. Then, just put the data into your own array. It could look something like this:

$args = array(
  'post_type' => 'product',
  'post_status' => 'publish'
);
$products = get_posts( $args );
$profiles = array();
foreach( $products as $p ) {
  get_post_meta( $p->ID )
  $profiles[] = array(
    "file_url" => $p['product_profile'],
    "file_desc" => $p['product_profile_description'],
  );
}

DON’T DO IT THAT WAY! (maybe)

Although this will work, this method will NOT scale well. If you have only a few items, it will work in a pinch, but let’s look at what we’re actually doing.

If you had 100 products that you needed to get meta data for… you are calling the database 101 times ( once when you call get_posts, and then once each time you call get_post_meta ). If you had 1000 products… 1001 calls! Sure, you could cache it, but it’s still wasteful and it won’t scale well, especially when you consider that meta_data is stored in just ONE table! You should be able to do this in only ONE call to the database. So how do we do it?

The scalable way… is harder

Like many things, the scalable way is harder. But it’s worth it, trust me! Stick around, and we’ll get through it step by step. It will involve some SQL, but I won’t get into database theory (well not too much, anyhow). Although it’s more difficult, it’s only a little bit more difficult and understanding how WordPress structures its data never hurts and will make you a better developer.

The post_meta table is ONE table.

This is where knowing how table structures and how data is stored comes in handy. It helps to know that all the custom fields are stored in one table, usually called: wp_postmeta and that the data in that table is pretty straightforward. It’s a simple 1-to-many table with only 4 fields: meta_id, post_id, meta_key, and meta_value. Log into your SQL tool of choice ( like phpmyadmin ) and run the following SQL in your database:

SELECT meta_value FROM wp_postmeta WHERE meta_key = 'my_field_name'

This will return a one-cell table of the ‘my_field_name’ values for ALL posts. If you want to see all the meta_keys, just remove everything from WHERE. This will get all posts and any custom posts as well, which is not what we want here, so if you wanted to make sure you only get the fields for the product post type, then you’d need an INNER JOIN with wp_posts, like so:

SELECT meta_value FROM wp_postmeta
INNER JOIN wp_posts AS p ON p.id = post_id
WHERE `meta_key` = 'my_field_name' AND p.post_type = 'product'

I won’t get into how the SQL works here, but you should be able to modify that code to work on your data. Your tables may start with a different prefix than wp_ and you’ll have to use your meta_key field names.

So how do we do get this data into WordPress?

Sanitize your SQL, or  else

So, we have an idea of the SQL that we want to run in the database, but before we do that, we need to know about the prepare method that is part of the $wpdb class. This method allows you to prepare RAW SQL for consumption. Safe is good when you’re using SQL into your database, and essential if you’re ever relying on user input. The documentation for prepare is pretty straight forward, so I’ll leave you to read it.

The second function you’ll want to know is the get_col method. Documentation for get_col is also straigtforward. Give it SQL, it gives you results for a column.

So, let’s see how this works by getting all meta_keys that end in _description. Here’s how we do that. First, let’s sanitize the SQL by running it through the prepare function:

$sql_query = $wpdb->prepare('SELECT DISTINCT meta_key FROM ' . $wpdb->postmeta .' WHERE meta_key LIKE "' .'%%_description"' )

There’s a few new things in that SQL. First, we use the DISTINCT keyword. That just makes sure we don’t get duplicate rows. $wpdb->postmeta returns the name of the WordPress postmeta table. You can’t guarantee it will be wp_postmeta because WordPress allows you to choose the database prefix. Multisite also changes up the table names. The LIKE keyword is a wild card Keyword and it relies on % to do it. In WordPress you need to escape it by using %% in the command, but other than that, there’s nothing tricky. But we’ve only generated a SQL string. We haven’t actually done a database call. That’s what you need get_col for.

To do this, we simply pass $sql_query to $wpdb->get_col and that’s all there is to it.

$description_keys = $wpdb->get_col( $sql_string );

So, we’ll end up with an array of keys like so:

• product_profile_description
• research_info_description
• etc.

The keys to the kingdom (of data)!

So, why did we get the keys, and not the data? Well, remember we have a pattern of custom fields that all end in _description. If we add another one later, we don’t need to modify the code. We just need to stick to the same naming standard. When programming, it’s really important to avoid hard-coding values whenever possible. Your code will be more useful in the long-term and it will force you to look for time-saving patterns. In short, your code will just work.

In $description_keys we have an array. Because I need the related url fields, I iterate through the array and do some string manipulation. Basic PHP.

$url_keys = null;

foreach( $description_keys as $k ) 
  $url_keys[] = substr( $k, 0, strpos( $k, '_description' ) );

Loop through the keys, and get a substring of the first part. Then we can just merge these two arrays to get ALL the columns we want:

$all_keys = array_merge( $description_keys, $url_keys );

And now we have all the fields we need.:

• product_profile_description
• research_info_description
• product_profile
• research_info
• etc.

So, now that we have all the keys we want from the postmeta table, what do we do with them? It’s great that we have all the keys, but what we REALLY want is the data.

Well, first of all, we have to turn the array into a comma-separated list for the SQL call… so we use implode with some extra quotes on each side. We need those quotes.

$all_keys_list = '"' . implode( $all_keys, '","' ) . '"';

Get results with get_results

Next we have to get the actual results from the table. Since we need more than one column (in this case, post_id, meta_key and meta_value) from the database, we can’t use get_col. We’re going to use get_results. This method will return multiple columns and serialize them as an array. We’re also going to use the SQL IN command to get items in that list of meta keys. Here goes:

// prepare the SQL 
$sql_query = $wpdb->prepare("SELECT DISTINCT post_id, meta_key, meta_value FROM $wpdb->postmeta WHERE meta_key IN (" . $all_keys_list. ") ORDER BY post_id, meta_key ASC");
// Get the results
$results = $wpdb->get_results( $sql_query );

We’ve also done some sorting and get the post_id, which will be handy later when sorting the data.

Finally, we’ve GOT an array of a bunch of meta fields. We did it in TWO calls to the database and if any custom fields are added later that end in _description we’ll also get those without changing the code!

If you want to output the results of your work, you can want to output it like this: <pre><?php print_r( $results ) ?></pre>

All your meta data are belong to us!

We’re almost there. The goal is to display a lists of all Product Profiles and link to each document… so we are going to have to categorize the data first… phew! More work! But I’ll leave re-arranging the meta-data from the get_results call to another post.