WordPress Pagination and Search in Backend

This an advanced WordPress tutorial about plugin that creates and searches a table in database. All that is happening in WordPress backend. This is useful when you need to see what your visitor have submitted to you in contact form.

After plugin activation populate table tablePrefix_messages with some
dummy data and in backend you will be able to search and paginate the data.

<?php
/*
Plugin Name: Foo 
Plugin URI: http://www.example.com
Description: Baz
Version: 1.0
Author: Nikola
Author URI: http://www.example.com
License: GPL2
*/

//plugin activation hook
register_activation_hook( __FILE__, 'nb_coding_create_update_table' );

/**
 * Create custom tables
 * @global type $wpdb
 */
function nb_coding_create_update_table() {
    global $wpdb;
    $tablename = $wpdb->prefix . "messages";
    
    //if the table doesn't exist, create it
    if( $wpdb->get_var("SHOW TABLES LIKE '$tablename'") != $tablename ) {

        $sql = "CREATE TABLE `$tablename` (
        `id` INT( 11 ) NOT NULL AUTO_INCREMENT,
        `email` VARCHAR( 100 ) NOT NULL ,
        `message` VARCHAR( 100 ) NOT NULL ,
        `date` DATETIME,
        PRIMARY KEY (id)
        );";
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
    }
}

//add admin settings
add_action( 'admin_menu', 'nb_coding_contact_form_funtion' );



/**
 * add menu to admin
 */
function nb_coding_contact_form_funtion() {
    add_menu_page( 'Foo Coding', 'Foo Coding', 'manage_options', 'nbcodingcontactform', 'nb_coding_contact_form' );
}



function nb_coding_contact_form() {
 global $wpdb;
 $sc ="";
        $in_url = "";
        $sval = "";
 if (isset($_GET['s']) && $_GET['s'] != "" ) {
  $sc = " WHERE message LIKE '%".esc_attr($_GET['s'])."%' ";
                $in_url = "&s=".esc_attr($_GET['s']);
                $sval = esc_attr($_GET['s']);  
 }
  

    ?>
<div class="widefat">
<h2>Contact Form Data</h2>
<p class="search-box">
<form action="" method="GET">
    <input type="hidden" name="page" value="nbcodingcontactform">
    <input type="text" name="s" value="<?php echo $sval; ?>">
    <input type="submit" value="search">
</form>
</p>
<table class="wp-list-table widefat fixed posts">
  <thead>
  <tr>
   <th>ID</th> 
   <th>email</th> 
   <th>Message</th>
  </tr>
 </thead> 
 <tbody>
  <?php 

   $per_page = 5;
   $curr_page = 1;
   if (!isset($_GET['paged'])) {
    $offset = $per_page;
   } else {
    $offset = $_GET['paged']*$per_page;
    $curr_page = $_GET['paged'];
   }
   
   $offset = $offset - $per_page;
   $tablename = $wpdb->prefix . "messages";
      $total = $wpdb->get_var( "SELECT COUNT(*) FROM $tablename $sc");

   $num_pages = floor($total / $per_page);
   $rem = $total % $per_page;
   if ($rem != 0) {
    $num_pages++;
   } 
   
   $items = $wpdb->get_results( 
    "
    SELECT *
    FROM $tablename $sc LIMIT $offset, $per_page
    "
   );
   
   $alt = 0;
   foreach ( $items as $item ) 
   {
    $alt++;
    $palt = $alt % 2;
    if ($palt==0) {
     $class = ' class = "alternate"' ;
    } else{
     $class = '';
    } 
    echo "<tr$class> <td> {$item->id} </td> <td> {$item->email} </td> <td> {$item->message} </td>  </tr>" ;
   }   
  ?> 
 </tbody>
 <tfoot>
  <tr>
   <th>ID</th> 
   <th>email</th> 
   <th>Message</th>
  </tr>
 </tfoot>
</table>

<div>
 <?php
 if ($total > $per_page) {
  echo "<ul style=\"display: inline-block\">";
  for ($i=1; $i <= $num_pages; $i++) {
   if ($curr_page == $i) {
    echo "<li style='display: inline;'><span href='/wp-admin/admin.php?page=nbcodingcontactform&paged=$i{$in_url}' style='padding: 6px 12px;'>$i</span></li>";    
   } else {
    echo "<li style='display: inline;'><a href='/wp-admin/admin.php?page=nbcodingcontactform&paged=$i{$in_url}' style='padding: 6px 12px;border: 1px solid #DDD;'>$i</a></li>";
   }
  }
  echo "</ul>"; 
 }
 ?>
</div>
  
</div>

    <?php
}
?>