WordPress分页$wpdb->获取结果

时间:2012-05-24 作者:uknowit2

由于一个复杂的多站点配置,我有一个查询,它将来自两个博客的帖子组合在一起,我想paginate the results. 我很感激你的帮助。我已经发布了我的查询。

            $latestposts = $wpdb->get_results(

            "
            (SELECT * FROM net_5_posts
            INNER JOIN net_5_term_relationships ON net_5_posts.ID=net_5_term_relationships.object_id  
            WHERE post_type = \'post\' 
            AND post_status = \'publish\' 
            AND term_taxonomy_id = \'151\' 
            )

            UNION ALL

            (SELECT * FROM net_7_posts
            INNER JOIN net_7_term_relationships ON net_7_posts.ID=net_7_term_relationships.object_id  
            WHERE post_type = \'post\' 
            AND post_status = \'publish\' 
            AND term_taxonomy_id = \'20\' 
            )

            ORDER BY post_date
            DESC LIMIT 5",\'ARRAY_A\');

            foreach ($latestposts as $latestpost) {

            $da_id = $latestpost[\'ID\'];
            $da_title = $latestpost[\'post_title\'];
            $da_content = strip_tags($latestpost[\'post_content\']);
            $da_content = limit_words($da_content,55);
            $da_link = $latestpost[\'guid\'];
            $da_date = $latestpost[\'post_date\'];
            $da_date = date(\'F j, Y\', strtotime($da_date));

            echo \'
            <div class="ldapost">
            <h2 class="lheader"><a href="\'.$da_link.\'">\'.$da_title.\'</a></h2>
            <span class="ldate">\'.$da_date.\'</span>
            <span class="lcontent">\'.$da_content.\'…</span><br>
            <a class="button btnright" href="\'.$da_link.\'">Continue Reading</a>
            </div>
            \';

            }

2 个回复
最合适的回答,由SO网友:getWeberForStackExchange 整理而成

更新我已经对此进行了测试,并在我的网站上运行。有几件事:

更换我的$query 用你的global $wpdb (根据您关于全局变量的评论)因为它超出了范围

  • get_results() 如果没有其他说明,则返回一个对象(第二个参数是返回类型)
  • 我将其放在插件中,但您可以提取代码并将其放在主题中,也可以将其放在functions.php.
  • 功能如下:

    function test_function() {
    
        global $wpdb;
    
        $query = "
            (SELECT * FROM wp_18_posts
            INNER JOIN wp_18_term_relationships ON wp_18_posts.ID=wp_18_term_relationships.object_id  
            WHERE post_type = \'post\' 
            AND post_status = \'publish\' 
            AND term_taxonomy_id = \'2\')
    
            UNION ALL
    
            (SELECT * FROM wp_17_posts
            INNER JOIN wp_17_term_relationships ON wp_17_posts.ID=wp_17_term_relationships.object_id  
            WHERE post_type = \'post\' 
            AND post_status = \'publish\' 
            AND term_taxonomy_id = \'2\')";
    
        $total_query = "SELECT COUNT(1) FROM (${query}) AS combined_table";
        $total = $wpdb->get_var( $total_query );
        $items_per_page = 1;
        $page = isset( $_GET[\'cpage\'] ) ? abs( (int) $_GET[\'cpage\'] ) : 1;
        $offset = ( $page * $items_per_page ) - $items_per_page;
        $latestposts = $wpdb->get_results( $query . " ORDER BY post_date LIMIT ${offset}, ${items_per_page}" );
    
        foreach ($latestposts as $latestpost) {
            $da_id = $latestpost->ID;
            $da_title = $latestpost->post_title;
            $da_content = strip_tags($latestpost->post_content);
            $da_content = wp_trim_words($da_content, 55);
            $da_link = $latestpost->guid;
            $da_date = $latestpost->post_date;
            $da_date = date(\'F j, Y\', strtotime($da_date));
    
            echo \'
            <div class="ldapost">
            <h2 class="lheader"><a href="\'.$da_link.\'">\'.$da_title.\'</a></h2>
            <span class="ldate">\'.$da_date.\'</span>
            <span class="lcontent">\'.$da_content.\'…</span><br>
            <a class="button btnright" href="\'.$da_link.\'">Continue Reading</a>
            </div>
            \';
        }
    
        echo paginate_links( array(
            \'base\' => add_query_arg( \'cpage\', \'%#%\' ),
            \'format\' => \'\',
            \'prev_text\' => __(\'&laquo;\'),
            \'next_text\' => __(\'&raquo;\'),
            \'total\' => ceil($total / $items_per_page),
            \'current\' => $page
        ));
    }
    
    原始帖子的paginate\\u links功能独立于您的查询。给定一些参数,如项目总数和当前页面,它可以提供您要查找的分页。因此,您需要计算:

    项目总数当前页码,基于1的mysql limit语句的偏移量我在想这样的事情(未经测试,抱歉!):

    $query = "
        (SELECT * FROM net_5_posts
        INNER JOIN net_5_term_relationships ON net_5_posts.ID=net_5_term_relationships.object_id  
        WHERE post_type = \'post\' 
        AND post_status = \'publish\' 
        AND term_taxonomy_id = \'151\' 
        )
    
        UNION ALL
    
        (SELECT * FROM net_7_posts
        INNER JOIN net_7_term_relationships ON net_7_posts.ID=net_7_term_relationships.object_id  
        WHERE post_type = \'post\' 
        AND post_status = \'publish\' 
        AND term_taxonomy_id = \'20\' 
        )";
    
    $total = $wpdb->get_var( "SELECT COUNT(1) FROM (${query}) AS combined_table" );
    $items_per_page = 5;
    $page = isset( $_GET[\'cpage\'] ) ? abs( (int) $_GET[\'cpage\'] ) : 1;
    $offset = ( $page * $items_per_page ) - $items_per_page;
    $latestposts = $wpdb->get_results( $query . " ORDER BY post_date LIMIT ${offset}, ${items_per_page}" );
    
    foreach ($latestposts as $latestpost) {
        // Your code here ...
    }
    
    echo paginate_links( array(
        \'base\' => add_query_arg( \'cpage\', \'%#%\' ),
        \'format\' => \'\',
        \'prev_text\' => __(\'&laquo;\'),
        \'next_text\' => __(\'&raquo;\'),
        \'total\' => ceil($total / $items_per_page),
        \'current\' => $page
    ));
    
    参考文献:

    SO网友:cmocha
    // This worked great for me so much thanks! I just adapted for what I needed. Right in template file, Sweet!
    global $wpdb;
    // QUERY HERE TO COUNT TOTAL RECORDS FOR PAGINATION $total = $wpdb->get_var("SELECT COUNT(*)
    $post_per_page = 10;
    $page = isset( $_GET[\'cpage\'] ) ? abs( (int) $_GET[\'cpage\'] ) : 1;
    $offset = ( $page * $post_per_page ) - $post_per_page;
    
    // QUERY HERE TO GET OUR RESULTS $results = $wpdb->get_results
    
    // PHP FOR EACH LOOP HERE TO DISPLAY OUR RESULTS
    // END OUR FOR EACH LOOP
    
    // PAGINATION HERE IN NICE BOOTSTRAP STYLES
    <?php 
    echo \'<div class="pagination">\';
    echo paginate_links( array(
    \'base\' => add_query_arg( \'cpage\', \'%#%\' ),
    \'format\' => \'\',
    \'prev_text\' => __(\'&laquo;\'),
    \'next_text\' => __(\'&raquo;\'),
    \'total\' => ceil($total / $post_per_page),
    \'current\' => $page,
    \'type\' => \'list\'
    ));
    echo \'</div>\';
    ?>
    
    结束

    相关推荐

    避免WP_QUERY的带有分类的重复帖子

    给定一个包含多个类别(如“运动”、“自然”等)的博客,然后我开始创建一个自定义分类法,它允许我在博客的某些区域显示文章,并使用诸如“幻灯片放映”、“侧边栏突出显示”、“顶级类别”等术语。在我的侧边栏中,我创建了一个区域,使用两个不同的循环显示文章(来自同一类别,比如“Nature”)。第一篇文章只显示了一篇文章(第一篇按时间顺序排列,来自自然类别,也在“侧栏突出显示”分类法的术语内),带有特色图片、标题和摘录,而第二篇文章只显示了自然类别中最近的3篇文章。为了避免重复,我一直在使用以下代码: