Make a database query using WordPress

Question

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?

birgire · Accepted Answer · 2013-02-07 03:31:49Z

1

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

edited Feb 7, 2013 at 3:31

answered Feb 7, 2013 at 3:24

birgire

68.1k7 gold badges121 silver badges255 bronze badges

Add a comment |

Milo · Accepted Answer · 2013-02-07 03:25:42Z

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'];

Stack Exchange Network

Make a database query using WordPress

2 Answers 2

Your Answer

Hot Network Questions

Make a database query using WordPress

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions