Custom Database Tables In WordPress: Part 2

In the first part of our series on Custom Database Tables in WordPress, we looked at how you can create a custom database table from inside your own custom plugin. In Part 2, we will walk you through how you can modify a modify a WordPress custom table, including how to delete the custom database table upon plugin deletion. In addition, we will explain how you can add menu items for the plugin in the admin area, with options to view or add entries in your custom data table.

How To Delete Your Custom Table Upon Plugin Deletion

In order to delete the table when the plugin itself is deleted we need to use the register_uninstall_hook() function that WordPress provides to set the uninstallation hook for a plugin.

function uninstall_students_plugin(){
    global $wpdb;
    $table_name = $wpdb->prefix . 'students';
    $wpdb->query("DROP TABLE IF EXISTS $table_name"); 
}
register_uninstall_hook(__FILE__,'uninstall_students_plugin');

If you deactivate and delete the plugin now, you should now see that the “students” table in the database has been successfully deleted.

Custom Table Admin Menu Items And Page

In this section, we are going to show you how to add an admin page, along with its menu items, for the “students” custom table.

Admin Menu Items

Here is the piece of code you can try – append it to the current plugin PHP file:

function students_custom_table_admin_menu() {
    add_menu_page(__('Students', 'students_custom_table'), __('Students', 'students_custom_table'), 'activate_plugins', 'students', 'students_custom_table_page_handler', 'dashicons-welcome-learn-more', '5');
    add_submenu_page('students', __('Students', 'students_custom_table'), __('Students', 'students_custom_table'), 'activate_plugins', 'students', 'students_custom_table_page_handler');
    add_submenu_page('students', __('Add new', 'students_custom_table'), __('Add new', 'students_custom_table'), 'activate_plugins', 'students_form', 'students_custom_table_page_handler_add_form');
}
add_action('admin_menu', 'students_custom_table_admin_menu');

Now in the admin area, you should see something like this.

As expected, you’ll see that nothing is displayed when you click any of the icons. We will define the page contents in the next section but first, let’s examine the above lines of code to understand how they work.

Because we want to create a top-level menu item and two subitems, we used both the add_menu_page() function and the add_submenu_page() that WordPress provides. These functions accept the following arguments:

add_menu_page( $page_title, $menu_title, $capability, $menu_slug, $function, $icon_url, $position )

• The $page_title is required to be defined and it is basically the first part of title tags of the page you are redirected to when selecting this menu item. In our case, students_custom_table.
• The $menu_title -also required- is the text to be used for the menu. We chose “Students” as a text.
• $capability is the capability required for this menu to be displayed by the user. In our case, we chose the activate_plugins permission. By default, it is given to Super Users and Administrators only. If you want to understand which role or capability fits your needs, you can consult the related official documentation.
• The $menu_slug is our students string that comes right after the permission. It is also required and should be unique. It only uses lowercase alphanumeric, dashes, and underscores characters that are compatible with sanitize_key().
• The $icon_url is an optional argument and is the URL which links to the icon file that will be used for the menu item. We chose one from the dashicons WordPress library.
• $position is where you optionally set the position in the menu order this item should appear.

add_submenu_page( $parent_slug, $page_title, $menu_title, $capability, $menu_slug, $function, $icon_url, $position )

Apart from the parameters it has in common with the add_menu_page, we also see

• The $parent_slug parameter is the required string for the parent menu, in our case, students.
• The $function argument is where you define the callback function that will create the content of the settings page. In our case, it is the students_custom_table_page_handler(), for the students display table, and the students_custom_table_page_handler_add_form(), for the form that we will use to add students.
  We have not declared these functions yet, but we will in a while.

Custom Table Records Display

Let’s start with adding the code that will display the students table under the “Students” menu item.

In order to show the admin tables data, WordPress extends the WP_List_Table built-in Class. The WP_List_Table Class is introduced as a private class in the wp-admin/includes/class-wp-list-table.php file. Private classes are named private as they are only intended for use by other core classes and functions and not by developers.

However, WordPress offers you the ability to redefine this class by extending it. So, what we will do is create a custom class, where we will redefine the properties and methods of the WP_List_Table class, in order to populate the admin table with the data we want. We named the class “Students_Custom_Table_List_Table” and the lines of code we need are the following.

if (!class_exists('WP_List_Table')) {
    require_once(ABSPATH . 'wp-admin/includes/class-wp-list-table.php');
}
class Students_Custom_Table_List_Table extends WP_List_Table {
    
	function __construct() {
        global $status, $page;

        parent::__construct(array(
            'singular' => 'person',
            'plural' => 'persons',
        ));
    }

    function column_default($item, $column_name) {
        return $item[$column_name];
    }

    function column_age($item) {
        return '<em>' . $item['age'] . '</em>';
    }
    
    function column_ip($item) {
        return '<em>' . $item['ip_address'] . '</em>';
    }
    
    function column_name($item) {
        $actions = array(
            'edit' => sprintf('<a href="?page=students_form&id=%s">%s</a>', $item['id'], __('Edit', 'students_custom_table')),
            'delete' => sprintf('<a href="?page=%s&action=delete&id=%s">%s</a>', $_REQUEST['page'], $item['id'], __('Delete', 'students_custom_table')),
        );

        return sprintf('%s %s',
            $item['name'],
            $this->row_actions($actions)
        );
    }

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

    function get_columns() {
        $columns = array(
            'cb' => '<input type="checkbox" />', //Render a checkbox instead of text
            'name' => __('Name', 'students_custom_table'),
            'email' => __('E-Mail', 'students_custom_table'),
            'age' => __('Age', 'students_custom_table'),
            'ip_address' => __('IP address', 'students_custom_table'),
        );
        return $columns;
    }

    function get_sortable_columns() {
        $sortable_columns = array(
            'name' => array('name', true),
            'email' => array('email', false),
            'age' => array('age', false),
            'ip_address' => array('ip_address', false),
        );
        return $sortable_columns;
    }

    function get_bulk_actions() {
        $actions = array(
            'delete' => 'Delete'
        );
        return $actions;
    }

    function process_bulk_action() {
        global $wpdb;
        $table_name = $wpdb->prefix . 'students'; // do not forget about tables prefix

        if ('delete' === $this->current_action()) {
            $ids = isset($_REQUEST['id']) ? $_REQUEST['id'] : array();
            if (is_array($ids)) $ids = implode(',', $ids);

            if (!empty($ids)) {
                $wpdb->query("DELETE FROM $table_name WHERE id IN($ids)");
            }
        }
    }

    function prepare_items() {
        global $wpdb;
        $table_name = $wpdb->prefix . 'students';

        $per_page = 5;

        $columns = $this->get_columns();
        $hidden = array();
        $sortable = $this->get_sortable_columns();

        $this->_column_headers = array($columns, $hidden, $sortable);

        $this->process_bulk_action();

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

        $paged = isset($_REQUEST['paged']) ? ($per_page * max(0, intval($_REQUEST['paged']) - 1)) : 0;
        $orderby = (isset($_REQUEST['orderby']) && in_array($_REQUEST['orderby'], array_keys($this->get_sortable_columns()))) ? $_REQUEST['orderby'] : 'name';
        $order = (isset($_REQUEST['order']) && in_array($_REQUEST['order'], array('asc', 'desc'))) ? $_REQUEST['order'] : 'asc';

        $this->items = $wpdb->get_results($wpdb->prepare("SELECT * FROM $table_name ORDER BY $orderby $order LIMIT %d OFFSET %d", $per_page, $paged), ARRAY_A);

        $this->set_pagination_args(array(
            'total_items' => $total_items,
            'per_page' => $per_page,
            'total_pages' => ceil($total_items / $per_page)
        ));
    }
	
}

If you examine the code carefully you will notice how the columns ‘age’ and ‘ip_address’ are defined. Now we can proceed and also define the function that will finally give us the contents of the “Students” admin screen.

function students_custom_table_page_handler() {
    global $wpdb;

    $table = new Students_Custom_Table_List_Table();
    $table->prepare_items();

    $message = '';
    if ('delete' === $table->current_action()) {
        $message = '<div class="updated below-h2" id="message"><p>' . sprintf(__('Items deleted: %d', 'students_custom_table'), count($_REQUEST['id'])) . '</p></div>';
    }
    ?>
<div class="wrap">

    <div class="icon32 icon32-posts-post" id="icon-edit"><br></div>
    <h2><?php _e('Students', 'students_custom_table')?> <a class="add-new-h2" href="<?php echo get_admin_url(get_current_blog_id(), 'admin.php?page=students_form');?>"><?php _e('Add new', 'students_custom_table')?></a>
    </h2>
    <?php echo $message; ?>

    <form id="students-table" method="GET">
        <input type="hidden" name="page" value="<?php echo $_REQUEST['page'] ?>"/>
        <?php $table->display() ?>
    </form>

</div>
<?php
}

In short, we first created an instance of the custom Students_Custom_Table_List_Table class and then the html elements that will include the students’ table data. You should be able to see the table displayed now, but note that it will be empty.

Now we’ve done this, let’s add some students.!

Creating The Form To Add Our Custom Data

As we mentioned previously, the function responsible for adding students will be students_custom_table_page_handler_add_form().

function students_custom_table_page_handler_add_form() {
    global $wpdb;
    $table_name = $wpdb->prefix . 'students';

    $message = '';
    $notice = '';

    $default = array(
        'id' => 0,
        'name' => '',
        'email' => '',
        'age' => null,
        'ip_address' => null,
    );

    if (wp_verify_nonce($_REQUEST['nonce'], basename(__FILE__))) {
        $item = shortcode_atts($default, $_REQUEST);
        $result = $wpdb->insert($table_name, $item);
        $item['id'] = $wpdb->insert_id;
        if ($result) {
            $message = __('Item was successfully saved', 'students_custom_table');
        } else {
            $notice = __('There was an error while saving item', 'students_custom_table');
        }
    }
    add_meta_box('students_form_meta_box', 'Student data', 'students_custom_table_students_form_meta_box_handler', 'student', 'normal', 'default');

    ?>
<div class="wrap">
    <div class="icon32 icon32-posts-post" id="icon-edit"><br></div>
    <h2><?php _e('Student', 'students_custom_table')?> <a class="add-new-h2"
                                href="<?php echo get_admin_url(get_current_blog_id(), 'admin.php?page=students');?>"><?php _e('back to list', 'students_custom_table')?></a>
    </h2>

    <?php if (!empty($notice)): ?>
    <div id="notice" class="error"><p><?php echo $notice ?></p></div>
    <?php endif;?>
    <?php if (!empty($message)): ?>
    <div id="message" class="updated"><p><?php echo $message ?></p></div>
    <?php endif;?>

    <form id="form" method="POST">
        <input type="hidden" name="nonce" value="<?php echo wp_create_nonce(basename(__FILE__))?>"/>
        <input type="hidden" name="id" value="<?php echo $item['id'] ?>"/>

        <div class="metabox-holder" id="poststuff">
            <div id="post-body">
                <div id="post-body-content">
                    <?php do_meta_boxes('student', 'normal', $item); ?>
                    <input type="submit" value="<?php _e('Save', 'students_custom_table')?>" id="submit" class="button-primary" name="submit">
                </div>
            </div>
        </div>
    </form>
</div>
<?php
}

As you can see in the code, we first set a $default array which will be used for our new records.

Next, after we verify that the request is posted and we have the correct nonce, we use the shortcode_atts(). This is a very useful built-in WordPress function that combines the parameters given and fills in defaults when needed.

And finally, we add our custom meta box and insert the data into the custom table, receiving a message that informs us that the process was successful.

For the purposes of this tutorial we skipped a few elements that, if used in the real world, you’d want to add. These include validating the information added to the database by doing things like defining what happens if a duplicate name or email is added.

Last but not least, we should add a handler for our custom meta box:

function students_custom_table_students_form_meta_box_handler($item) {
    ?>

<table cellspacing="2" cellpadding="5" style="width: 100%;" class="form-table">
    <tbody>
    <tr class="form-field">
        <th valign="top" scope="row">
            <label for="name"><?php _e('Name', 'students_custom_table')?></label>
        </th>
        <td>
            <input id="name" name="name" type="text" style="width: 95%" value="<?php echo esc_attr($item['name'])?>"
                    size="50" class="code" placeholder="<?php _e('Your name', 'students_custom_table')?>" required>
        </td>
    </tr>
    <tr class="form-field">
        <th valign="top" scope="row">
            <label for="email"><?php _e('E-Mail', 'students_custom_table')?></label>
        </th>
        <td>
            <input id="email" name="email" type="email" style="width: 95%" value="<?php echo esc_attr($item['email'])?>"
                    size="50" class="code" placeholder="<?php _e('Your E-Mail', 'students_custom_table')?>" required>
        </td>
    </tr>
    <tr class="form-field">
        <th valign="top" scope="row">
            <label for="age"><?php _e('Age', 'students_custom_table')?></label>
        </th>
        <td>
            <input id="age" name="age" type="number" style="width: 95%" value="<?php echo esc_attr($item['age'])?>"
                    size="50" class="code" placeholder="<?php _e('Your age', 'students_custom_table')?>" required>
        </td>
    </tr>
    <tr class="form-field">
        <th valign="top" scope="row">
            <label for="ip_address"><?php _e('IP', 'students_custom_table')?></label>
        </th>
        <td>
            <input id="ip_address" name="ip_address" type="number" style="width: 95%" value="<?php echo esc_attr($item['ip_address'])?>"
                    size="50" class="code" placeholder="<?php _e('Your IP address', 'students_custom_table')?>" required>
        </td>
    </tr>
    </tbody>
</table>
<?php
}

And that’s it. Now we have a custom plugin that allows us to add students, view the student’s list or delete a student, and store the data in a custom database table! We hope you enjoyed this tutorial and are ready to experiment on your own ideas.

Custom plugins are fun! If you are interested in taking your plugin development skills to the next level, we highly recommend that you study our extensive series on how to create a WordPress plugin with object-oriented programming.
In these articles, we have tutorialized the OOP techniques that will improve your plugin’s reusability and extensiveness, as well as security, scalability and maintenance. Don’t miss out on this valuable resource!