WordPress Paginate $wpdb->get_results

2 comments

1. 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' => __('«'),
       'next_text' => __('»'),
       'total' => ceil($total / $items_per_page),
       'current' => $page
   ));
   

   References:

   • This answer about pagination.
   • This StackOverflow answer about counting in MySQL unions.

   Log in to Reply

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

   Log in to Reply