WordPress custom pagination with $wpdb->get_result

Question

I was able to get custom data from Wordpress database by $wpdb->get_results like so $results = $wpdb->get_results( $query, OBJECT ); However i would like to paginate the data using paginate_links();

Which is presently displaying no data with pagination links, I think my error is probably within $results = $wpdb->get_results( $query.'ORDER BY id DESC LIMIT'. $offset.', '. $items_per_page, OBJECT );

My code:

    global $wpdb;
    $table_name = $wpdb->prefix . 'templates';
    $items_per_page = 3;
    $offset = ( $page * $items_per_page ) - $items_per_page;

    $page = isset( $_GET['cpage'] ) ? abs( (int) $_GET['cpage'] ) : 1;

    $query = 'SELECT * FROM '.$table_name;

    $total_query = "SELECT COUNT(1) FROM (${query}) AS combined_table";

    $total = $wpdb->get_var( $total_query );


    $results = $wpdb->get_results( $query.'ORDER BY id DESC LIMIT'. $offset.', '. $items_per_page, OBJECT );

    $results = $wpdb->get_results( $query, OBJECT );

    if(!empty($results)) {
    echo"<table class=\"table table-hover\">";
        echo"<thead>";
            echo"<tr>";
                echo"<th>Id</th>";  
                echo"<th>Date</th>";
                echo"<th>Name</th>";
                echo"<th>Image src</th>";
                echo"<th>Category</th>";
                echo"<th>Preview Link</th>";
                echo"<th>BuiltWith</th>";
                echo"<th>Price</th>";

            echo"</tr>";
        echo"</thead>";
        echo"<tbody>";
        foreach($results as $row){  


            echo"<tr>";
                echo"<td>". $row->id . "</td>";
                echo"<td>". $row->tdateTime ."</td>";
                echo"<td>". $row->tName ."</td>";
                echo"<td>". $row->tName ."</td>";
                echo"<td>". $row->tCategory ."</td>";
                echo"<td>". $row->tPreview ."</td>";
                echo"<td>". $row->tBuiltWith . "</td>";
                echo"<td>". $row->tPrice ."</td>";
            echo"</tr>";
        }

        echo"</tbody>";
    echo"</table>";

    }


    echo paginate_links( array(

        'base' => add_query_arg( 'cpage', '%#%' ),

        'format' => '',

        'prev_text' => __('&laquo;'),

        'next_text' => __('&raquo;'),

        'total' => ceil($total / $items_per_page),

        'current' => $page

    ));

Answer 1

Try this code

<?php

    ob_start();
    $pagenum = isset( $_GET['pagenum'] ) ? absint( $_GET['pagenum'] ) : 1;

    global $wpdb;
    $table_name = $wpdb->prefix . "templates";


    $limit = 10;
    $offset = ( $pagenum - 1 ) * $limit;

    $total = $wpdb->get_var("SELECT COUNT(id) FROM $table_name ");              

    $num_of_pages = ceil( $total / $limit );

    ?>

    <?php   
        $questionnaire_data = $wpdb->get_results("SELECT * FROM $table_name desc LIMIT $offset, $limit", OBJECT );          
        $rowcount = $wpdb->num_rows;            

    ?>
        <h1>Result</h1>     

        <table class="wp-list-table widefat fixed striped" style="width: 99%;">
            <tr>
                <thead>
                    <th style="width: 5%;"><strong>S.No</strong></th>                       
                </thead>
            </tr>
            <tbody>
            <?php 
                if($rowcount ) {
                    $i=1;                           

                    foreach ($questionnaire_data as $key=>$singledata) {    
                        echo "<tr class='no-items'>";
                        echo "<td>".$i."</td>";                         
                        echo "</tr>";
                        $i++;
                    }
                } else {
                    echo "<td colspan='4' align='center'> No details(s) found </td>";
                }
            ?>
            </tbody>            
            <tfoot>
                <tr>
                    <th style="width: 5%;"><strong>S.No</strong></th>                       
                </tr>
            </tfoot>        
        </table>

    <?php
        $page_links = paginate_links( array(
            'base' => add_query_arg( 'pagenum', '%#%' ),
            'format' => '',
            'prev_text' => __( '&laquo;', 'text-domain' ),
            'next_text' => __( '&raquo;', 'text-domain' ),
            'total' => $num_of_pages,
            'current' => $pagenum
        ) );

        if ( $page_links ) {
            echo '<div class="tablenav" style="width: 99%;"><div class="tablenav-pages" style="margin: 1em 0">' . $page_links . '</div></div>';
        }
    }

Answer 2

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)

Here's the code:

<?php
global $wpdb;
    $table_name = $wpdb->prefix . 'templates';
    $query = "(SELECT * FROM '.$table_name)";

    $total_query = "SELECT COUNT(1) FROM (${query}) AS combined_table";
    $total = $wpdb->get_var( $total_query );

    $items_per_page = 3;
    $page = isset( $_GET['cpage'] ) ? abs( (int) $_GET['cpage'] ) : 1;
    $offset = ( $page * $items_per_page ) - $items_per_page;

    $results = $wpdb->get_results( $query . " ORDER BY post_date LIMIT ${offset}, ${items_per_page}" );

  if(!empty($results)) {
    echo"<table class=\"table table-hover\">";
        echo"<thead>";
            echo"<tr>";
                echo"<th>Id</th>";
                echo"<th>Date</th>";
                echo"<th>Name</th>";
                echo"<th>Image src</th>";
                echo"<th>Category</th>";
                echo"<th>Preview Link</th>";
                echo"<th>BuiltWith</th>";
                echo"<th>Price</th>";

            echo"</tr>";
        echo"</thead>";
        echo"<tbody>";
        foreach($results as $row){


            echo"<tr>";
                echo"<td>". $row->id . "</td>";
                echo"<td>". $row->tdateTime ."</td>";
                echo"<td>". $row->tName ."</td>";
                echo"<td>". $row->tName ."</td>";
                echo"<td>". $row->tCategory ."</td>";
                echo"<td>". $row->tPreview ."</td>";
                echo"<td>". $row->tBuiltWith . "</td>";
                echo"<td>". $row->tPrice ."</td>";
            echo"</tr>";
        }

        echo"</tbody>";
    echo"</table>";

    }
    
    echo paginate_links( array(
        'base' => add_query_arg( 'cpage', '%#%' ),
        'format' => '',
        'prev_text' => __('&laquo;'),
        'next_text' => __('&raquo;'),
        'total' => ceil($total / $items_per_page),
        'current' => $page
    ));
    ?>