WordPress.org

1. not_sean
   Member
   Posted 1 year ago #

   I am a newbie with WordPress and am trying to modify the standard SQL call for for custom field data posts to limit by category. Here is what I am basing my attempts on, but i just can't figure out how to filter for category 15.

   $querystr = "
   SELECT wposts.*
   FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
   WHERE wposts.ID = wpostmeta.post_id
   AND wpostmeta.meta_key = 'priority'
   AND wposts.post_type = 'post'
   AND wposts.post_status = 'publish'
   AND wposts.
   ORDER BY wpostmeta.meta_value ASC
   ";

2. vtxyzzy
   Member
   Posted 1 year ago #

   I think this is what you want:

   $querystr = "
   SELECT wposts.*
   FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
   JOIN $wpdb->term_relationships tr ON (tr.object_id = wposts.ID)
   JOIN $wpdb->term_taxonomy tt ON (tt.term_taxonomy_id = tr.term_taxonomy_id)
   WHERE wposts.ID = wpostmeta.post_id
   AND wpostmeta.meta_key = 'priority'
   AND wposts.post_type = 'post'
   AND wposts.post_status = 'publish'
   AND tt.taxonomy = 'category'
   AND tt.term_id = 15
   ORDER BY wpostmeta.meta_value ASC
   ";

3. not_sean
   Member
   Posted 1 year ago #

   That responds with a blank page

   My code looks like this:

   <?php 
   
   $querystr = "
   SELECT wposts.*
   FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
   JOIN $wpdb->term_relationships tr ON (tr.object_id = wposts.ID)
   JOIN $wpdb->term_taxonomy tt ON (tt.term_taxonomy_id = tr.term_taxonomy_id)
   WHERE wposts.ID = wpostmeta.post_id
   AND wpostmeta.meta_key = 'priority'
   AND wposts.post_type = 'post'
   AND wposts.post_status = 'publish'
   AND tt.taxonomy = 'category'
   AND tt.term_id = 7
   ORDER BY wpostmeta.meta_value ASC
   ";
   
   $pageposts = $wpdb->get_results($querystr, OBJECT);
   
   echo( $pageposts);
   print_r( $pageposts);
   
   if ($pageposts)
   {
   	foreach ($pageposts as $post)
   	{
   		setup_postdata($post);
   		?>
   	<div class="team_member">
           <h2><?php the_title(); ?></h2>
           <?php the_content(); ?>
       </div>
   <?php
   	}
   }
   ?>

   echo( $pageposts); = "Array"
   print_r( $pageposts); = "Array( )"

4. vtxyzzy
   Member
   Posted 1 year ago #

   My mistake. I forgot that you can't mix regular FROM clauses with JOINs. Please try this:

   <?php 
   
   $querystr = "
   SELECT wposts.*
   FROM $wpdb->posts wposts
   JOIN $wpdb->postmeta wpostmeta ON (wpostmeta.post_id = wposts.ID)
   JOIN $wpdb->term_relationships tr ON (tr.object_id = wposts.ID)
   JOIN $wpdb->term_taxonomy tt ON (tt.term_taxonomy_id = tr.term_taxonomy_id)
   WHERE wpostmeta.meta_key = 'priority'
   AND wposts.post_type = 'post'
   AND wposts.post_status = 'publish'
   AND tt.taxonomy = 'category'
   AND tt.term_id = 7
   ORDER BY wpostmeta.meta_value ASC
   ";

5. not_sean
   Member
   Posted 1 year ago #

   Thank you! That works perfectly

Topic Closed

This topic has been closed to new replies.