Using WP_Query or not to get results from custom tables for pagination - Wordpress

Question

I have several custom tables that I use for various information that is not related to Wordpress per se. Such as, I have a custom table that I use for certain images.

The custom table is below:

id(int11) img_link(text) img_desc(text) img_wk(text) img_wishtype(text) img_dateadded(datetime)

This table will eventually have hundreds of rows, I would like to implement pagination. I am unsure of if it is possible to:

How can I use WP_Query to do this and include pagination
If can't use WP_Query, how can I paginate the results to show only say 20 per page?

Below is my code that I tried and failed with. I've viewed several other questions that are similar, but most of them used a posttype. Which would be fine, I just don't know how to declare posttypes or how they would coincide with my custom table.

global $wpdb;
$per_page = 5;
$page = isset( $_GET['cpage'] ) ? abs( (int) $_GET['cpage'] ) : 1;
if ($page > 1) {
    $offset = $page * $per_page - $per_page;
} else {
    $offset = $page;
}
$the_post = "SELECT img_link,img_wk FROM custom_table ORDER BY id DESC LIMIT $per_page offset $offset";
$details = $wpdb->get_results($the_post, ARRAY_A);
//do foreach to display your post details.
print_r($details);
echo '<br>';
$output = '';
foreach($details as $livewish){
    $output .= $livewish['img_link'].'======'.$livewish['img_wk'].'<br>';
}


$total = $wpdb->get_var("SELECT img_link FROM custom_table ORDER BY id DESC");
$output .= paginate_links(array(
    'base' => add_query_arg('cpage', '%#%'),
    'format' => '',
    'prev_text' => __('&laquo;'),
    'next_text' => __('&raquo;'),
    'total' => ceil($total / $per_page),
    'current' => $page
));

The above code doesn't really work. For some reason it only outputs one row, when I have two rows for testing on. I did notice that if I change ARRAY_A to OBJECT the pagination part works, but the SQL breaks. When I change it back to ARRAY_A I only get one row of results, when there should be two.

score 1 · Answer 1 · answered Mar 28 '17 at 16:11

I think rewriting the query as following should suffice.

$limit = $wpdb->prepare("LIMIT %d, %d", $per_page, $offset); // I'd use the builtin method to escape untrusted values.
$the_post = "SELECT img_link,img_wk FROM custom_table ORDER BY id DESC LIMIT {$limit}"; // And then just concatenating parts...
$details = $wpdb->get_results($the_post, ARRAY_A);

If you pass OBJECT as the second argument, then the query will return rows as objects. So you need to access your values as following-

foreach($details as $livewish){
    $output .= $livewish->img_link . '======' . $livewish->img_wk.'<br>';
}

And also I think you are calculating total wrong. This should be-

$total = $wpdb->get_var("SELECT COUNT(*) FROM custom_table");

Adding these updates to my code did not produce results. However, this did open my eyes. I moved my code to a template file, and updated it. So thanks for the assist! :) — semaj0, Mar 28 '17 at 17:18

score 1 · Accepted Answer · edited May 23 '17 at 11:54

So thanks to @maksbd19, and this post I was able to see the issue. I think a big part of it was I was doing it first as a function that was a shortcode. I moved this to a template, updated the pagination args and updated my html.

<?php

        global $wpdb;
        $pagenum = isset( $_GET['pagenum'] ) ? absint( $_GET['pagenum'] ) : 1;
        $limit = 1;
        $offset = ($pagenum-1) * $limit;
        $total = $wpdb->get_var( "SELECT COUNT(*) FROM custom_table" );
        $num_of_pages = ceil( $total / $limit );

        $qry="select img_link, img_alt from custom_table LIMIT $offset, $limit";
        $result=$wpdb->get_results($qry, object);
        //print_r($result);
        if($result):
            foreach($result as $row){
                echo '<div class="col-md-4 featured-image-holder"><img src="'. $row->img_link .'" title="'. $row->img_title .'" alt="'. $row->img_alt .'" style="width:100%;" class="img-responsive img-rounded tbWow fadeIn" /></div>';
            }

            //Link for Pagination

            $page_links = paginate_links( array(
                'base'               => add_query_arg( 'pagenum', '%#%' ),
                'format'             => '',
                'prev_text'          => __( '&laquo;', 'aag' ),
                'next_text'          => __( '&raquo;', 'aag' ),
                'total'              => $num_of_pages,
                'current'            => $pagenum,               
                'base'               => add_query_arg( 'pagenum', '%#%' ),
                'format'             => '',
                'prev_next'          => true,
                'prev_text'          => __( '&larr;', 'aag' ),
                'next_text'          => __( '&rarr;', 'aag' ),
                'before_page_number' => '<li><span class="page-numbers btn btn-pagination btn-tb-primary">',
                'after_page_number'  => '</span></li>'
            ) );
            if ( $page_links ) {
                ?>
                <br class="clear">
            <nav id="archive-navigation" class="paging-navigation tbWow fadeInUp" role="navigation" style="visibility: visible; animation-name: fadeInUp;">
                <ul class="page-numbers">
                    <?php echo $page_links; ?>
                </ul>
            </nav>
        <?php   }
        endif;

       ?>

Using WP_Query or not to get results from custom tables for pagination - Wordpress

2 Answers2