By uknowit2


2012-05-24 18:46:23 8 Comments

Due to a complex multisite config, I have a query which combines the posts from two blogs and I would like to paginate the results. I am grateful for any help. I have posted my query.

            $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 comments

@getWeberForStackExchange 2012-05-24 18:52:09

Update

I've tested this and it works on my site. A few things:

  • Replace my $query with yours
  • global $wpdb (per your comment regarding global variables) since it's out of scope!
  • get_results() returns an object when not told otherwise (second parameter is the return type)
  • I placed this in a plugin, but you could extract the code and place it in your theme or just put it in functions.php.

Here's the function:

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
    ));
}

Original Post

The paginate_links function is independent of your query. Given a few parameters, like the total number of items and the current page, it can provide the pagination that you're looking for. So you need to calculate:

  1. The total number of items
  2. The current page number, 1-based
  3. The offset for the mysql limit statement.

I was thinking something like this (untested, sorry!):

$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
));

References:

@uknowit2 2012-05-24 19:23:45

thanks, hmmm couldn't quite get that working with my query, I might be missing something.

@getWeberForStackExchange 2012-05-24 21:27:59

I've updated my answer to be more clear, with some pseudocode. Hopefully this works! If not, please comment! :)

@uknowit2 2012-05-25 08:25:16

Hey Weberwithoneb thanks for sticking with me on this one. I have tried the updated query but it brings up a blank, no errors, just no results. I can confirm that the query in my question does work. do I need to add any globals?

@getWeberForStackExchange 2012-05-25 16:57:21

Ok I've updated my answer with tested code. Please comment with questions!

@uknowit2 2012-05-25 20:37:10

Wow!! You are a GENIUS and I want to say a BIG THANK YOU!! Works like a dream!! Thanks again.

@getWeberForStackExchange 2012-05-25 22:45:39

Glad to hear it!!

@cmocha 2014-08-08 20:42:17

// 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>';
?>

Related Questions

Sponsored Content

1 Answered Questions

wpdb get_results() returns only 2 rows

4 Answered Questions

[SOLVED] get_results using wpdb

  • 2013-08-19 13:10:56
  • user1933824
  • 78361 View
  • 9 Score
  • 4 Answer
  • Tags:   database query wpdb

1 Answered Questions

[SOLVED] $wpdb is get_results escaped

  • 2018-06-12 00:56:59
  • tim92109
  • 278 View
  • 1 Score
  • 1 Answer
  • Tags:   wpdb security

2 Answered Questions

[SOLVED] Differences between wpdb->get_results() and wpdb->query()

  • 2014-10-23 14:32:42
  • emanuele
  • 1843 View
  • 5 Score
  • 2 Answer
  • Tags:   query wpdb

2 Answered Questions

[SOLVED] Paginate result set from $wpdb->get_results()

2 Answered Questions

[SOLVED] WordPress get pagination on wpdb get_results

1 Answered Questions

[SOLVED] $wpdb->get_results not returning an array

  • 2013-12-04 09:49:32
  • KingRichard
  • 1449 View
  • 0 Score
  • 1 Answer
  • Tags:   wpdb

2 Answered Questions

[SOLVED] Can't pass variable in wordpress wpdb->get_results

  • 2013-07-12 07:14:45
  • user35227
  • 6284 View
  • 0 Score
  • 2 Answer
  • Tags:   wpdb

2 Answered Questions

[SOLVED] Speed optimization of $wpdb->get_results

  • 2012-12-17 11:43:42
  • bumTomica
  • 1623 View
  • 0 Score
  • 2 Answer
  • Tags:   wpdb

Sponsored Content