The "out of the box" type of data extraction that you can do with Wordpress is awesome... really, it is. Until it's not. You see, there's actually quite a few scenarios out there where you need more than a simple function or tag to grab the data that you want and convert it to something that's actually useful. Buckle your seat belts, we're about to jump off the tracks for a few minutes...
Interfacing with the Database for Fun and Profit Category IDs
Today I'm going to share a little code snippet with you that I've found incredibly useful over the past several months... but before you read on, know that it's
- ...going to delve into some database stuff (which can get hairy if you're a brand new WP developer)
- ...I'm going to use a really specific example to teach some much bigger lessons.
In fact, the example itself is meaningless... this is all about the techniques we'll use to solve the hypothetical problem. By the end of this you'll learn that there's virtually no limit to what you can do with just about any piece of information that's saved inside the WordPress database.
Oh, and because this is homebrew stuff, I'm guessing that someone out there is going to have a different way of doing this. Share, rant, vent about it in the comments section :P I'm all ears!
Our Assumption: We have a list of category slugs (saved from a custom meta box on a page).
Our problem: You can't use category slugs in query_posts();, but if the only piece of information that we have is the slug, we need to find a way to turn it into something that we actually can use: category IDs. There are a few ways of converting slugs to IDs (get_category_by_slug, get_term_by, etc.), but they aren't quite as flexible or powerful as the method I'll show you. Will they work though? Yes, those will work fine and we'll discuss them at the end. As I said, we're going off the tracks for a few minutes here... enjoy the ride :)
Our goal: to convert one piece of category data into another by asking the database nicely for it, combine it with other similar pieces to make a array, then turn that array into an string that we can actually use in other places of our theme or plugin.
The Hypothetical Problem: How to Grab the Category ID from a Category Slug
Our hypothetical problem includes a custom meta box that returns only Category Slugs. Our problem is that we need them converted to IDs before we can use them in query_posts();.
Picture this: You have a custom field assigned to a page template called "category_filter". What is does is grab a list of categories that the user has selected from a custom meta box, and returns their category-slugs. Great... slugs. What are we going to do with those?
As of the current WordPress version, there's no quick and easy way (at least that I could find) that allows you to simply convert category-slugs into category-ids. Which, frankly, kinda sucks because this sort of "currency conversion" type stuff is remarkably easy if we're trying to do the same thing with post data (ie: turning a name into a slug, a slug into an ID, or an ID into a name).
In the case of posts, all you need to do is fire up query_posts or get_posts and it becomes pretty easy to grab anything you need. Not so much in the case of categories... but like anything in WordPress, where there's a will, there's a way.
We know that the data exists in our WordPress database; So as long as there's some way for us to associate the one piece of data (category ID) with the other (category slug), we can convert it... it'll just take a little extra effort. Here's what our data looks like in the database table:
Showing the wp_terms table... just showing the relationship between the category-id (term_id) and the slug (shown).
An example of the data on the actual table.
Edit: We're about to jump into $wpdb right here, but as a few commenters have pointed out, there shortcuts around this... and when I say shortcuts, I mean, that there are shorter ways of grabbing a single category ID. For what we'll be doing ultimately, those ways are actually a bit longer ways of doing this... but as I mentioned above, this is about learning a few ways of doing things... so I'll show both (plus a bonus method) at the end!
Introducing $wpdb->
Interfacing with the Database is one of the more obscure sections of the WordPress codex, but it includes just the information that we're going to need to make our conversion (and learn a fancy new trick that we can use in lots of different situations). Here are the basics:
You can use the object $wpdb to "talk" to any table on the WordPress database. If you've ever tinkered with MySQL at all, you'll recognize some of how this is done, since this is all done using a version of the ezSQL class. For instance, the query function allows you to execute any SQL query on the WordPress database.
<?php $wpdb->query('query'); ?> // Where 'query' represents the SQL query you wish to execute
You can read more about $wpdb->query in the codex page... Today, we're going to be little more refined and use the following:
<?php $wpdb->get_var('query',column_offset,row_offset); ?>
From the codex: The get_var function returns a single variable from the database. Though only one variable is returned, the entire result of the query is cached for later use...
Perfect! What this means is that we can essentially grab ANYTHING inside the WordPress database as long as we know where to find it... and we just happen to know where to find our Category ID because we already know the Category Slug. Following me still?
Extracting a Single Category ID from a Single Category Slug
Here's where it gets hot and heavy. Using our example where we know the category slug, we can extract the ID like this:
$cats = get_post_custom_values('category_filter'); // Returns an array of cat-slugs from the custom field.
$catid = $wpdb->get_var("SELECT term_id FROM $wpdb->terms WHERE slug='$cat'"); // Grab the category-id from the WPDB using the category slug ($cat).
This will return a single category ID (term_id as it's known in the DB) and store it in our $catid variable. In some situations, we might be done... you could simple echo the $catid wherever you wanted to. In our example though, we've still got some work to do.
To be clear, the initial $cats variable is grabbing the category slugs from our custom field... you could insert the slugs using other methods as well... but you do need a way to grab the slugs (or some piece of data that associates with our ID).
Grabbing Multiple Category IDs from Multiple Category Slugs
Let's extend our little snippet now by allowing for more than one category using a quick foreach loop:
$cats = get_post_custom_values('category_filter'); // Returns an array of cat-slugs from the custom field.
foreach ( $cats as $cat ) {
$catid = $wpdb->get_var("SELECT term_id FROM $wpdb->terms WHERE slug='$cat'"); // Grab the cat-id from the DB using the category slug ($cat).
$acats
[] = $catid; // Turn the list of ID's into an ARRAY, $acats[]
}
This last piece of code ($acats...) uses some simple PHP to turn each category ID that we find into part of an array... which might be useful to you. But let's go yet another step and convert the array into a string (because most functions in WordPress use strings).
Converting the Array into a Comma Separated String Using Join();
We're getting close now! Let's use a little bit more simple PHP to convert the array of Category IDs that we've created into a string that we can use in query_posts.
$cats = get_post_custom_values('category_filter'); // Returns an array of cat-slugs from the custom field.
foreach ( $cats as $cat ) {
$catid = $wpdb->get_var("SELECT term_id FROM $wpdb->terms WHERE slug='$cat'"); // Grab the cat-id from the DB using the category slug ($cat).
$acats
[] = $catid; // Turn the list of ID's into an ARRAY, $acats[]
}
$cat_string = join(',', $acats); // Join the ARRAY into a comma-separated STRING for use in query_posts
//echo $cat_string; // Remove Commenting to Test Output
Notice the last line where you can remove the "//" to test your output at this point, which should be a series of numbers (the category IDs) like this: 11, 123, 198, 210...
You can read about how the PHP 'join' function works right here, but all you really need to know is that we just turned our array of category ID's into a string, with each ID separated by a comma... This is handy, because that's exactly what we need for our final step...
Putting It All Together With Query_Posts();
Let's review before the big reveal. We've taken a known value (our category slugs), turned them into category IDs by asking the database, then joined our category IDs into a string that we can use in query_posts();. All good? Ok, here's what that all looks like:
$cats = get_post_custom_values('category_filter'); // Returns an array of cat-slugs from the custom field.
foreach ( $cats as $cat ) {
$catid = $wpdb->get_var("SELECT term_id FROM $wpdb->terms WHERE slug='$cat'"); // Grab the cat-id from the DB using the category slug ($cat).
$acats
[] = $catid; // Turn the list of ID's into an ARRAY, $acats[]
}
$cat_string = join(',', $acats); // Join the ARRAY into a comma-separated STRING for use in query_posts
//echo $cat_string; // Remove Commenting to Test Output
$args=array(
'cat'=>$cat_string, // Query for the cat ID's (because you can't use multiple names or slugs... crazy WP!)
);
query_posts($args);
Not bad right? We're ready for action - Now we can use this in the basic If/While post loop to dish out posts that are only in the particular category IDs that the user selected for in the checkboxes (remember the checkbox image at the top?).
The Complete Snippet
Alright, the big reveal! We just need to add a few extra safeguards to make sure that we can use/reuse this snippet. We need to ensure that the custom field, "category_filter" exists (there's always the chance that the user doesn't select any categories at all). If it doesn't exist, there's no sense in running around in circles... so we'll use a simple if/then wrapper to only run our snippet when the custom field exists:
<?php
if(get_post_custom_values('category_filter')) : // If the category filter exists on this page...
$cats = get_post_custom_values('category_filter'); // Returns an array of cat-slugs from the custom field.
foreach ( $cats as $cat ) {
$catid = $wpdb->get_var("SELECT term_id FROM $wpdb->terms WHERE slug='$cat'"); // Grab the cat-id from the DB using the category slug ($cat).
$acats
[] = $catid; // Turn the list of ID's into an ARRAY, $acats[]
}
$cat_string = join(',', $acats); // Join the ARRAY into a comma-separated STRING for use in query_posts
//echo $cat_string; // Remove Commenting to Test Output
endif; // End the situation... carry on as usual if there was no category filter.
?>
<?php
$args=array(
'cat'=>$cat_string, // Query for the cat ID's (because you can't use multiple names or slugs... crazy WP!)
);
query_posts($args);
?>
This snippet assumes that you have a custom field called "category_filter" assigned to your post... but you can Franken-code this snippet to do pretty much anything you want. Ok, maybe it won't make you a sammich... but it's still a pretty nifty snippet.
A Practical Example
See those images? Each one is a blog post that was grabbed from our custom query_posts(); setup... See, category slugs aren't completely useless after all! View the full example.
For instance, I used this snippet to form the basis for a custom filterable portfolio page template in my latest SuperSkeleton WordPress theme. The "portfolio page template" includes the list of category checkboxes, which allows the user to select which categories of portfolio posts he wants to show up on the page. The workflow is the same, I'm just adding some extra twists after we use query_posts();.
- Category Slugs...
- Converted to Category IDs...
- Used in Query_Posts...
-
Profit!!!Filterable Portfolio Page Template
You can use this for a lot of different things though... in fact, just about any case where you need to create a page template that queries for a specific category can put this to use.
Why Use $wpdb?
Two reasons. Primarily, I couldn't get the preferred techniques of get_category_by_slug and get_term_by to work in my case because they simply weren't returning what I needed in order to grab lots of this stuff (they worked fine for grabbing single categories when I knew the category slug... but what if I didn't want to hard code the slug?).
The second reason is educational... this little circuitous problem-solving exercise gives me an opportunity to write something about $wpdb, which is a highly underrated technique when trying to manipulate and extract very specific information from the database. Frankly, because of the fact that you can run direct SQL queries using it, it's hard to find something it can't do.
Oh, and the thumbnail for this post is Will Smith singing a rap song... I'm hoping you didn't expect anything too official :P This is good clean fun and no one's saying that this is the only way of grabbing what we did... in fact, here's a few other ideas:
Alternate Methods Without $wpdb
Using Get_Category_By_Slug();
Thanks to Andrew for bringing up that you can also use a more specific function to convert a Category Slug into an ID using get_category_by_slug();. Check out the codex page for this, and you can view what our code would look like below:
Simple version:
<?php
$idObj = get_category_by_slug('category-name');
$id = $idObj->term_id;
?>
Our version (which is still long, but you can see where we're using get_category_by_slug(); instead of the $wpdb-> as our data gathering tool:
<?php
if(get_post_custom_values('category_filter')) : // If the category filter exists on this page...
$cats = get_post_custom_values('category_filter'); // Returns an array of cat-slugs from the custom field.
foreach ( $cats as $cat ) {
$idObj = get_category_by_slug('$cat');
$id = $idObj->term_id;
$acats
[] = $id; // Turn the list of ID's into an ARRAY, $acats[]
}
$cat_string = join(',', $acats); // Join the ARRAY into a comma-separated STRING for use in query_posts
//echo $cat_string; // Remove Commenting to Test Output
endif; // End the situation... carry on as usual if there was no category filter.
?>
<?php
$args=array(
'cat'=>$cat_string, // Query for the cat ID's (because you can't use multiple names or slugs... crazy WP!)
);
query_posts($args);
?>
One More Method Using Get_Term_By();
Finally, as Thomas elequently put it in the comments, this is a lot like "programming equivalent of going from Rochester, NY to Boston via Wheeling WV".... You can use the following codex page for more details on this method. There's even a built in function for building our array, which helps us skip a step in this case:
$myCategory = get_term_by('id', $postCategories[0]->cat_ID, 'category');
We could simply pass in our slug and be on our merry way ;)
Wrapping It Up
Yes, this was a very specific example with some very specific assumptions up front... but, if you're still following along at this point, you should have successfully learned a thing or two about the following:
- How to use $wpdb to execute SQL queries on the WordPress database (grabbing the category ID from a category slug in our example, but you can do a lot more than this)
- How to run a foreach loop on a list of categories and store them in an array
- Convert an array of category IDs to a comma separated string for use in query_posts();
Heckler's Note: Yes, if we started with category IDs in the first place, we'd never need to bother with this long winded process... but then, it wouldn't be a very educational tutorial, now would it? ;P Only time (and your comments) will tell if this is actually the best way to approach this, but hopefully you know just a little bit more about WordPress now! Thanks for reading!
Comments