I want a pagination to my options page of wordpres

Question

MY DEMO

I want a pagination according to the results coming from wordpress database...This is all done on my options page of wordpress plugin..

My code to retrieve from database is as follows

$per_page=5;
$sql = "SELECT * FROM wp_dive ";
$result = $wpdb->get_results($sql_10) or die(mysql_error());
$length=count($result);
$pages = ceil($length/$per_page);
foreach( $result as $results ) 

    {
    $id=$results->id;
    $name= $results->name_cust;
    $gender= $results->gender_cust;
    $dob= $results->dob_cust;

<?php $html= "<div class=\"divContentBody\">";?>
<?php $html .= "<span class=\"clsOrderNo\">". $id."</span>";?>
<?php $html .= "<span class=\"clsName\">". $name."</span>";?>
<?php $html .= "<span class=\"clsGender\">".$gender."</span>";?>
<?php $html .= "<span class=\"clsDOB\">".  $dob ."</span>";?>
<?php $html .= "</div>"?>
<?php
$data_html .=$html; 
 }

 ?> 

I m getting the data dynamically ..I just want to add pagination ..to show 5 entries on first page and accordingly ...

Answer 1

Take this https://stackoverflow.com/a/16358219/1596547 and adopt it to your needs like:

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

$limit = 5; // number of rows in page
$offset = ( $pagenum - 1 ) * $limit;
$total = $wpdb->get_var( "SELECT COUNT(`id`) FROM `wp_dive`" );
$num_of_pages = ceil( $total / $limit );
$result = $wpdb->get_results( "SELECT `id`,`name_cust`,`gender_cust`,`dob_cust` FROM `wp_dive` LIMIT $offset, $limit" );

$data_html = '';

foreach( $result as $results ) 

    {
    $id=$results->id;
    $name= $results->name_cust;
    $gender= $results->gender_cust;
    $dob= $results->dob_cust;
?>
<?php $html= "<div class=\"divContentBody\">";?>
<?php $html .= "<span class=\"clsOrderNo\">". $id."</span>";?>
<?php $html .= "<span class=\"clsName\">". $name."</span>";?>
<?php $html .= "<span class=\"clsGender\">".$gender."</span>";?>
<?php $html .= "<span class=\"clsDOB\">".  $dob ."</span>";?>
<?php $html .= "</div>"?>
<?php
$data_html .=$html; 
 }
echo $data_html;


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

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

Answer 2

<?php

global $wpdb;
$paged = isset( $_GET['paged'] ) ? (int) $_GET['paged'] : 1;

$limit = 10; // Row in a single
$offset = ( $paged - 1 ) * $limit;
$total = $wpdb->get_var( 'SELECT COUNT(`id`) FROM `wp_dive`' );
$num_of_pages = ceil( $total / $limit );
$result = $wpdb->get_results( "SELECT `id`,`name_cust`,`gender_cust`,`dob_cust` FROM `wp_dive` LIMIT $offset, $limit" );

$data_html = '';

foreach( $result as $results ) 

    {
    $id=$results->id;
    $name= $results->name_cust;
    $gender= $results->gender_cust;
    $dob= $results->dob_cust;
?>
<?php $html= "<div class=\"divContentBody\">";?>
<?php $html .= "<span class=\"clsOrderNo\">". $id."</span>";?>
<?php $html .= "<span class=\"clsName\">". $name."</span>";?>
<?php $html .= "<span class=\"clsGender\">".$gender."</span>";?>
<?php $html .= "<span class=\"clsDOB\">".  $dob ."</span>";?>
<?php $html .= "</div>"?>
<?php
$data_html .=$html; 
 }
echo $data_html;


$page_links = paginate_links( array(
    'base' => add_query_arg( 'paged', '%#%' ),
    'format' => '',
    'prev_text' => '&laquo;',
    'next_text' => '&raquo;',
    'total' => $total,
    'current' => $paged
) );

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

?>

Answer 3

You are querying with posts_per_page, but in order to use pagination you also need to append the 'paged' parameter to your query.

See the WP Codex here:

http://codex.wordpress.org/Pagination#Adding_the_.22paged.22_parameter_to_a_query

Answer 4

The native way of doing this is extending the class WP_List_Table and let WordPress handle all the specifics of the table display. I know it from the plugin Internal Link Check, by kaiser. The example bellow is a stripped version that performs a very simple SQL query.

First, we need a helper page (using PHP5.3+ anonymous functions):

add_action('admin_menu', function() 
{
    add_menu_page(
        'TE', 
        '<span style="color:#e57300;">Table Example</span>', 
        'edit_pages', 
        'table-example', 
        function() { 
            echo '<div class="wrap">';
            screen_icon('edit');
            echo '<h2>Table Example</h2>';
            new B5F_WP_Table(); 
            echo '</div>';
        },
        'http://sstatic.net/stackexchange/img/favicon.ico',
        1  // create before Dashboard menu item
    );
});

This is the end result:

And here the class that performs everything (note the need of importing the main class). You'll have to adjust the query and the table columns for your data.

if( is_admin() && !class_exists( 'WP_List_Table' ) )
    require_once( ABSPATH . 'wp-admin/includes/class-wp-list-table.php' );

class B5F_WP_Table extends WP_List_Table
{
    private $order;
    private $orderby;
    private $posts_per_page = 5;

    public function __construct()
    {
        parent :: __construct( array(
            'singular' => 'table example',
            'plural'   => 'table examples',
            'ajax'     => true
        ) );
        $this->set_order();
        $this->set_orderby();
        $this->prepare_items();
        $this->display();
    }

    private function get_sql_results()
    {
        global $wpdb;
        $args = array( 'ID', 'post_title', 'post_date', 'post_content', 'post_type' );
        $sql_select = implode( ', ', $args );
        $sql_results = $wpdb->get_results("
                SELECT $sql_select
                    FROM $wpdb->posts
                WHERE post_status = 'publish'
                ORDER BY $this->orderby $this->order "
        );
        return $sql_results;
    }

    public function set_order()
    {
        $order = 'DESC';
        if ( isset( $_GET['order'] ) AND $_GET['order'] )
            $order = $_GET['order'];
        $this->order = esc_sql( $order );
    }

    public function set_orderby()
    {
        $orderby = 'post_date';
        if ( isset( $_GET['orderby'] ) AND $_GET['orderby'] )
            $orderby = $_GET['orderby'];
        $this->orderby = esc_sql( $orderby );
    }

    /**
     * @see WP_List_Table::ajax_user_can()
     */
    public function ajax_user_can() 
    {
        return current_user_can( 'edit_posts' );
    }

    /**
     * @see WP_List_Table::no_items()
     */
    public function no_items() 
    {
        _e( 'No posts found.' );
    }

    /**
     * @see WP_List_Table::get_views()
     */
    public function get_views()
    {
        return array();
    } 

    /**
     * @see WP_List_Table::get_columns()
     */
    public function get_columns()
    {
        $columns = array(
            'ID'         => __( 'ID' ),
            'post_title' => __( 'Title' ),
            'post_date'  => __( 'Date' ),
            'post_type'  => __( 'Type' )
        );
        return $columns;        
    }

    /**
     * @see WP_List_Table::get_sortable_columns()
     */
    public function get_sortable_columns()
    {
        $sortable = array(
            'ID'         => array( 'ID', true ),
            'post_title' => array( 'post_title', true ),
            'post_date'  => array( 'post_date', true )
        );
        return $sortable;
    }

    /**
     * Prepare data for display
     * @see WP_List_Table::prepare_items()
     */
    public function prepare_items()
    {
        $columns  = $this->get_columns();
        $hidden   = array();
        $sortable = $this->get_sortable_columns();
        $this->_column_headers = array( 
            $columns,
            $hidden,
            $sortable 
        );

        // SQL results
        $posts = $this->get_sql_results();
        empty( $posts ) AND $posts = array();

        # >>>> Pagination
        $per_page     = $this->posts_per_page;
        $current_page = $this->get_pagenum();
        $total_items  = count( $posts );
        $this->set_pagination_args( array (
            'total_items' => $total_items,
            'per_page'    => $per_page,
            'total_pages' => ceil( $total_items / $per_page )
        ) );
        $last_post = $current_page * $per_page;
        $first_post = $last_post - $per_page + 1;
        $last_post > $total_items AND $last_post = $total_items;

        // Setup the range of keys/indizes that contain 
        // the posts on the currently displayed page(d).
        // Flip keys with values as the range outputs the range in the values.
        $range = array_flip( range( $first_post - 1, $last_post - 1, 1 ) );

        // Filter out the posts we're not displaying on the current page.
        $posts_array = array_intersect_key( $posts, $range );
        # <<<< Pagination

        // Prepare the data
        $permalink = __( 'Edit:' );
        foreach ( $posts_array as $key => $post )
        {
            $link     = get_edit_post_link( $post->ID );
            $no_title = __( 'No title set' );
            $title    = ! $post->post_title ? "<em>{$no_title}</em>" : $post->post_title;
            $posts[ $key ]->post_title = "<a title='{$permalink} {$title}' href='{$link}'>{$title}</a>";
        }
        $this->items = $posts_array;
    }

    /**
     * A single column
     */
    public function column_default( $item, $column_name )
    {
        return $item->$column_name;
    }

    /**
     * Override of table nav to avoid breaking with bulk actions & according nonce field
     */
    public function display_tablenav( $which ) {
        ?>
        <div class="tablenav <?php echo esc_attr( $which ); ?>">
            <!-- 
            <div class="alignleft actions">
                <?php # $this->bulk_actions( $which ); ?>
            </div>
             -->
            <?php
            $this->extra_tablenav( $which );
            $this->pagination( $which );
            ?>
            <br class="clear" />
        </div>
        <?php
    }

    /**
     * Disables the views for 'side' context as there's not enough free space in the UI
     * Only displays them on screen/browser refresh. Else we'd have to do this via an AJAX DB update.
     * 
     * @see WP_List_Table::extra_tablenav()
     */
    public function extra_tablenav( $which )
    {
        global $wp_meta_boxes;
        $views = $this->get_views();
        if ( empty( $views ) )
            return;

        $this->views();
    }
}

_{Helper plugin to style the admin: WordPress Admin Style, by bueltge}