Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I'm sure this question is asked alot, but I cannot figure it out, since all the different ways to query posts are confusing me..

Here is my current code.
How can I tell the code to only get posts from category ID #22?

<?php 
$querystr = " 
    SELECT post_id, AVG(meta_value) AS total, COUNT(post_id) as num_karakter
    FROM sk_postmeta
    WHERE meta_key = 'krit_karakter'
    GROUP BY post_id
    ORDER BY total DESC
    LIMIT 5
    "; 
$pageposts = $wpdb->get_results($querystr, OBJECT); 

if ($pageposts) { ?>
    <ul class="smartbar-items">
    <?php foreach ($pageposts as $post): 
        setup_postdata($post); ?> 
        <li><a href="<?php echo get_permalink($post->post_id);?>"><img src="<?php bloginfo('url');?>/wp-content/files_mf/<?php echo get_post_meta($post->post_id, 'game_poster', true); ?>" width="80" height="110" alt="<?php echo get_the_title($post->post_id);?>" />  <br /><?php echo get_the_title($post->post_id);?></a></li>
    <?php  endforeach; ?>
    </ul>

Thanks for all help!

share|improve this question
    
Why count the post_id column when you're setting the query limit? The count will always be 5 unless the result set is smaller.. Why not use WP_Query instead and perform your averaging and counting on the returned data? You'd then not have any need to be writing the SQL by hand.. –  t31os Nov 11 '10 at 14:34

2 Answers 2

up vote 1 down vote accepted

I would do this in two pieces.

  1. Add this into your functions.php file to grab custom fields globally:

    function get_custom_field($key, $echo = FALSE) {
    global $post;
    $custom_field = get_post_meta($post->ID, $key, true);
    if ($echo == FALSE) return $custom_field;
    echo $custom_field;
    

    }

  2. Build your query as follows:

I can't seem to get markdown to play nice, so here's the code in Pastie http://pastie.org/1294594

share|improve this answer
    
Thank you! I think this will be the best solution for my use.. :) –  Martin-Al Nov 15 '10 at 23:47
    
How can I limit the output based on the number of 'krit_karakter'? I want to only show posts that have 5 or more entries in "krit_karakter". Is this possible? –  Martin-Al Nov 16 '10 at 20:43

You would need to join in the sk_term_relationships table.

$querystr = " 
 SELECT post_id, AVG(meta_value) AS total, COUNT(post_id) as num_karakter
 FROM sk_postmeta p
 inner join sk_term_relationship t on p.post_id = t.object_id 
  and t.term_taxonomy_id = 22
 WHERE meta_key = 'krit_karakter'
 GROUP BY post_id
 ORDER BY total DESC
 LIMIT 5
"; 
share|improve this answer
    
Hmm, I thought it was something like that. I tried on my local installation, and it gave no output. When I tried on the live installation, it only gave me a blank page... Why? –  Martin-Al Nov 4 '10 at 22:27
1  
Could you verify in sk_term_taxonomy what is the term_taxonomy_id of the category #22? –  Wadih M. Nov 4 '10 at 22:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.