WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] Using SQL Query on Custom Post Meta Data to Restrict Posts (9 posts)

  1. muraii
    Member
    Posted 8 years ago #

    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/

    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

  2. lellie
    Member
    Posted 8 years ago #

    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.

  3. tomm
    Member
    Posted 8 years ago #

    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!

  4. muraii
    Member
    Posted 8 years ago #

    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, etc.

    We'll think of something!

  5. muraii
    Member
    Posted 8 years ago #

    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, andwp_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

  6. muraii
    Member
    Posted 8 years ago #

    Hmm. So, it appears that I presumed complexity where there was none. I sent a note to Mr. Phu Ly, 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.

  7. ifelse
    Member
    Posted 8 years ago #

    "Thanks Tomm for the theme, and Phu for the enlightenment."

    Always happy to enlighten:)

  8. tomm
    Member
    Posted 8 years ago #

    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.

  9. CounterDax
    Member
    Posted 7 years ago #

    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.

Topic Closed

This topic has been closed to new replies.

About this Topic