How to add a WordPress admin page to add data to a custom table

So last time I talked about how you can create a custom table with WordPress. Now, we're going to add some data to that table.

But we're not going to just write some MySQL and write it directly in phpMyAdmin. Instead, we're going to create a way for an admin use to insert data to the table via a page in the backend.

This is something that can seem like a massive worry, but the basic principles are pretty simple. So let's get started!

How to add data the WordPress way

Like I said in the custom tables post, I'm not really going to go over MySQL and the commands. Instead, this tutorial will show you how to use MySQL the WordPress way. If you're looking to learn MySQL, I would highly suggest the W3Schools tutorial.

Anyway, to add data via code in WordPress, we're going to go back to the $wpdb class with the insert method. This function takes in the table name and an array of the values to insert and adds the data row to the table. So, an example line of code would look like this.

$wpdb->insert( $table_name, $item );

Now that we know the function we'll be using, let's learn how to create a custom admin page.

How to add a custom admin page

The next part we need to learn before creating the page is how to add a custom admin page in WordPress. Fortunately, this part is super simple. First, we'll use the add_menu_page function. Here we'll add the page name, page slug, dashicon, capability needed to access the page and the callback function.

add_menu_page( __( 'Teams', 'sports-bench' ), __( 'Teams', 'sports-bench' ), 'edit_posts', 'teams', 'sports_bench_teams_page_handler', 'dashicons-groups', 6 ) ;

Then the callback function is where we'll put all of our code that will be printed on the page. So, a basic set up for an admin page would look like this.

function sports_bench_team_admin_menu() {
global $team_page;
add_menu_page( __( 'Teams', 'sports-bench' ), __( 'Teams', 'sports-bench' ), 'edit_posts', 'add_data', 'sports_bench_teams_page_handler', 'dashicons-groups', 6 ) ;
}
add_action( 'admin_menu', 'sports_bench_team_admin_menu' );

function sports_bench_teams_form_page_handler() {
echo 'Hello World!';
}

I do want to note that I've wrapped the add_menu_page function inside another function that is called with the admin_menu hook, which is the proper way to add the page.

Putting the page together

So now we're ready to put together the page. Let's focus in on the callback function, since that's the trickiest part and where all of the fun stuff happens.

First, let's create a form. We'll have text inputs for team_name, team_city, team_state and team_stadium. We'll get to how we'll add team_id in a bit. The form method should be POST and the action should be "?page=add_data".

<form method="POST" action="?page=add_data">
<label>Team Name: </label><input type="text" name="team_name" /><br />
<label>Team City: </label><input type="text" name="team_city" /><br />
<label>Team State: </label><input type="text" name="team_state" /><br />
<label>Team Stadium: </label><input type="text" name="team_stadium" /><br />
<input type="submit" value="submit" />
</form>

Now we get into the actual PHP code. First we're going to find out how to get the team id for the team. And to do that, we'll use the following lines of code. It checks the database for the last entered team, grabs that id and adds one to it for our new team. If there are no teams, then the id is set to one.

$default_row = $wpdb->get_row( "SELECT * FROM $table_name ORDER BY team_id DESC LIMIT 1" );
if ( $default_row != null ) {
$id = $default_row->team_id + 1;
} else {
$id = 1;
}

Next, we'll create an array of default values. This will help us avoid any weird errors when we go to insert the data. We then merge this array with the data that comes back into a variable.

$default = array(
'team_id' => $id,
'team_name' => '',
'team_city' => '',
'team_state' => '',
'team_stadium' => '',
);
$item = shortcode_atts( $default, $_REQUEST );

And then finally, we go and insert the new team into the database.

$wpdb->insert( $table_name, $item );

And here's our completed code.

function sports_bench_team_admin_menu() {
global $team_page;
add_menu_page( __( 'Teams', 'sports-bench' ), __( 'Teams', 'sports-bench' ), 'edit_posts', 'add_data', 'sports_bench_teams_page_handler', 'dashicons-groups', 6 ) ;
}
add_action( 'admin_menu', 'sports_bench_team_admin_menu' );

function sports_bench_teams_form_page_handler() {
global $wpdb;
echo '<form method="POST" action="?page=add_data">
<label>Team Name: </label><input type="text" name="team_name" /><br />
<label>Team City: </label><input type="text" name="team_city" /><br />
<label>Team State: </label><input type="text" name="team_state" /><br />
<label>Team Stadium: </label><input type="text" name="team_stadium" /><br />
<input type="submit" value="submit" />
</form>';

$default_row = $wpdb->get_row( "SELECT * FROM $table_name ORDER BY team_id DESC LIMIT 1" );
if ( $default_row != null ) {
$id = $default_row->team_id + 1;
} else {
$id = 1;
}
$default = array(
'team_id' => $id,
'team_name' => '',
'team_city' => '',
'team_state' => '',
'team_stadium' => '',
);
$item = shortcode_atts( $default, $_REQUEST );

$wpdb->insert( $table_name, $item );
}

Pretty cool isn't it.

Where you can go from here

This is a super simple example to the sake of time, but it shows you the power you have when it comes to data for your site or plugin. You'll want to be sure to add more security to it, like sanitation and validation, but this the basic outline to follow with adding data to a custom table in the WordPress admin.

So have fun with this newly learned power!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

7 comments on “How to add a WordPress admin page to add data to a custom table

  1. rajan

    I copied the code for create a custom table and code from this post “How to add a WordPress admin page to add data to a custom table” in a new plugin.

    I activated it and it created successfully the table in database.
    Also on admin ‘Teams’ menu created but when I click on it nothing displays. I am using latest wordpress version 4.9.7.

    get_charset_collate();
    require_once( ABSPATH . ‘wp-admin/includes/upgrade.php’ );

    //* Create the teams table
    $table_name = $wpdb->prefix . ‘sb_teams’;
    $sql = “CREATE TABLE $table_name (
    team_id INTEGER NOT NULL AUTO_INCREMENT,
    team_name TEXT NOT NULL,
    team_city TEXT NOT NULL,
    team_state TEXT NOT NULL,
    team_stadium TEXT NOT NULL,
    PRIMARY KEY (team_id)
    ) $charset_collate;”;
    dbDelta( $sql );
    }
    register_activation_hook( __FILE__, ‘sports_bench_create_db’ );

    function sports_bench_team_admin_menu() {
    global $team_page;
    add_menu_page( __( ‘Teams’, ‘sports-bench’ ), __( ‘Teams’, ‘sports-bench’ ), ‘edit_posts’, ‘add_data’, ‘sports_bench_teams_page_handler’, ‘dashicons-groups’, 6 ) ;
    }
    add_action( ‘admin_menu’, ‘sports_bench_team_admin_menu’ );

    function sports_bench_teams_form_page_handler() {
    global $wpdb;
    echo ‘
    Team Name:
    Team City:
    Team State:
    Team Stadium:

    ‘;

    $default_row = $wpdb->get_row( “SELECT * FROM $table_name ORDER BY team_id DESC LIMIT 1″ );
    if ( $default_row != null ) {
    $id = $default_row->team_id + 1;
    } else {
    $id = 1;
    }
    $default = array(
    ‘team_id’ => $id,
    ‘team_name’ => ”,
    ‘team_city’ => ”,
    ‘team_state’ => ”,
    ‘team_stadium’ => ”,
    );
    $item = shortcode_atts( $default, $_REQUEST );

    $wpdb->insert( $table_name, $item );
    }

    What may be I made mistake. Please could you help me on this.
    Thank you

    Reply
  2. rajan

    I tried your code above it created table but from admin menu team it do not shows any form or ‘Hello World’

    Please could you help me on this.

    Reply
    • Jacob Martella

      Hello Rajan,
      So I just want to check, but is the page showing in the admin menu or not? Just want to see if that’s the main issue. As for the form for the page, the code you pasted in the other comment is missing the code for the form to display it. So that might be the problem.

      Reply
  3. rajan

    Jacob Thakyou for your reply.
    Yes, In admin Menu ‘Teams’ is created

    and with this code in plugin database also created.
    function sports_bench_create_db() {
    global $wpdb;
    $charset_collate = $wpdb->get_charset_collate();
    require_once( ABSPATH . ‘wp-admin/includes/upgrade.php’ );

    //* Create the teams table
    $table_name = $wpdb->prefix . ‘sb_teams’;
    $sql = “CREATE TABLE $table_name (
    team_id INTEGER NOT NULL AUTO_INCREMENT,
    team_name TEXT NOT NULL,
    team_city TEXT NOT NULL,
    team_state TEXT NOT NULL,
    team_stadium TEXT NOT NULL,
    PRIMARY KEY (team_id)
    ) $charset_collate;”;
    dbDelta( $sql );
    }
    register_activation_hook( __FILE__, ‘sports_bench_create_db’ );

    Then below it I pasted the above code from this blog

    function sports_bench_team_admin_menu() {
    global $team_page;
    add_menu_page( __( ‘Teams’, ‘sports-bench’ ), __( ‘Teams’, ‘sports-bench’ ), ‘edit_posts’, ‘add_data’, ‘sports_bench_teams_page_handler’, ‘dashicons-groups’, 6 ) ;
    }
    add_action( ‘admin_menu’, ‘sports_bench_team_admin_menu’ );

    function sports_bench_teams_form_page_handler() {
    global $wpdb;
    echo ‘
    Team Name:
    Team City:
    Team State:
    Team Stadium:

    ‘;

    $default_row = $wpdb->get_row( “SELECT * FROM $table_name ORDER BY team_id DESC LIMIT 1″ );
    if ( $default_row != null ) {
    $id = $default_row->team_id + 1;
    } else {
    $id = 1;
    }
    $default = array(
    ‘team_id’ => $id,
    ‘team_name’ => ”,
    ‘team_city’ => ”,
    ‘team_state’ => ”,
    ‘team_stadium’ => ”,
    );
    $item = shortcode_atts( $default, $_REQUEST );

    $wpdb->insert( $table_name, $item );
    }

    but clicking on ‘Teams’ from admin menu it do not shows anything.
    Also tried adding only
    echo ‘Hello World!’;
    but also it did not displayed anything.

    Sorry for making you trouble, but don’t know where I missed code for displaying form.

    Thankyou

    Reply