Plugin database tables in Wordpress - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ component_behavior

intermediate

How does a WordPress plugin typically create its database tables?

When a WordPress plugin needs to store custom data, it often creates its own database tables. How does the plugin usually create these tables?

A By running SQL commands directly inside the plugin's main file without any checks.

B By modifying the WordPress core database files manually during plugin activation.

C By using the <code>register_activation_hook</code> to run a function that creates tables with <code>dbDelta()</code> after checking the table doesn't exist.

D By creating tables only when the plugin's admin page is loaded for the first time.

💡 Hint

Think about how plugins safely create tables only once during activation.

WordPress plugins use register_activation_hook to run code when the plugin activates. Inside that code, they use dbDelta() to create or update tables safely. This avoids errors and ensures tables are created only once.

📝 Syntax

intermediate

Identify the correct SQL syntax for creating a plugin table with charset in WordPress

Which SQL statement correctly creates a plugin table with the proper charset and collation in WordPress?

A CREATE TABLE wp_myplugin_data (id INT NOT NULL) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

B CREATE TABLE wp_myplugin_data (id INT NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

C CREATE TABLE wp_myplugin_data (id INT NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

D CREATE TABLE wp_myplugin_data (id INT NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

💡 Hint

WordPress recommends using InnoDB engine and utf8mb4 charset for compatibility.

Option B uses InnoDB engine and utf8mb4 charset with unicode collation, which is the recommended setup for WordPress plugin tables to support emojis and multilingual content.

🔧 Debug

advanced

Why does this plugin table creation code fail to create the table?

Consider this plugin activation code snippet:

global $wpdb;
$table_name = $wpdb->prefix . 'myplugin';
$sql = "CREATE TABLE $table_name (
  id mediumint(9) NOT NULL AUTO_INCREMENT,
  name varchar(255) NOT NULL,
  PRIMARY KEY  (id)
)";
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
dbDelta( $sql );

Why might the table not be created?

A The SQL statement has a syntax error: the closing parenthesis is followed by a semicolon inside the string.

B The SQL statement is missing the charset and collation specification, so dbDelta ignores it.

C The <code>dbDelta</code> function requires the SQL string to have the <code>ENGINE=InnoDB</code> clause to create tables.

D The <code>dbDelta</code> function only works if the table name is lowercase without prefix.

💡 Hint

Check the SQL syntax carefully, especially punctuation inside the string.

The SQL string ends with a closing parenthesis followed immediately by a semicolon inside the string. dbDelta expects no semicolon at the end of the SQL statement. This causes the table creation to fail.

❓ state_output

advanced

What is the value of $table_version after plugin activation with version check?

A plugin stores its database version in the option myplugin_db_version. On activation, it runs this code:

global $wpdb;
$table_version = get_option('myplugin_db_version');
if ( $table_version != '1.0' ) {
  // create or update tables
  update_option('myplugin_db_version', '1.0');
}

If the option myplugin_db_version does not exist before activation, what will be its value after activation?

A null

B '' (empty string)

C false

D '1.0'

💡 Hint

Think about what update_option does after the check.

If the option does not exist, get_option returns false, so the condition is true. The plugin then updates the option to '1.0'. After activation, the option value is '1.0'.

🧠 Conceptual

expert

Which approach ensures plugin database tables are updated safely on plugin upgrade?

When a plugin updates to a new version that changes the database schema, what is the best practice to update the plugin's database tables safely?

A Use a version number stored in options to detect schema changes and run incremental <code>dbDelta()</code> calls during admin_init hook.

B Drop the existing tables and recreate them from scratch during plugin activation.

C Manually run ALTER TABLE SQL commands without checking the current schema version.

D Require users to uninstall and reinstall the plugin to update the database tables.

💡 Hint

Think about how to avoid data loss and keep schema in sync.

Storing a database version in options and checking it on admin_init allows the plugin to run dbDelta() safely to update tables incrementally without losing data. Dropping tables or requiring reinstall causes data loss or poor user experience.

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