Plugin database tables in Wordpress - Step-by-Step Execution

Concept Flow - Plugin database tables

Plugin Activation

↓

Check if DB Tables Exist?

Yes→Skip Creation

No↓

Create Plugin DB Tables

↓

Store Table Names & Versions

↓

Plugin Uses Tables for Data Storage

↓

Plugin Deactivation or Update

↓

Optional: Update or Remove Tables

When a plugin activates, it checks if its database tables exist. If not, it creates them, stores info, and uses them for data. On updates or deactivation, it may update or remove tables.

Execution Sample

Wordpress

<?php
function plugin_activate() {
  global $wpdb;
  $table_name = $wpdb->prefix . 'myplugin_data';
  if($wpdb->get_var("SHOW TABLES LIKE '$table_name'") != $table_name) {
    $charset_collate = $wpdb->get_charset_collate();
    $sql = "CREATE TABLE $table_name (id mediumint(9) NOT NULL AUTO_INCREMENT, data text NOT NULL, PRIMARY KEY  (id)) $charset_collate;";
    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    dbDelta($sql);
    add_option('myplugin_db_version', '1.0');
  }
}

This code runs on plugin activation to create a custom database table if it doesn't exist.

Execution Table

Step	Action	Check	Result	Next Step
1	Plugin activates	N/A	Start activation	Check if table exists
2	Check table existence	SHOW TABLES LIKE 'wp_myplugin_data'	Table not found	Create table
3	Create table SQL prepared	N/A	SQL ready with charset	Run dbDelta()
4	Run dbDelta()	Execute CREATE TABLE	Table created successfully	Store version info
5	Store version info	N/A	Version saved in options	Activation complete
6	Activation complete	N/A	Plugin ready to use tables	End

💡 Table exists or created, activation finishes successfully

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
$table_name	undefined	wp_myplugin_data	wp_myplugin_data	wp_myplugin_data	wp_myplugin_data
$charset_collate	undefined	undefined	utf8mb4_unicode_ci	utf8mb4_unicode_ci	utf8mb4_unicode_ci
$sql	undefined	undefined	CREATE TABLE SQL string	CREATE TABLE SQL string	CREATE TABLE SQL string

Key Moments - 3 Insights

Why do we check if the table exists before creating it?

To avoid errors and duplicate tables, the plugin checks if the table already exists (see execution_table step 2). If it exists, creation is skipped.

What does dbDelta() do with the SQL?

dbDelta() runs the SQL safely, creating or updating the table structure without losing data (see execution_table step 4).

Why store the table version after creation?

Storing the version helps the plugin know if it needs to update the table structure later (see execution_table step 5).

Visual Quiz - 3 Questions

Look at the execution_table, what happens at step 2?

A The plugin checks if the database table exists

B The plugin creates the database table

C The plugin stores the version info

D The plugin finishes activation

💡 Hint

Refer to execution_table row with Step 2 describing the check for table existence

According to variable_tracker, what value does $charset_collate have after step 3?

A undefined

B utf8mb4_unicode_ci

C wp_myplugin_data

D CREATE TABLE SQL string

💡 Hint

Check variable_tracker row for $charset_collate at After Step 3 column

If the table already exists, which step in execution_table is skipped?

A Step 2

B Step 3

C Step 4

D Step 5

💡 Hint

Look at execution_table step 2 result 'Table not found' triggers creation at step 4; if table exists, creation (step 4) is skipped

Concept Snapshot

Plugin database tables:
- On activation, check if tables exist
- If not, create tables with dbDelta()
- Store version info for updates
- Use $wpdb->prefix for table names
- Use charset collate for compatibility
- Avoid duplicate tables by checking first

Full Transcript

When a WordPress plugin activates, it needs to store data in custom database tables. The plugin first checks if its tables already exist using a SQL query. If the tables do not exist, it prepares a CREATE TABLE SQL statement including the correct character set and collation. Then it runs the dbDelta() function to create or update the table safely. After creation, the plugin stores the table version in the WordPress options to track future updates. This process prevents errors from duplicate tables and ensures the plugin can manage its data storage properly. Variables like $table_name hold the full table name with prefix, and $charset_collate ensures the table uses the right encoding. This flow is essential for plugins that need custom data storage beyond WordPress default tables.

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