WordPress.org

1. mylagoon
   Member
   Posted 3 years ago #

   I'm trying to figure this somewhat complex function, but not having much luck.

   I want to be able to have a page, or the generated category page, to show all posts that meet 3 conditions:

   1. they are in a defined category number
   2. they have a defined custom field key
   3. they have a defined custom field value

   If they meet the 3 conditions, then the post is shown, if it doesn't it isn't shown.

   I have the feeling that this will require a custom SQL query, so any help would be appreciated.

2. Kafkaesqui
   Moderator
   Posted 3 years ago #

   "I have the feeling that this will require a custom SQL query"

   So do I... But actually, here's two ways to do it.

   1. This is similar to what I use on my site for my plugins Page; it uses query_posts() to pull in the category, then tests custom key/value:

   <?php
$catid = ''; // set to category ID #
$customkey = ''; // set to your custom key
$customvalue = ''; // set to custom value

   query_posts("cat=$catid&showposts=-1");
while(have_posts()) : the_post();
$value = post_custom("$customkey");
if($customvalue == $value) : $more = 0;
?>

   ~ Your "Loop" template tags and whatnot go here ~

   <?php endif; endwhile; ?>

   2. The custom SQL query you were expecting:

   <?php
$catid = ''; // set to category ID #
$customkey = ''; // set to your custom key
$customvalue = ''; // set to custom value

   global $wpdb;
$my_posts = $wpdb->get_results("SELECT * FROM $wpdb->posts, $wpdb->post2cat, $wpdb->postmeta WHERE ID = $wpdb->post2cat.post_id AND ID = $wpdb->postmeta.post_id AND $wpdb->post2cat.category_id = '$catid' AND meta_key = '$customkey' AND meta_value = '$customvalue' ORDER BY post_date DESC");

   foreach($my_posts as $post) : setup_postdata($post);
?>

   ~ Your "Loop" template tags and whatnot go here ~

   <?php endforeach; ?>

   For either option, just make sure to set the correct values for the three variables at the top of each code block: $catid, $customkey and $customvalue.

3. mylagoon
   Member
   Posted 3 years ago #

   Cheers Kafkaesqui, query_posts() method worked a charm.

4. degarantia
   Member
   Posted 2 years ago #

   I'm sorting the posts by custom field value and it shows all the posts.
   Is there any way to show only 10 posts and navigate through the rest of posts using "next_posts_link" or "previous_posts_link", just like a simple pagination?

   This is the code I'm using:

   <?php $pageposts = $wpdb->get_results("SELECT wposts.* FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta, $wpdb->post2cat wpost2cat WHERE wposts.ID = wpostmeta.post_id AND wposts.ID = wpost2cat.post_id AND wpost2cat.category_id = '1' AND wpostmeta.meta_key = 'POSICION' ORDER BY wpostmeta.meta_value , wpostmeta.meta_id DESC", OBJECT);
?>
<?php if ($pageposts) : foreach ($pageposts as $post): setup_postdata($post);?>
<?php include(TEMPLATEPATH."/includes/whatever.php"); ?>
<?php endforeach;?>
<?php endif; ?>

   Thanks a lot.

5. Kafkaesqui
   Moderator
   Posted 2 years ago #

   "Is there any way to show only 10 posts and navigate through the rest of posts using "next_posts_link" or "previous_posts_link", just like a simple pagination?"

   Not without a lot of work recreating WordPress' functionality. But I'll make the same offer as I do here:

   http://wordpress.org/support/topic/96851#post-483662

   My Custom Home plugin could be rewritten to work within category queries, but not in a Page (or category incorporating a template) using code like the SQL queries above.

Topic Closed

This topic has been closed to new replies.