Using WordPress ‘dbDelta()’ PHP function

by

The dbDelta() WordPress PHP function is useful for modifying the database. It can create new tables and update existing ones according to the provided structure. This function accepts SQL queries, either as an array or a string separated by semicolons, and has an option to execute the query immediately.

Usage

Let’s say you have a WordPress website and you want to create a new table called ‘user_activity’ to track the activities of your users. Here’s how you can use the dbDelta() function.

global $wpdb;

$table_name = $wpdb->prefix . 'user_activity';
$charset_collate = $wpdb->get_charset_collate();

$sql = "CREATE TABLE $table_name (
  id mediumint(8) NOT NULL AUTO_INCREMENT,
  user_id mediumint(8) NOT NULL,
  activity text NOT NULL,
  date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  PRIMARY KEY  (id)
) $charset_collate;";

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

Parameters

  • $queries (string|array): The SQL query(ies) to run. It can be multiple queries in an array, or a string of queries separated by semicolons. Default: ”
  • $execute (bool): Specifies whether or not to execute the query right away. Default: true

More information

See WordPress Developer Resources: dbDelta()
This function is very sensitive to the format of SQL commands. Proper syntax and spacing are critical. Please note that the function doesn’t support FOREIGN KEY constraints and it’s not recommended to change the structure of a table using this function as it might not work as expected.

Examples

Creating a new table

This code creates a new table ‘user_activity’ in the WordPress database to track user activities.

global $wpdb;

$table_name = $wpdb->prefix . 'user_activity';
$charset_collate = $wpdb->get_charset_collate();

$sql = "CREATE TABLE $table_name (
  id mediumint(8) NOT NULL AUTO_INCREMENT,
  user_id mediumint(8) NOT NULL,
  activity text NOT NULL,
  date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  PRIMARY KEY  (id)
) $charset_collate;";

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

Adding a new column

This code adds a new column ‘last_login’ to the ‘user_activity’ table.

global $wpdb;

$table_name = $wpdb->prefix . 'user_activity';
$charset_collate = $wpdb->get_charset_collate();

$sql = "CREATE TABLE $table_name (
  id mediumint(8) NOT NULL AUTO_INCREMENT,
  user_id mediumint(8) NOT NULL,
  activity text NOT NULL,
  date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  last_login datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  PRIMARY KEY  (id)
) $charset_collate;";

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