Adding Pagination to a Custom Select Query

Wouldn’t it be easier to use WP_Query?

$args = array(
    'posts_per_page' => 20,
    'order_by' => 'meta_value',
    'meta_key' => 'top',
    'paged' => get_query_var('paged')
);

$query = new WP_Query( $args );

The query above should be equivalent to yours but with proper pagination and 20 results per page. If you do use WP_Query make sure to add wp_reset_postdata(); after your wp_pagenavi(); call as recommended by the author of that plugin.

I’m not that experienced when it comes to doing queries, (I mainly just go go through this codex page). The query I gave:

<?php
$args = array(
    'posts_per_page' => 20,
    'order_by' => 'meta_value',
    'meta_key' => 'top',
    'paged' => get_query_var('paged')
);

$my_query = new WP_Query( $args );
?>

would essentially replace this part:

<?php
$querystr = "
    SELECT $wpdb->posts.*
    FROM $wpdb->posts, $wpdb->postmeta
    WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id
    AND $wpdb->postmeta.meta_key = 'top'
    AND $wpdb->posts.post_status = 'publish'
    AND $wpdb->posts.post_type = 'post'
    AND $wpdb->posts.post_date < NOW()
    ORDER BY ABS($wpdb->postmeta.meta_value) DESC
 ";
 $pageposts = $wpdb->get_results($querystr, OBJECT);
?>

Then to start the loop you would do something like:

<?php
while ( $my_query->have_posts() ) { $my_query->the_post();

...all the bits to display your posts (the_title(); the_content(); and all those sorts of things)...

}
?>

And then, if you’re using that plugin you mentioned, you would probably call wp_reset_postata(); after using it. For example:

wp_pagenavi( array( 'query' => $my_query ) );

wp_reset_postdata();

*I’m not familiar with the use of that plugin; I’m just going by what the author of it says here.

If you want to post your entire query and loop into a pastebin, I could look it over for any obvious weirdness.

I don’t believe you need the ‘order’ => ‘desc’ argument since that’s what it automatically defaults to. You used ‘orderby’ correctly, except for the single quotes around ‘meta_value_num’. As far as the pagination, I always have problems with it. Try it with ‘page’ instead, like this:

$args = array(
    'posts_per_page' => 20,
    'orderby' => 'meta_value_num',
    'meta_key' => 'top',
    'paged' => get_query_var( 'page' )
);

There’s another way to try if it still gives you problems with the order. If the pagination doesn’t work with get_query_var( 'paged' ) or get_query_var( 'page' ), I’ll look for something else to try; I really hope it’s not just a problem with the plugin.

What if I want to paginate Category lists. Maybe from all the category or the child categories of a parent category?