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 am working on a Wordpress theme that has a homepage slider. I have a table in Wordpress database that stores post view counts and I would like to make my slider show the top viewed posts.

This is the SQL to get the top post, Post ID's

SELECT `post_id`, `single_views`
FROM `wp_ak_popularity`
ORDER BY `single_views`
DESC LIMIT 30

And below is my current Wordpress DB query that is currently in use.

$featured = new WP_Query(
    array(
        'no_found_rows'             => TRUE,
        'update_post_meta_cache'    => FALSE,
        'update_post_term_cache'    => FALSE,
        'ignore_sticky_posts'       => 1,
        'posts_per_page'            => wpb_option('featured-slider-number'),
        'cat'                       => wpb_option('featured-slider-category')
    )
);

I was looking at the WordPress documentation site and saw that I could use something like this to only query the posts that I need...

$featured  = new WP_Query( 
    array( 
        post_type' => 'post',
        'post__in' => array( 2, 5, 12, 14, 20 )
    )
);

Where I need help is how I can make the first Database query and get those results into a variable like this

$post_ids = '2, 5, 12, 14, 20';

So I can then make the main Database query by simply passing in the Post ID's that I need.

Can someone show me how to get the first list of ID's?

share|improve this question

2 Answers 2

up vote 1 down vote accepted

if I understand you correctly, you might want something like this:

global $wpdb;

$number=30; // EDIT this to your needs

$sql="SELECT post_id
FROM `wp_ak_popularity`
ORDER BY `single_views`
DESC LIMIT %d";

$my_top_posts = $wpdb->get_results($wpdb->prepare($sql,$number),ARRAY_N);

to get an array of the top posts id's.

You could then use this in your WP_Query:

'post__in' => $my_top_posts
share|improve this answer

Have a look at the wpdb class in Codex, this is what you should be using to run your SQL queries.

First, you should be using the $wpdb->prefix with your table name. The prefix can be changed by the user, and multiple sites can share the same database with different prefixes.

So here we query the database and then loop over the results, putting the post ids in an array. Note that in your example, creating a string like $post_ids = '2, 5, 12, 14, 20'; wouldn't work unless you first exploded it into an array.

$table = $wpdb->prefix . 'ak_popularity'
$query = "SELECT post_id, single_views
          FROM $table
          ORDER BY single_views DESC LIMIT 30";
$results = $wpdb->get_results( $query, ARRAY_A );
$post_ids = array();
foreach( $results as $row )
    $post_ids[] = $row['post_id'];
share|improve this answer

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.