How To Add Custom Data Table In Wordpress Dashboard? Tweet

Sometimes, while working on wordpress themes, you might need to create custom forms and the functions too in order to display the data stored through such forms in wordpress admin dashboard so that they look similar to other tables and have the same features like bulk action, search, pagination, data count as well as actions on hover too. Assuming you know the php basics and can create a form and database table too which are required for the form data to be inserted into database, here, we'll be focussing on displaying those data in tabular format in wordpress admin panel or dashboard.

Create Admin Menu Item

The first thing you'll need in order to display any content in wordpress admin panel would be the admin menu title. Let's create a function to add that title in functions.php file inside admin.

function my_admin_menu() 
{
	add_menu_page('Registrations','Registrations','manage_options','std-regd','registration_callback','dashicons-welcome-write-blog',98);
	add_submenu_page('std-regd','Event Registration', 'Event Registration', 'manage_options', 'std-regd','registration_callback');
	add_submenu_page('std-regd','Course Registration', 'Course Registration', 'manage_options', 'course-registration','course_reg_callback');	
}
add_action('admin_menu','my_admin_menu');	

add_menu_page function will add a top level menu page where the parameters are as follows.

1. Page Title - The text which is displayed on the title tags when the menu is selected.
2. Menu Title - The text which is displayed on the sidemenu as the menu title.
3. User Type - The authority of the user required for the menu item to be displayed.
4. Menu Slug - The unique slug for the menu item.
5. Call Back Function - The function name to define the output of the menu title.
6. Dashicon - The icon which is displayed beside the menu title.
7. Menu Position - The numeric value for the position of the menu item in sidemenu. Basically, you'll place it below all the menu items that come with wordpress installation i.e. above 100.

Inside the top level menu title, generally you'll add another menu title with the same url so that it appears exactly as other menu items in wordpress dashboard. For second level menu, you need to use add_submenu_page function for which the required parameters are as follows.

1. Parent Slug - Slug of the top level menu item.
2. Page Title - Text which is displayed on the title tags when the menu is selected.
3. Menu Title - Text which is displayed in the dashboard sidemenu.
4. User Type - The authority of the user required for the menu item to be displayed.
5. Menu Slug - The same slug defined for the top level menu item.
6. Call Back Function - The function name to define the output of the menu title.

In case you need another second level menu title, then only the parent slug would be taken from the top level menu while the user type too might be the same in most cases.

Finally, you'll need call the function add_action where the wordpress hook admin_menu will be added in the parameter along with the function you just created to add those items.

Create Page to Override WP_List_Table classes

Lets call one of those callback functions in the same page and add a page that will hold all the functions required to display the contents form our database. You can create a separate page template and include here but we're going the easier route.

function registration_callback()
{
	require_once(get_template_directory().'/inc/admin/event_registrations.php');
}	

Include WP_List_Table Class

Create a file with the same name in the defined location and add the following code to include the class-wp-list-table.php file that comes with wordpress installation.

<?php
if (!class_exists('WP_List_Table')) {
	require_once (ABSPATH . 'wp-admin/includes/class-wp-list-table.php');
}	

Create Class to override existing classes in WP_List_Table

Now, you'll be required create a new table class that'll extend the WP_List_Table and overrides the classes as required by wordpress.

class Event_Reg_Table extends WP_List_Table
{
	public function __construct()
	{
		parent::__construct(array(
			'singular' => 'Registration',
			'plural' => 'Registrations',
			'ajax' => false
		));		
	}
}	

The constructor method here will hold the name to be used for the data as per the data count. It also contains the information regarding either ajax is to be run on this page or not. All other classes will be added inside the same class i.e. Event_Reg_Table in this example.

Retrieve Data Records From Database

Lets define the class to retrieve records from the defined database table first then. Simply change the table name in the SQL query below and you're done. If you have the basic understanding of php then the function stated below is quite clear. It'll store data rows from the defined table in a variable where the data will be ordered by the order request value in case of request whereas the limit and offset value is defined as the base for pagination functions that'll required in latter part. The data will then be stored in another variable as an associative array which will be returned whenever this function is called.

public static function get_records($per_page = 20, $page_number = 1)
{
	global $wpdb;
	$sql = "select * from {wp_prefix}_table_name";
	if (isset($_REQUEST['s'])) {
		$sql.= ' where your_name LIKE "%' . $_REQUEST['s'] . '%" or event_name LIKE "%' . $_REQUEST['s'] . '%"';
	}
	if (!empty($_REQUEST['orderby'])) {
		$sql.= ' ORDER BY ' . esc_sql($_REQUEST['orderby']);
		$sql.= !empty($_REQUEST['order']) ? ' ' . esc_sql($_REQUEST['order']) : ' ASC';
	}
	$sql.= " LIMIT $per_page";
	$sql.= ' OFFSET ' . ($page_number - 1) * $per_page;
	$result = $wpdb->get_results($sql, 'ARRAY_A');
	return $result;
}	

Define Column Name for Dashboard Table

function get_columns()
{
	$columns = [
		'cb' => '<input type="checkbox" />', 
		'your_name' => __('Name', 'wth') , 
		'your_email' => __('Email', 'wth') , 
		'your_contact' => __('Contacts', 'wth') , 
	];
	return $columns;
}

This function defines the column names which are displayed in the dashboard table and allows us to rename them for better user experience. It also allows you to add checkbox for bulk options on the header and footer of the table.

Define Hidden Columns

public function get_hidden_columns()
{
	array([
		'created_at' => __('created_at','wth')
	]);
	return array();
}	

You can define the hidden columns from the database table as well if you don't want some of the columns to be visible in the dashboard.

Define Sortable Columns

public function get_sortable_columns()
{
	$sortable_columns = array(
		'event_date' => array('event_date',true)
	);
	return $sortable_columns;
}	

This function defines the sortable columns that helps to reorder the data rows in ascending or descending order.

Define Default Columns

public function column_default( $item, $column_name ) 
{
	switch ( $column_name ) {
		case 'your_email':
		case 'your_contact':
		return $item[ $column_name ];
		default:
		return print_r( $item, true );
	}
}	

This function defines the default columns in the table which don't require additional options like edit or delete.

Add Checkboxes to Data Rows

function column_cb($item)
{
	return sprintf('<input type="checkbox" name="bulk-delete[]" value="%s" />', $item['id']);
}	

This function adds checkbox beside each data rows that allows us to carry on bulk actions on multiple data rows at one go. sprintf function is used to define the formatted string.

Define Column Options

function column_your_name( $item ) 
{
	$actions = array(
		'edit'      => sprintf('<a href="?page=%s&action=%s&record=%s">Edit</a>',$_REQUEST['page'],'edit',$item['ID']),
		'delete'    => sprintf('<a href="?page=%s&action=%s&record=%s">Delete</a>',$_REQUEST['page'],'delete',$item['id']),
	);
	return sprintf('%1$s %2$s', $item['your_name'], $this->row_actions($actions) );
}	

This function allows you to add options on mouse hover over the column value. Basically, you'll need the edit and delete options there. Add the page urls you want for those actions. Remember the function name should have a prefix column_ followed by the database column name on which you want to add those options.

Define Bulk Action

public function get_bulk_actions()
{
	$actions = ['bulk-delete' => 'Delete'];
	return $actions;
}	

This function defines the bulk actions available on the dropdown.

Delete Function

public static function delete_records($id)
{
	global $wpdb;
	$wpdb->delete("database_table_name", ['id' => $id], ['%d']);
}	

$wpdb is a global variable for database where the delete function is called and the required parameters defined for the query to be executed. You need to replace the database_table_name with your tablename.

Count Records

public static function record_count()
{
	global $wpdb;
	$sql = "SELECT COUNT(*) FROM database_table_name";
	return $wpdb->get_var($sql);
}

Simply a SQL query that counts the resultset from a table and returns the numeric value.

No Records

public function no_items()
{
	_e('No record found in the database.', 'wth');
}	

Returns the defined string in case there's no data in the database table to display.

Process Bulk Action

public function process_bulk_action() 
{
	if ( 'delete' === $this->current_action() ) {	    
		self::delete_records( absint( $_GET['record'] ) );
	}

	if ( ( isset( $_POST['action'] ) && $_POST['action'] == 'bulk-delete' ) || ( isset( $_POST['action2'] ) && $_POST['action2'] == 'bulk-delete' )) {
		$delete_ids = esc_sql( $_POST['bulk-delete'] );
		foreach ( $delete_ids as $id ) {
			self::delete_records( $id );
		}
	}
}	

Simply the action defined in case the bulk action is triggered. If delete option is selected directly, it'll delete the selected resultset while it'll go through the loop and delete selected resultsets in case multiple ids are selected. If header redirect issue is detected simply add the wp_redirect function right below the delete function before closing if clauses.

wp_redirect( esc_url( add_query_arg() ) );
exit;	

Prepare Items

public function prepare_items()
{
	$columns = $this->get_columns();
	$hidden = $this->get_hidden_columns();
	$sortable = $this->get_sortable_columns();
	$this->_column_headers = array( $columns, $hidden, $sortable );
	$this->process_bulk_action();
	$per_page = $this->get_items_per_page('records_per_page', 20);
	$current_page = $this->get_pagenum();
	$total_items = self::record_count();
	$data = self::get_records($per_page, $current_page);
	$this->set_pagination_args( [
		'total_items' => $total_items,
		'per_page' => $per_page,
	]);
	$this->items = $data;
}	

This function combines all other functions within this parent class and stores the result in a variable $this->items as required by WP_List_Table and this is the only function that'll be required to be called outside of this page.

Now, everything you need for a dashboard table is sorted out let's have a look on it for a better understanding on how it looks.

<?php
if (!class_exists('WP_List_Table')) {
	require_once (ABSPATH . 'wp-admin/includes/class-wp-list-table.php');
}

class Event_Reg_Table extends WP_List_Table
{
	public function __construct() {}
	public static function get_records($per_page = 20, $page_number = 1) {}
	function get_columns() {}
	public function get_hidden_columns() {}
	public function get_sortable_columns() {}
	public function column_default( $item, $column_name ) {}
	function column_cb($item) {}
	function column_your_name( $item ) {}
	public function get_bulk_actions() {}	
	public static function delete_records($id) {}
	public static function record_count() {}
	public function no_items() {}
	public function process_bulk_action() {}
	public function prepare_items(){}
}	

So, this is what you need to get those resultsets to be displayed on our dashboard page. Now, the only thing you'll need is to connect these classes to the dashboard function that was created prior to extending the WP_List_Table class. Let's get back to that again and add some functions there.

function registration_callback()
{
	require_once(get_template_directory().'/inc/admin/event_registrations.php');
	$registration = new Event_Reg_Table;
	echo '<h1 class="wp-heading-inline">Event Registrations</h1>';
	echo '<form method="post">';
	$registration->prepare_items();
	$registration->search_box('Search Records','search_record');
	$registration->display();
	echo '</form>';
}	

First of all, you'll need to store the data retrieved by the object that's created so far in a variable. $registration = new Event_Reg_Table;

Then, you can add the page title to display it on top of the page. Since, you haven't created a separate template for the table, you'll need to wrap the table data with HTML form element which is essential for bulk actions to be executed.

Then the prepare_items() function will be called, that'll retrieve all the resultsets from the database table.

You can add the search box by simply calling the search_box() method that's present in the parent class. Required parameters are the text value for search button and the id for the input field.

Finally, you can call the display() method that too is present in the parent class. It is the method that'll display all the records in individual rows.

That's it. Enjoy your custom dashboard table with all the opotions available with wordpress inbuilt functionalities.