I this tutorial I will show you how to create a List table which can be used in a custom WordPress plugin, with a connection to WordPress DB. The goal is to show data from a database table in a nice grid view. For achieving this we will need to extend WordPress List Table class.
You can find a lot of tutorials on the web, but none that covers all the aspects such as databases uses, sorting and use of filters. I will be using the code from my latest project which was cart abandonment plugin for FastSpring payment provider which is used in our shop.
In the end, our result will be like the screen below:
Create WP Plugin
As I already mentioned, we will use our latest plugin for cart recovery.
First of all, we need to prepare the plugin. For this tutorial, I will use one file for better understanding. Let’s call the file pmc-wp-list-table.php.
<?php
/**
* Plugin Name: PMC WP List Table
* Plugin URI: https://premiumCoding.com
* Description: WordPress List Table example.
* Version: 1.0
* Author: PremiumCoding
* Author URI: https://premiumCoding.com
* License: GPLv3
*/
/**
* This is the PMC loader class.
*
* @package PMC WP List Table
*/
if ( ! class_exists( 'PMC_Loader_WP_List_Table' ) ) {
class PMC_Loader_WP_List_Table {
/**
* Start up
*/
public function __construct()
{
add_action( 'admin_menu', array( $this, 'pmc_add_plugin_page' ) );
$this-> pmc_define();
$this-> pmc_db_create();
}
public function pmc_define(){
global $wpdb;
define('PMC_FS_TABLE', $wpdb->prefix . 'pmc_fs');
}
public function pmc_add_plugin_page()
{
/* add pages & menu items */
add_menu_page( esc_attr__( 'PMC WP List Table', 'textdomain' ),esc_html__( 'PMC List Table', 'textdomain' ),
'administrator','pmc-listTable',array( $this, 'pmc_create_admin_page' ), '', 10);
}
/**
* Admin page callback
*/
public function pmc_create_admin_page()
{
/*here we will output wp list table*/
}
}
/**
* Instantiate the OptionTree loader class.
*
* @since 2.0
*/
$PMC_Loader_WP_List_Table = new PMC_Loader_WP_List_Table();
Our plugin is created so let’s focus on the WP List table.
Create WP List Table
First, we need to be sure that the class WP_List_Table exist.
if( ! class_exists( 'WP_List_Table' ) ) {
require_once( ABSPATH . 'wp-admin/includes/class-wp-list-table.php' );
}
To build a WordPress UI table, the WP_List_Table> will have to be extended with a couple of its methods overridden by a child class.
class PMC_FS_WP_List_Table extends WP_List_Table
{
function __construct(){
parent::__construct( array(
'ajax' => false //does this table support ajax?
) );
}
}
Create WordPress database table
Since we get data from the database we need to create a table. We will call a function pmc_db_create() in PMC_FS_WP_List_Table class where we have also defined table name with constant PMC_FS_TABLE. The function dbDelta( ) is used for creating new tables or updating existing tables to a new structure.
public function pmc_db_create() {
global $wpdb;
$charset_collate = $wpdb->get_charset_collate();
$table_name = PMC_FS_TABLE;
$sql = "CREATE TABLE $table_name (
id mediumint(9) NOT NULL AUTO_INCREMENT,
name varchar(255) ,
last_name varchar(255) ,
email varchar(255) ,
UNIQUE KEY id (id)
) $charset_collate;";
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
dbDelta( $sql );
}
Define WP List Columns, Sortable items, Filter options and Search
First, we need to define which columns from the table will be used in our grid. This is done with the function get_columns(). You can include all the columns or only the column that you wish to display.
/**
* Add columns to grid view
*/
function get_columns(){
$columns = array(
'name' => 'Name',
'last_name' => 'Last Name',
'email' => 'Email',
);
return $columns;
}
Now that we have defined the columns for our grid, we need to define default values for these columns. We can use the function column_default( $item, $column_name ).
function column_default( $item, $column_name ) {
switch( $column_name ) {
case 'id':
case 'name':
case 'last_name':
case 'email':
case 'action':
return $item[ $column_name ];
default:
return print_r( $item, true ) ; //Show the whole array for troubleshooting purposes
}
}
In the next step, we need to get data from a database and start preparing all the items. For this step, we will use the function prepare_items().
First, we need to call global $wpdb so we can use WordPress database functions.
Next, we define numbers of rows in our grid $per_page = 50;. 50 means 50 rows per page.
For database select we can use WordPress function $wpdb->get_results(). We need to get columns name, last_name, email and action from our DB table. Our query will use search and filter options. That is why there are {$search} {$search_custom_vars}.
We will use filters and so we need to retrieve this filter.
//Retrieve $customvar for use in query to get items.
$customvar = ( isset($_REQUEST['customvar']) ? $_REQUEST['customvar'] : '');
if($customvar != '') {
$search_custom_vars= "AND action LIKE '%" . esc_sql( $wpdb->esc_like( $customvar ) ) . "%'";
} else {
$search_custom_vars = '';
}
And for our search via email column, we need to retrieve search request.
if ( ! empty( $_REQUEST['s'] ) ) {
$search = "AND email LIKE '%" . esc_sql( $wpdb->esc_like( $_REQUEST['s'] ) ) . "%'";
}
Because we use sortable table we need to create function get_sortable_columns(). With this function, we will get a list of sortable columns. The format is: ‘internal-name’ => ‘orderby’ or ‘internal-name’ => array( ‘orderby’, true ).
$sortable = $this->get_sortable_columns();
We also need to define columns headers.
$this->_column_headers = array($columns, $hidden, $sortable);
Now that we have headers, sortable columns, and data, we just need to define items for our grid.
$this->items = $items;
And finally, we just need to set page navigation.
$count = $wpdb->get_var( "SELECT COUNT(id) FROM ".PMC_FS_TABLE." WHERE 1 = 1 {$search} {$search_custom_vars}" );
// Set the pagination
$this->set_pagination_args( array(
'total_items'finally => $count,
'per_page' => $per_page,
'total_pages' => ceil( $count / $per_page )
) );
As you can see we need to get a number of all rows in the table so we will get this via WP get_var() function.
$count = $wpdb->get_var( "SELECT COUNT(id) FROM ".PMC_FS_TABLE." WHERE 1 = 1 {$search} {$search_custom_vars}" );
Here is the entire function.
function prepare_items() {
global $wpdb;
$per_page = 50;
$current_page = $this->get_pagenum();
if ( 1 < $current_page ) {
$offset = $per_page * ( $current_page - 1 );
} else {
$offset = 0;
}
$search = '';
//Retrieve $customvar for use in query to get items.
$customvar = ( isset($_REQUEST['customvar']) ? $_REQUEST['customvar'] : '');
if($customvar != '') {
$search_custom_vars= "AND action LIKE '%" . esc_sql( $wpdb->esc_like( $customvar ) ) . "%'";
} else {
$search_custom_vars = '';
}
if ( ! empty( $_REQUEST['s'] ) ) {
$search = "AND email LIKE '%" . esc_sql( $wpdb->esc_like( $_REQUEST['s'] ) ) . "%'";
}
$items = $wpdb->get_results( "SELECT id,name,last_name,email,action FROM ".PMC_FS_TABLE." WHERE 1=1 {$search} {$search_custom_vars}" . $wpdb->prepare( "ORDER BY id DESC LIMIT %d OFFSET %d;", $per_page, $offset ),ARRAY_A);<br> $columns = $this->get_columns();
$hidden = array();
$sortable = $this->get_sortable_columns();
$this->_column_headers = array($columns, $hidden, $sortable);
usort( $items, array( &$this, 'usort_reorder' ) );
$count = $wpdb->get_var( "SELECT COUNT(id) FROM ".PMC_FS_TABLE." WHERE 1 = 1 {$search};" );
$this->items = $items;
// Set the pagination
$this->set_pagination_args( array(
'total_items' => $count,
'per_page' => $per_page,
'total_pages' => ceil( $count / $per_page )
) );
}
We wish to sort the column action so we need to create a function that does this. This way the above-mentioned column headers are changed to links. The second parameter in the value array of $sortable_columns takes care of a possible pre-ordered column. If the value is set to true, the column is in ascending order, if the value is set to false, the column is in descending order or unordered.
function get_sortable_columns() {
$sortable_columns = array(
'action' => array('action',false),
);
return $sortable_columns;
}
With this data, we can write a method for sorting out the data. Our default sorting field is ID.
function usort_reorder( $a, $b ) {
// If no sort, default to title
$orderby = ( ! empty( $_GET['orderby'] ) ) ? $_GET['orderby'] : 'id';
// If no order, default to asc
$order = ( ! empty($_GET['order'] ) ) ? $_GET['order'] : 'desc';
// Determine sort order
$result = strcmp( $a[$orderby], $b[$orderby] );
// Send final sort direction to usort
return ( $order === 'asc' ) ? $result : -$result;
}
We will also use filter display ALL | ABBANDON | RECOVERED. For the reason we need to write a method get_views().
protected function get_views() {
$views = array();
$current = ( !empty($_REQUEST['customvar']) ? $_REQUEST['customvar'] : 'all');
//All link
$class = ($current == 'all' ? ' class="current"' :'');
$all_url = remove_query_arg('customvar');
$views['all'] = "<a href='{$all_url }' {$class} >All</a>";
//Recovered link
$foo_url = add_query_arg('customvar','recovered');
$class = ($current == 'recovered' ? ' class="current"' :'');
$views['recovered'] = "<a href='{$foo_url}' {$class} >Recovered</a>";
//Abandon
$bar_url = add_query_arg('customvar','abandon');
$class = ($current == 'abandon' ? ' class="current"' :'');
$views['abandon'] = "<a href='{$bar_url}' {$class} >Abandon</a>";
return $views;
}
Our class for the extend Wp List Column is complete. Here is the entire class:
if( ! class_exists( 'WP_List_Table' ) ) {
require_once( ABSPATH . 'wp-admin/includes/class-wp-list-table.php' );
}
class PMC_FS_WP_List_Table extends WP_List_Table
{
function __construct(){
parent::__construct( array(
'ajax' => false //does this table support ajax?
) );
}
/**
* Add columns to grid view
*/
function get_columns(){
$columns = array(
'name' => 'Name',
'last_name' => 'Last Name',
'email' => 'Email',
'action' => 'Action'
);
return $columns;
}
function column_default( $item, $column_name ) {
switch( $column_name ) {
case 'id':
case 'name':
case 'last_name':
case 'email':
case 'action':
return $item[ $column_name ];
default:
return print_r( $item, true ) ; //Show the whole array for troubleshooting purposes
}
}
protected function get_views() {
$views = array();
$current = ( !empty($_REQUEST['customvar']) ? $_REQUEST['customvar'] : 'all');
//All link
$class = ($current == 'all' ? ' class="current"' :'');
$all_url = remove_query_arg('customvar');
$views['all'] = "<a href='{$all_url }' {$class} >All</a>";
//Recovered link
$foo_url = add_query_arg('customvar','recovered');
$class = ($current == 'recovered' ? ' class="current"' :'');
$views['recovered'] = "<a href='{$foo_url}' {$class} >Recovered</a>";
//Abandon
$bar_url = add_query_arg('customvar','abandon');
$class = ($current == 'abandon' ? ' class="current"' :'');
$views['abandon'] = "<a href='{$bar_url}' {$class} >Abandon</a>";
return $views;
}
function usort_reorder( $a, $b ) {
// If no sort, default to title
$orderby = ( ! empty( $_GET['orderby'] ) ) ? $_GET['orderby'] : 'id';
// If no order, default to asc
$order = ( ! empty($_GET['order'] ) ) ? $_GET['order'] : 'desc';
// Determine sort order
$result = strcmp( $a[$orderby], $b[$orderby] );
// Send final sort direction to usort
return ( $order === 'asc' ) ? $result : -$result;
}
function get_sortable_columns() {
$sortable_columns = array(
'action' => array('action',false),
);
return $sortable_columns;
}
/**
* Prepare admin view
*/
function prepare_items() {
global $wpdb;
$per_page = 50;
$current_page = $this->get_pagenum();
if ( 1 < $current_page ) {
$offset = $per_page * ( $current_page - 1 );
} else {
$offset = 0;
}
$search = '';
//Retrieve $customvar for use in query to get items.
$customvar = ( isset($_REQUEST['customvar']) ? $_REQUEST['customvar'] : '');
if($customvar != '') {
$search_custom_vars= "AND action LIKE '%" . esc_sql( $wpdb->esc_like( $customvar ) ) . "%'";
} else {
$search_custom_vars = '';
}
if ( ! empty( $_REQUEST['s'] ) ) {
$search = "AND email LIKE '%" . esc_sql( $wpdb->esc_like( $_REQUEST['s'] ) ) . "%'";
}
$items = $wpdb->get_results( "SELECT id,name,last_name,email,action FROM ".PMC_FS_TABLE." WHERE 1=1 {$search} {$search_custom_vars}" . $wpdb->prepare( "ORDER BY id DESC LIMIT %d OFFSET %d;", $per_page, $offset ),ARRAY_A);
$columns = $this->get_columns();
$hidden = array();
$sortable = $this->get_sortable_columns();
$this->_column_headers = array($columns, $hidden, $sortable);
usort( $items, array( &$this, 'usort_reorder' ) );
$count = $wpdb->get_var( "SELECT COUNT(id) FROM ".PMC_FS_TABLE." WHERE 1 = 1 {$search} {$search_custom_vars}" );
$this->items = $items;
// Set the pagination
$this->set_pagination_args( array(
'total_items' => $count,
'per_page' => $per_page,
'total_pages' => ceil( $count / $per_page )
) );
}
}
Now we only need to set our admin page. We will use the function mentioned above pmc_create_admin_page().
Create WP Admin page with List Table
First, we need to create new WP List Table
$pmc_fs_table = new PMC_WP_List_Table();
Then we need to prepare it.
$pmc_fs_table->prepare_items();
Because we are using filters and search options, we need to call view and add search form.
$pmc_fs_table->views(); echo '<form method="post">'; echo ' <input type="hidden" name="page" value="pmc_fs_search">'; $pmc_fs_table->search_box( 'search', 'search_id' ); $pmc_fs_table->display(); echo '</form></div>';
And now the entire function for admin page is ready.
public function pmc_create_admin_page()
{
global $wpdb;
?>
<div class="wrap pmc-fs">
<?php
$pmc_fs_table = new PMC_WP_List_Table();
echo '<div class="wrap"><h2>Our List tablet</h2>';
$pmc_fs_table->prepare_items();
echo '<input type="hidden" name="page" value="" />';
$pmc_fs_table->views();
echo '<form method="post">';
echo ' <input type="hidden" name="page" value="pmc_fs_search">';
$pmc_fs_table->search_box( 'search', 'search_id' );
$pmc_fs_table->display();
echo '</form></div>';
}
And here is the final image of our new WordPress list table and admin page:
Conclusion
I hope that this tutorial helps you create Wp list for your admin pages. If you have any questions or maybe I did something wrong in the code 🙂 please comment on this tutorial. You can download the whole script below:
Thanks for reading.
Other tutorials:
WordPress Tutorial: How to add a custom style to WordPress Admin?
You must be logged in to post a comment.