WordPress Paginate $wpdb->get_results

by

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:

Leave a Comment