Mircian

Adding a custom table to the WordPress database

mircian

Many times in a WordPress plugin you want to store data in an easy accessible way without bloating the default WP tables. That’s when a custom database table comes in handy.

This isn’t something new just a way to do it clean and some warnings from troubles encountered in live environments.

The code

This is just a simple example of a database class which I include in custom plugins:

class M_Db {

    private static $db_version = '1.0.0';
    const DB_VERSION_NAME = 'm_db_version';
    const POSTS_TABLE = 'm_info';

    public static function install() {
        global $wpdb;

        $installed_ver = get_option(self::DB_VERSION_NAME);

        if ( $installed_ver != self::$db_version ) {

            $table_name = $wpdb->prefix . self::POSTS_TABLE;

            $charset_collate = $wpdb->get_charset_collate();

            $sql = "CREATE TABLE $table_name (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            user_id bigint(20) DEFAULT 0 NOT NULL,
            region VARCHAR(50) NOT NULL,
               UNIQUE KEY id (id)
           ) $charset_collate;";

            require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
            dbDelta( $sql );

            update_option( self::DB_VERSION_NAME, self::$db_version );
        }
    }

}

This code will allow you to create a custom table and also easily update its structure using the version check. After a change is made ( like adding a new column ) you just need to change the static version and the table will be updated.

Of course, you don’t want to run this code each time the website is loaded so you’ll have to call the “install” function from the proper hook.

In a plugin ( and hopefully you won’t be creating custom tables with a theme! ) you should run this on plugin activation by adding this code to the main plugin file:

register_activation_hook( __FILE__, array( 'M_Db', 'install' ) )

Warnings

Exit mobile version