Title: Using SQL Query on Custom Post Meta Data to Restrict Posts
Last modified: August 18, 2016

---

# Using SQL Query on Custom Post Meta Data to Restrict Posts

 *  Resolved [muraii](https://wordpress.org/support/users/muraii/)
 * (@muraii)
 * [20 years, 6 months ago](https://wordpress.org/support/topic/using-sql-query-on-custom-post-meta-data-to-restrict-posts/)
 * Hi, folks,
 * I was recently tooling around, looking at the wide variety of themes available,
   and came across Tom Maisey’s Zine Theme currently available from
 * [http://www.boilerhouseblog.com/fishcakes/index.php/2005/08/01/zine-theme/](http://www.boilerhouseblog.com/fishcakes/index.php/2005/08/01/zine-theme/)
 * It isn’t earthshattering, but it shouldn’t need to be: it gets the job done pretty
   nicely, at least on his demo site. I like the idea of being able to designate
   whether or not a particular entry is going to go in the center “Featured” column
   or in one of the side columns. There are, of course, a couple of caveats:
 * 1. It doesn’t seem to work, or work easily, with blog posts which belong to more
   than one category. If I happened to have a post categorized in the ID=1 and ID
   =2 categories, it would show up in both the “Featured” and “Unfeatured” columns.
   Correct me if I’m wrong.
 * 2. I don’t want to have to recategorize all my posts into one of only two categories.
   That basically obviates much of why I moved to WordPress, i.e. the ease with 
   which posts can be organized.
 * I started exploring using `query_posts` or `get_posts` to work around this, but
   neither opens the `postmeta` table for use (at least not as they’re currently
   constructed). After quite a bit of searching around, I decided I could just as
   easily fumble about and create my own SQL query to solve the problem, using a
   custom meta field to designate each post as either “Feature” or “Not Feature”(
   which structure could be extended for greater flexibility).
 * However, I’ve run into a wall: I don’t have a clue what I’m doing. That’s not
   _precisely_ true, but it’s not too terribly off the mark. Here is what I’ve got:
 * <?php
 * $feature_stub = "Feature";
    $featurePosts = $wpdb->get_results( "SELECT DISTINCT*
   FROM $wpdb->posts, $wpdb->postmeta WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id
   AND $wpdb->postmeta.meta_value='$feature_stub' AND $wpdb->posts.post_date <= '
   $now' AND ($wpdb->posts.post_status = 'publish') ORDER BY $wpdb->posts.post_date
   DESC LIMIT 3");
 *  foreach($featurePosts as $post):
    apply_filters('the_content',$post->post_content);?
   >
 * <!-- SOME HTML AND WORDPRESS JUNK HERE -->
 * <?php
 * endforeach;
 * ?>
 * Now, I had initially not given a value to `$feature_stub`, nor had I given any
   posts the meta_value of “Feature”; but I’ve fixed that. I am, though, getting
   an error:
 * >  Warning: Invalid argument supplied for foreach()
 * So, I can’t test if the query itself works; but it looks fairly straightforward.
 * Any advice? If I get it working, I’ll pop back in and update.
 * Daniel

Viewing 8 replies - 1 through 8 (of 8 total)

 *  [lellie](https://wordpress.org/support/users/lellie/)
 * (@lellie)
 * [20 years, 6 months ago](https://wordpress.org/support/topic/using-sql-query-on-custom-post-meta-data-to-restrict-posts/#post-293026)
 * I’m interested in this.. at the moment I use categories with a featured and headline
   option.. so things are categorised in both the normal cat like music or news 
   and if they’re a featured or headline story also the headline and featured category..
   mine works with both..
 * but I’m interested in accessing custom fields for another purpose.. no one seems
   to know much about them though.. i’ve tried to get help myself.
 *  [tomm](https://wordpress.org/support/users/tomm/)
 * (@tomm)
 * [20 years, 6 months ago](https://wordpress.org/support/topic/using-sql-query-on-custom-post-meta-data-to-restrict-posts/#post-293035)
 * Well, you know what you’re doing a hell of a lot more than me. I cobbled the 
   theme together purely using wordpress tags I looked up in the codex (eg query_posts
   and get_posts). I know _no_ php at all. Therefore, the limitations you mentioned
   in your post are pretty much due to limitations on WordPress’ template tags.
 * I am in the (very drawn-out, procrastinating) process of updating the theme in
   terms of looks, but my ability to tinker with what’s under the hood is limited.
   I like where you’re going with this, and I’d be _very_ interested to hear any
   ideas you’ve got about how to beat the limitations currently prestent in the 
   theme. Please keep going, and if you succeed, please post it to this thread!
 *  Thread Starter [muraii](https://wordpress.org/support/users/muraii/)
 * (@muraii)
 * [20 years, 6 months ago](https://wordpress.org/support/topic/using-sql-query-on-custom-post-meta-data-to-restrict-posts/#post-293040)
 * Tomm,
 * I think the “it’s not earthshattering” might’ve come across a bit more acerbic
   than I intended. I wanted to preempt the “it’s not the ONE TRUE LAYOUT” replies.
   I think it’s very clean, and well considered.
 * I would like to find a way to
 * (a) extend its capabilities with some custom querying, if necessary; and
    (b)
   possibly make that extension a plugin for others’ uses.
 * There’s really not much to updating the looks except for maybe custom bullets
   or other little things. I like the simple approach, a la [http://www.erraticwisdom.com](http://www.erraticwisdom.com),
   etc.
 * We’ll think of something!
 *  Thread Starter [muraii](https://wordpress.org/support/users/muraii/)
 * (@muraii)
 * [20 years, 6 months ago](https://wordpress.org/support/topic/using-sql-query-on-custom-post-meta-data-to-restrict-posts/#post-293184)
 * Okay, I’ve made some progress; but there are still some pieces that won’t fit
   together. First, the good news.
 * The SQL query I had was okay, but needed some variables defined. I’d pored over
   the `functions.php` file and figured out the right syntax, and had taken the 
   variable `$new` to be a WordPress defined class like `$wpdb`. It isn’t, so that
   needed to be defined. Here’s what I have:
 * `function featureColumns ($feature_stub) {
    $feature_stub = "Feature"; $now =
   current_time('mysql', 1); $featurePosts = $wpdb->get_results( "SELECT DISTINCT*
   FROM $wpdb->posts, $wpdb->postmeta WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id
   AND $wpdb->postmeta.meta_value='$feature_stub' AND $wpdb->posts.post_status ='
   publish' AND $wpdb->posts.post_date <= '$now' ORDER BY $wpdb->posts.post_date
   DESC LIMIT 3");
 * This creates the function `featureColumns` which gives you a query object `$featurePosts`
   which holds all the stuff from the `wp_posts` and `wp_postmeta` tables for any
   published post not post-dated after right now and which has the ‘meta_value’ 
   of “Feature”.
 * The next step is to take this and, well, use it. So that you keep whatever formatting
   WP and any plugins provide, you need to wrap it in a filter function, too. So,
   in place of
 * `<?php the_content() ?>`
 * use
 * `<?php echo apply_filters('the_content',$featurePost->post_content) ?>`
 * Since we’re working around the limitations of the `query_posts` and `get_posts`
   functions, well, we don’t get to make use of the built-in Loop. We have to create
   our own. I commented out the entire business of
 * `<?php if (have_posts()) : while (have_posts()) : the_post(); ?>`
 * and added a `foreach` loop, to end up with
 * `<?php // if (have_posts()) : while (have_posts()) : the_post();
    foreach($featurePosts
   as $featurePost):
 * <!-- Post-level HTML and other stuff here... -->
 * endforeach ?>
    So, that works well enough, and will give you your own Loop; but
   there are definitely limitations:
    1.  The posts don’t currently reflect to what categories they belong.
    2.  There is a SQL error with the comments for each of my test posts.
 * I’m sure there are others as well. I think that the primary reason for the issues
   is that the query object `$featurePosts` only includes content from the `wp_posts`
   and `wp_postmeta` tables. Obviously, then, your `index.php` has no clue where
   to find stuff for your categories or comments.
    I tried to include the `wp_categories`,`
   wp_post2cat`, and`wp_comments` tables as well, so that the `$featurePosts` object
   was “fully loaded”. That resulted in a page that wouldn’t load. I can only assume
   that that is a result of the sheer volume of data I’m trying to cram into that
   query object. I don’t know.
 * I’m not much of a coder of anything–not XHTML, CSS, PHP, or SQL–so if there is
   someone about with some stronger aptitude who can tell me where I’ve gone wrong,
   I’d happily fix this. I want at least to release a PHP file for inclusion, but
   there would be room for a nice little control panel to set the meta data and 
   control stuff better. I have no idea how to make a plugin or add a control panel,
   though.
 * Daniel
 *  Thread Starter [muraii](https://wordpress.org/support/users/muraii/)
 * (@muraii)
 * [20 years, 6 months ago](https://wordpress.org/support/topic/using-sql-query-on-custom-post-meta-data-to-restrict-posts/#post-293438)
 * Hmm. So, it appears that I presumed complexity where there was none. I sent a
   note to [Mr. Phu Ly](http://ifelse.co.uk), since he’d written up the piece on`
   query_posts` in the first place, asking for a little guidance. He very nicely
   and thoroughly explained that I had a good idea, but that there was a simpler
   approach that left The Loop intact. I felt immediately, well, retarded. But that’s
   another story.
 * >  As well as category exclusion, what query_posts allows is the category inclusion.
   > What you can do is the following: create 2 new categories called feature and
   > nonfeature. This is akin to the values for the custom key that you had wanted
   > to drive your post retrieval off.
   > Now for posts that you want to show in the featured column, link them to your
   > categories as usual…but also link them to the feature category. Likewise, for
   > posts in the non-featured column, link them as usual…but also link them to 
   > the category nonfeature.
   > Now, before the retrieval of entries in your feature block, call `query_posts(
   > cat=[feature_category_id])` and before the retrieval of entries in your non-
   > feature block call `query_posts(cat=[non_feature_category_id])`.
 * This is basically what lellie was talking about, too.
 * So, like, while I think it’s a valid wishlist item to be able to run `query_posts()`
   with meta data as the input(s), Phu provided the solution to our dilemma here.
   This, like using meta data, also extends well: if you want to segregate the first
   column from the second column from the 3rd column, explictly, just make sure 
   the have a separate category for each column and call that in the `query_posts()`
   function.
 * Thanks Tomm for the theme, and Phu for the enlightenment.
 *  [ifelse](https://wordpress.org/support/users/ifelse/)
 * (@ifelse)
 * [20 years, 6 months ago](https://wordpress.org/support/topic/using-sql-query-on-custom-post-meta-data-to-restrict-posts/#post-293439)
 * > “Thanks Tomm for the theme, and Phu for the enlightenment.”
 * Always happy to enlighten:)
 *  [tomm](https://wordpress.org/support/users/tomm/)
 * (@tomm)
 * [20 years, 6 months ago](https://wordpress.org/support/topic/using-sql-query-on-custom-post-meta-data-to-restrict-posts/#post-293479)
 * That’s great – it was Phu’s articles in the codex and on his blog that allowed
   me to learn the nuances of `query_posts` in the first place. It’s fitting that
   it was he who could resolve this.
 * It won’t directly work with the theme out of the box, because I’ve used `get_posts`
   to get the third column as it has an “offset” function that (as far as I can 
   tell) `query_posts` doesn’t have. However, I’m thinking of getting my head round
   a little php to resolve this.
 *  [CounterDax](https://wordpress.org/support/users/counterdax/)
 * (@counterdax)
 * [19 years, 11 months ago](https://wordpress.org/support/topic/using-sql-query-on-custom-post-meta-data-to-restrict-posts/#post-293687)
 * Try
    `<?php // if (have_posts()) : while (have_posts()) : the_post(); foreach(
   $featurePosts as $featurePost): setup_postdata($featurePost); ?>
 * It will load all the table field belonging to the post which is a pain for the
   server, but it works.

Viewing 8 replies - 1 through 8 (of 8 total)

The topic ‘Using SQL Query on Custom Post Meta Data to Restrict Posts’ is closed
to new replies.

## Tags

 * [custom](https://wordpress.org/support/topic-tag/custom/)
 * [get_posts](https://wordpress.org/support/topic-tag/get_posts/)
 * [query](https://wordpress.org/support/topic-tag/query/)
 * [query_posts](https://wordpress.org/support/topic-tag/query_posts/)
 * [sql](https://wordpress.org/support/topic-tag/sql/)

 * In: [Fixing WordPress](https://wordpress.org/support/forum/how-to-and-troubleshooting/)
 * 8 replies
 * 5 participants
 * Last reply from: [CounterDax](https://wordpress.org/support/users/counterdax/)
 * Last activity: [19 years, 11 months ago](https://wordpress.org/support/topic/using-sql-query-on-custom-post-meta-data-to-restrict-posts/#post-293687)
 * Status: resolved

## Topics

### Topics with no replies

### Non-support topics

### Resolved topics

### Unresolved topics

### All topics