WordPress在wpdb GET_RESULTS上获取分页

时间：2015-06-05 作者：Ohsik

如何对自定义wpdb结果进行编号分页？

下面的代码将显示站点中每个作者的一篇最新帖子。我想用编号分页的方式显示每页20篇文章。

$postids = $wpdb->get_results( 
    "
     SELECT a.ID, a.post_title, a.post_author, a.post_date
     FROM $wpdb->posts a
     JOIN (
              SELECT max(post_date) post_date, post_author
                FROM $wpdb->posts
              WHERE post_status = \'publish\' 
                AND post_type = \'post\'
              GROUP BY post_author
          ) b ON a.post_author = b.post_author AND a.post_date = b.post_date
     WHERE post_status = \'publish\' 
           AND post_type = \'post\'
     ORDER BY post_date DESC
    "
);

foreach ( $postids as $postid ) 
{
    //setup_postdata( $postid );
    echo $postid->ID." :: ".$postid->post_title." :: ".$postid->post_author . "<br />" ;
    //get_template_part( \'content\', \'category\' );
}

2 个回复
SO网友:willrich33

你在自己的回答中说“然而，我知道这不是一个好办法”。我可以添加一件事来回答您的问题并使用您的答案，那就是，您可以将SQL\\u CALC\\u FOUND\\u行与$wpdb一起使用

$result = $wpdb->get_results( 
    "SELECT SQL_CALC_FOUND_ROWS * FROM `wp_table` WHERE 1 LIMIT 10;"
);
$total_count = $wpdb->get_var(
    "SELECT FOUND_ROWS();"
);
这样，您就不必运行两次查询。

SO网友:Ohsik

所以我最终得到了我想用下面的代码做的事情。然而，我知道这不是一个好办法。请随时分享您对此代码的建议。

global $wpdb;
global $post;

// Pagination Setup
$posts_per_page = 20;
$start = 0;
$paged = get_query_var( \'paged\') ? get_query_var( \'paged\', 1 ) : 1; // Current page number
$start = ($paged-1)*$posts_per_page;

$num_of_total_posts = $wpdb->get_results( 
    "
     SELECT a.ID, a.post_title, a.post_author, a.post_date
     FROM $wpdb->posts a
     JOIN (
              SELECT max(post_date) post_date, post_author
                FROM $wpdb->posts
              WHERE post_status = \'publish\' 
                AND post_type = \'post\'
              GROUP BY post_author
          ) b ON a.post_author = b.post_author AND a.post_date = b.post_date
     WHERE post_status = \'publish\' 
           AND post_type = \'post\'
     ORDER BY post_date DESC
    "
);
$total_posts = $wpdb->num_rows; // Get Total number of posts


$postids = $wpdb->get_results( 
    "
     SELECT a.ID, a.post_title, a.post_author, a.post_date, a.post_name
     FROM $wpdb->posts a
     JOIN (
              SELECT max(post_date) post_date, post_author
                FROM $wpdb->posts
              WHERE post_status = \'publish\' 
                AND post_type = \'post\'
              GROUP BY post_author
          ) b ON a.post_author = b.post_author AND a.post_date = b.post_date
     WHERE post_status = \'publish\' 
           AND post_type = \'post\'
     ORDER BY post_date DESC
     LIMIT $start, $posts_per_page
    "
);

// Loop content
foreach ( $postids as $post ):
    setup_postdata($post);
    get_template_part( \'content\', \'category\' );
endforeach;


// Display Pagination
$total_page = ceil( $total_posts / $posts_per_page); // Calculate Total pages

$prev_arrow = is_rtl() ? \'&rarr;\' : \'&larr;\';
$next_arrow = is_rtl() ? \'&larr;\' : \'&rarr;\';

global $wp_query;
$big = 999999999; // need an unlikely integer
if( $total > 1 )  {
     if( !$current_page = get_query_var(\'paged\') )
         $current_page = 1;
     if( get_option(\'permalink_structure\') ) {
         $format = \'page/%#%/\';
     } else {
         $format = \'&paged=%#%\';
     }
    echo paginate_links(array(
        \'base\'          => str_replace( $big, \'%#%\', esc_url( get_pagenum_link( $big ) ) ),
        \'format\'        => $format,
        \'current\'       => max( 1, get_query_var(\'paged\') ),
        \'total\'         => $total_page,
        \'mid_size\'      => 3,
        \'type\'          => \'list\',
        \'prev_text\'     => $prev_arrow,
        \'next_text\'     => $next_arrow,
     ) );
}

结束
标签： php   pagination   mysql   wpdb   小码农  

相关推荐

如何使用php回显头部中的meta标签

我需要在标题中显示一个元标记，用于单数自定义帖子类型“通信”，如下所示：<meta name=\"citation_title\" content=\"single post title is here\" /> 我尝试使用此代码：<?php if ( is_singular( \'communications\' ) ) { echo \'<meta name=\"citation_title\" content=\"\' . the_title