Plugin database tables in Wordpress

Introduction

Plugins often need to store their own data. Creating custom database tables helps keep plugin data organized and separate from WordPress core data.

When a plugin needs to save complex or large amounts of data that don't fit well into existing WordPress tables.

When you want to improve performance by avoiding overloading default tables like wp_options.

When you need to store structured data with relationships, like orders, bookings, or custom logs.

When plugin data must be isolated for easier backup, migration, or removal.

When you want to run custom queries efficiently on plugin-specific data.

Syntax

Wordpress

<?php
function myplugin_create_table() {
    global $wpdb;
    $table_name = $wpdb->prefix . 'myplugin_table';
    $charset_collate = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE $table_name (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        name varchar(255) NOT NULL,
        created datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
        PRIMARY KEY  (id)
    ) $charset_collate;";

    require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
    dbDelta( $sql );
}
add_action( 'plugins_loaded', 'myplugin_create_table' );

Use $wpdb->prefix to get the correct table prefix for the site.

Use dbDelta() to safely create or update tables without losing data.

Examples

This example creates a table to store user messages with an ID, text, user reference, and timestamp.

Wordpress

<?php
// Create a simple table for storing messages
function create_messages_table() {
    global $wpdb;
    $table_name = $wpdb->prefix . 'messages';
    $charset_collate = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE $table_name (
        message_id bigint(20) NOT NULL AUTO_INCREMENT,
        message_text text NOT NULL,
        user_id bigint(20) NOT NULL,
        sent_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
        PRIMARY KEY  (message_id)
    ) $charset_collate;";

    require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
    dbDelta( $sql );
}
add_action( 'plugins_loaded', 'create_messages_table' );

This example shows how to add a new column to an existing plugin table using a direct SQL query.

Wordpress

<?php
// Add a new column to an existing plugin table
function update_myplugin_table() {
    global $wpdb;
    $table_name = $wpdb->prefix . 'myplugin_table';

    $status_column = $wpdb->get_results( $wpdb->prepare("SHOW COLUMNS FROM $table_name LIKE %s", 'status') );
    if ( empty( $status_column ) ) {
        $sql = "ALTER TABLE $table_name ADD COLUMN status varchar(20) DEFAULT 'active' NOT NULL;";
        $wpdb->query($sql);
    }
}
add_action( 'plugins_loaded', 'update_myplugin_table' );

Sample Program

This plugin creates a custom table, inserts a sample row, and displays the stored data in the WordPress admin area.

Wordpress

<?php
/**
 * Plugin Name: Simple Plugin Table Example
 */

function spt_create_table() {
    global $wpdb;
    $table_name = $wpdb->prefix . 'spt_data';
    $charset_collate = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE $table_name (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        data varchar(100) NOT NULL,
        created_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
        PRIMARY KEY  (id)
    ) $charset_collate;";

    require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
    dbDelta( $sql );
}
add_action( 'plugins_loaded', 'spt_create_table' );

function spt_insert_sample_data() {
    global $wpdb;
    $table_name = $wpdb->prefix . 'spt_data';

    $row_count = $wpdb->get_var( "SELECT COUNT(*) FROM $table_name" );
    if ( $row_count == 0 ) {
        $wpdb->insert(
            $table_name,
            [
                'data' => 'Hello World'
            ]
        );
    }
}
add_action( 'plugins_loaded', 'spt_insert_sample_data' );

function spt_show_data() {
    global $wpdb;
    $table_name = $wpdb->prefix . 'spt_data';

    $results = $wpdb->get_results( "SELECT * FROM $table_name" );

    foreach ( $results as $row ) {
        echo "ID: {$row->id}, Data: {$row->data}, Created At: {$row->created_at}<br>";
    }
}
add_action( 'admin_notices', 'spt_show_data' );

Important Notes

Always use dbDelta() for creating or updating tables to avoid data loss.

Use the $wpdb object for safe database access and to respect WordPress table prefixes.

Remember to sanitize and validate any data before inserting it into your tables.

Summary

Plugins use custom database tables to store their own data separately from WordPress core.

Use $wpdb->prefix and dbDelta() to create and update tables safely.

Custom tables help organize data, improve performance, and keep plugin data isolated.

Practice

1. Why do WordPress plugins create their own database tables instead of using existing WordPress tables?

easy

A. To avoid using any database at all

B. Because WordPress does not allow plugins to use existing tables

C. To keep plugin data separate and organized for better performance

D. To make the plugin load slower intentionally

Solution

1. Step 1: Understand plugin data management

   Plugins create their own tables to store data separately from WordPress core data.

2. Step 2: Benefits of separate tables

   This separation helps organize data and improves performance by avoiding clutter in core tables.

3. Final Answer:

   To keep plugin data separate and organized for better performance -> Option C

4. Quick Check:

   Separate tables improve organization and performance [OK]

Hint: Separate plugin data for better organization and speed [OK]

Common Mistakes:

• Thinking WordPress forbids using core tables
• Believing plugins avoid databases entirely
• Assuming slower load is intentional

2. Which of the following is the correct way to get the table name with the WordPress prefix for a plugin table named myplugin_data?

easy

A. $wpdb->prefix . 'myplugin_data'

B. $wpdb->myplugin_data

C. myplugin_data

D. $wpdb->prefix('myplugin_data')

Solution

1. Step 1: Understand $wpdb prefix usage

   $wpdb->prefix contains the WordPress table prefix, usually 'wp_'.

2. Step 2: Correct concatenation for table name

   Concatenate $wpdb->prefix with the table name string to get the full table name.

3. Final Answer:

   $wpdb->prefix . 'myplugin_data' -> Option A

4. Quick Check:

   Prefix plus table name string = correct table name [OK]

Hint: Use $wpdb->prefix . 'table_name' for plugin tables [OK]

Common Mistakes:

• Using $wpdb->myplugin_data directly
• Not adding prefix to table name
• Calling $wpdb->prefix as a function

3. Given this code snippet in a plugin activation hook:

$table_name = $wpdb->prefix . 'myplugin_data';
$sql = "CREATE TABLE $table_name (
  id mediumint(9) NOT NULL AUTO_INCREMENT,
  name varchar(100) NOT NULL,
  PRIMARY KEY  (id)
);";
dbDelta($sql);

What will happen when this code runs?

medium

A. It updates existing rows in the table

B. It creates the table with columns id and name if it doesn't exist

C. It causes a syntax error because dbDelta cannot create tables

D. It deletes the table if it exists

Solution

1. Step 1: Understand dbDelta function

   dbDelta safely creates or updates database tables based on the SQL CREATE statement.

2. Step 2: Analyze the SQL statement

   The SQL creates a table with id and name columns and a primary key on id.

3. Final Answer:

   It creates the table with columns id and name if it doesn't exist -> Option B

4. Quick Check:

   dbDelta creates or updates tables safely [OK]

Hint: dbDelta creates or updates tables from SQL CREATE statements [OK]

Common Mistakes:

• Thinking dbDelta deletes tables
• Assuming dbDelta causes syntax errors
• Confusing dbDelta with data update functions

4. A plugin developer wrote this code to create a table:

$table_name = $wpdb->prefix . 'myplugin_data';
$sql = "CREATE TABLE $table_name (
  id int NOT NULL AUTO_INCREMENT,
  name varchar(100) NOT NULL,
  PRIMARY KEY (id)
)";
dbDelta($sql);

But the table is not created after activation. What is the likely problem?

medium

A. dbDelta requires the SQL string to end with a semicolon

B. Missing semicolon at the end of the SQL statement

C. Not calling $wpdb->query() before dbDelta()

D. Using int instead of mediumint for id

Solution

1. Step 1: Check SQL syntax for dbDelta

   dbDelta requires the SQL CREATE statement to end with a semicolon to parse correctly.

2. Step 2: Identify missing semicolon

   The SQL string lacks a semicolon after the closing parenthesis, causing dbDelta to fail creating the table.

3. Final Answer:

   dbDelta requires the SQL string to end with a semicolon -> Option A

4. Quick Check:

   SQL must end with semicolon for dbDelta [OK]

Hint: Always end SQL string with semicolon for dbDelta [OK]

Common Mistakes:

• Ignoring semicolon requirement
• Thinking int vs mediumint causes failure
• Calling $wpdb->query() unnecessarily

5. You want to add a new column email varchar(255) NOT NULL to your plugin's custom table myplugin_data during an update. Which approach correctly updates the table without losing data?

hard

A. Ignore the database and store emails in a separate file

B. Drop the table and recreate it with the new column

C. Use $wpdb->query() to run ALTER TABLE and then run dbDelta() with old SQL

D. Modify the original CREATE TABLE SQL to include the new column and run dbDelta() again

Solution

1. Step 1: Understand dbDelta update behavior

   dbDelta compares the current table structure with the SQL and adds missing columns safely.

2. Step 2: Update CREATE TABLE SQL with new column

   Modify the SQL to include the new email column and run dbDelta() to update the table without data loss.

3. Final Answer:

   Modify the original CREATE TABLE SQL to include the new column and run dbDelta() again -> Option D

4. Quick Check:

   dbDelta updates tables by comparing SQL definitions [OK]

Hint: Update CREATE TABLE SQL and rerun dbDelta to add columns [OK]

Common Mistakes:

• Dropping table loses all data
• Running ALTER TABLE manually then old dbDelta
• Avoiding database updates by using files