Blogkeyboard_arrow_right

How to programmatically insert a new table into a Database in WordPress

,

How to programmatically insert a new table into a Database in WordPress

by

Adebola Adeniran

4 mins read

Recently, at AEEIEE, we needed to programmatically insert a new table into the WordPress database for a plugin we were developing. The table had to hold certain data about users. We wanted this table to be tied directly to our plugin and not be affected by changes around the site.

For our use case, we needed to keep track of the IDs of the posts being viewed by a user as well as the City and Country users were viewing a post from.

In this short article, I’ll go on to explain how we achieved that.

Initial Setup steps

We will create a plugin that inserts a new table into the database when a plugin is activated. At AEEIEE, we follow a lot of OOP design principles and our code is architected following that pattern. We also use the DocBlocks style to add specially formatted comments to our code. Code blocks in this article will follow a similar pattern.

In your plugins folder within your WordPress directory, create a new file. We’ll call ours dummy_plugin.php. In this file, add the following block of code. The first part of the code gives WordPress information about the plugin we’re creating.

<?php
/**
 * Aeeiee Demo Plugin
 *
 * @author Aeeiee Inc.
 *
 * @wordpress-plugin
 * Plugin Name: Aeeiee Demo Plugin
 * Description: This plugin creates a new table in the database when initialized.
 * Author: Aeeiee Inc.
 * Author URI: https://www.aeeiee.com
 * Version: 1.0
 * Requires PHP: 7.2
 */

// Create a class
class AeeieeDemoPlugin {

    /**
     * Class Constructor
     * @author Adebola
     * @param None
     * @return void
     */

    public function __construct() {
    // This hook will run when the plugin is activated and call the activate function
        register_activation_hook(__FILE__, 'insert_demo_table_into_db');
    }


    /** This function inserts a new table into the db when the plugin is activated.
     * @author Adebola
     * @param None
     * @return void
     */
    public function insert_demo_table_into_db(){

    }
}

// Function that instantiates the class
function aeeiee_demo_plugin() {
    new AeeieeDemoPlugin();
}
aeeiee_demo_plugin();

Writing the function to insert the table into the Database

Time for the main bit! Within our insert_demo_table_into_db function, we’ll write the logic for inserting the table into the database.

public function insert_demo_table_into_db(){

    global $wpdb;

    // set the default character set and collation for the table
    $charset_collate = $wpdb->get_charset_collate();

    // Check that the table does not already exist before continuing
    $sql = "CREATE TABLE IF NOT EXISTS `{$wpdb->base_prefix}demo_table` (
           id bigint(50) NOT NULL AUTO_INCREMENT,
           post_id bigint(20) NOT NULL,
           user_id bigint(20),
           city varchar(100),
           country varchar(100),
           PRIMARY KEY (id),
           FOREIGN KEY (post_id) REFERENCES wp_posts(ID)
    ) $charset_collate;";

    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta( $sql );
    $is_error = empty( $wpdb->last_error );

    return $is_error;
}

The line CREATE TABLE IF NOT EXISTS will run a check on our Database if the table we’re about to create already exists. If it doesn’t, we ask MYSQL to go ahead with creating the table.

Next, we call the wpdb->base_prefix method to programmatically prefix the name of the table we want to create so it’s in the form wp_dummy_table.

Afterward, we write some SQL statements to specify the columns we want to create on our table. NOTICE that when we use PRIMARY KEY or FOREIGN KEY, we leave two spaces between KEY and the rest of the query. Your SQL query MUST be formatted like this to be correctly interpreted by WordPress.

Finally, we pass our $sql  query string into the dbDelta function. dbDelta is a WordPress function that alters the database based on the SQL queries passed to it.

After calling the dbDelta method, we run a check for errors using $wpdb->last_error . We store the result of that call in the $is_error variable.

You can now head over to the Plugins page on your Admin dashboard to activate the Plugin. And that’s it! You have successfully added a new table to your WordPress Database. It may also be worth your while going through the official WordPress documentation on creating a new table in the WordPress database.

Adebola Adeniran