WordPress wpdb get_results 自定义查询分页不显示

用Wordpress建的网站，自定义了一个投票表，需要按投票结果进行排序，但是文章只能显示第一页，分页按钮不显示。

function spiciest(){
global $wpdb, $paged, $max_num_pages;//全局变量

$paged = (get_query_var('paged')) ? get_query_var('paged') : 1;
$post_per_page = intval(get_query_var('posts_per_page')); //6
$offset = ($paged - 1)*$post_per_page;
/* 自定义sql，codebye.com 原创首发 */
$sql = "
    SELECT DISTINCT * FROM $wpdb->posts
    INNER JOIN (SELECT *, SUBSTRING(name, 6) as 'post_ID',
    votes_up  AS votes_balance,
    votes_up + votes_down AS votes_total
    FROM thumbsup_items) AS thumbsup
    ON $wpdb->posts.ID = thumbsup.post_ID
    WHERE $wpdb->posts.post_status = 'publish'
    AND $wpdb->posts.post_type = 'post'
    AND $wpdb->posts.post_password = ''
    ORDER BY votes_up DESC, votes_balance DESC
    LIMIT ".$offset.", ".$post_per_page."; ";

$sql_result = $wpdb->get_results( $sql, OBJECT);

/* 查出文章总数，计算最大页数 */
$sql_posts_total = $wpdb->get_var( "SELECT FOUND_ROWS();" );
$max_num_pages = ceil($sql_posts_total / $post_per_page);

print_r("offset ". $offset."\n") ;
print_r("\n"."sql_posts_total ". $sql_posts_total."\n") ;
print_r("\n"."max_num_pages ". $max_num_pages."\n") ;
return $sql_result;
}
经过调查，问题原因是Limit，只查出来第一页，而不是所有数据。解决方法如下：

function spiciest(){
global $wpdb, $paged, $max_num_pages;

$paged = (get_query_var('paged')) ? get_query_var('paged') : 1;
$post_per_page = intval(get_query_var('posts_per_page')); //6
$offset = ($paged - 1)*$post_per_page;


// 查询所有文章
$query_spicy = "
    SELECT DISTINCT * FROM $wpdb->posts
    INNER JOIN (SELECT *, SUBSTRING(name, 6) as 'post_ID',
    votes_up  AS votes_balance,
    votes_up + votes_down AS votes_total
    FROM thumbsup_items) AS thumbsup
    ON $wpdb->posts.ID = thumbsup.post_ID
    WHERE $wpdb->posts.post_status = 'publish'
    AND $wpdb->posts.post_type = 'post'
    AND $wpdb->posts.post_password = ''
    ORDER BY votes_up DESC, votes_balance DESC";


//分页查询文章
$spicy = $query_spicy . " LIMIT ".$offset.", ".$post_per_page."; ";

$spicy_results = $wpdb->get_results( $spicy, OBJECT);

// 查询 然后计数
$total_result = $wpdb->get_results( $query_spicy, OBJECT);


$total_spicy_post = count($total_result);
$max_num_pages = ceil($total_spicy_post / $post_per_page);


return $spicy_results;
}

至此，已经可以在前台，调用显示排序后的分页文章了。比如：

<?php
 $spiciest = spiciest();

 if ($spiciest):
    global $post;
    foreach ($spiciest as $post) :
        setup_postdata($post);
?>

/**** 在这里放置模板的标签,使用方法类似其他显示文章的模板 *****/
<?php
    endforeach;
endif;

?>

<code><span class="pun"> </span></code>

文章评论已关闭！