Plugin interaction with WordPress database. Part 1.

Hello, dear blog readers!
Let’s expand our WordPress programming knowledge a bit and look into how plugins interact with the database.

Out of the box, WP has pretty decent capabilities. I’m referring to things like creating custom taxonomies, terms, and linking them with additional data using metadata. It’s very convenient — once you write a dozen functions, you no longer need to worry about creating interfaces or even some of the internal app logic.

But for me, as someone still "new" to WordPress, it feels more natural to work with clearly structured database tables.
So in this article, I propose to explore that approach — based on one of my plugins called “my-simple-form,” slightly simplified for the purpose of the example.

Data Initialization

As expected, we’ll place our data initialization logic in the main plugin file: `my-simple-form.php`, the same file where the plugin description comment is located.

if(is_admin() == TRUE)
{
	register_activation_hook(__FILE__, 'Plance_MSF_DB::activate');
	register_uninstall_hook(__FILE__, 'Plance_MSF_DB::uninstall');
	register_deactivation_hook(__FILE__, 'Plance_MSF_DB::uninstall');
}

We call the database initialization methods only inside an `if` condition and only for the admin. There’s no point in running this logic for non-admin users.

In my case, the `Plance_MSF_DB` class is very simple and includes two methods:

• activate — called when the plugin is installed. This is where the database tables are created, and, if needed, additional options, metadata, roles, etc.
• uninstall — called when the plugin is uninstalled or deactivated.

Note that the `Plance_MSF_DB::uninstall` method is registered both for the `register_uninstall_hook` and `register_deactivation_hook`.
That means the plugin will be cleaned up both when it’s deactivated and when it’s fully deleted from the system.
To be honest, I don’t remember exactly why I did it this way — in this case, the `register_deactivation_hook` could probably be removed.

Here’s a basic class template:

class Plance_MSF_DB
{
	/**
	 * Plugin Activate
	 */
	public static function activate()
	{	
		return TRUE;
	}
	
	/**
	 * Plugin Uninstall
	 */
	public static function uninstall()
	{	
		return TRUE;
	}
}

I think nothing here is too complicated and doesn’t require further explanation.

Now let’s go over the method that runs on plugin activation:

public static function activate()
{
	global $wpdb;

	require_once(ABSPATH.'wp-admin/includes/upgrade.php');
	
	/* skipped code */

	add_option('plance_msf_notification', array(
		'admin_email' => get_bloginfo('admin_email'),
		'admin_name' => 'Administrator',
		'noreply_email' => get_bloginfo('admin_email'),
		'noreply_name' => get_bloginfo('blogname'),
		'shortcode_name' => 'plance-msf-form',
		'message_subject' => 'Email from Your Site',
		'message_template' => '….',
		'flash_message' => '',
	));
	
	dbDelta("CREATE TABLE IF NOT EXISTS `{$wpdb -> prefix}plance_msf_user` (
		`id` INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
		`name` VARCHAR(40) NULL,
		`surname` VARCHAR(40) NULL,
		`patronymic` VARCHAR(40) NULL,
		`company` VARCHAR(100) NULL,
		`birthday` INT(10) UNSIGNED NULL,
		`phone` VARCHAR(25) NULL,
		`email` VARCHAR(80) NULL,
		`messenger` VARCHAR(30) NULL,
		`site` VARCHAR(100) NULL,
		`country` VARCHAR(30) NULL,
		`city` VARCHAR(30) NULL,
		`address` VARCHAR(100) NULL,
		`credit_card` VARCHAR(20) NULL,
		`sum` VARCHAR(10) NULL,
		`comment` TEXT NULL,
		`image` VARCHAR(20) NULL,
		`file` VARCHAR(20) NULL,
		`radio` VARCHAR(100) NULL,
		`select` VARCHAR(100) NULL,
		`checkbox` TINYINT(1) UNSIGNED NULL,
		`date_create` INT(10) UNSIGNED NOT NULL
	) {$wpdb -> get_charset_collate()};");
	
	return TRUE;
}

Let’s break this method down:
1. We load the global `$wpdb` object — it’s needed for the table prefix and charset info.
2. We include the file `wp-admin/includes/upgrade.php` so we can use the `dbDelta` function.
3. To store global plugin options in the `wp_options` table, we use the `add_option` function.
Here’s an example:

add_option('plance_msf_notification', array(
	'admin_email' => get_bloginfo('admin_email'),
	'admin_name' => 'Administrator',
	'noreply_email' => get_bloginfo('admin_email'),
	'noreply_name' => get_bloginfo('blogname'),
	'shortcode_name' => 'plance-msf-form',
	'message_subject' => 'Email from Your Site',
	'message_template' => $message_template,
	'flash_message' => '',
));

Where:
- `'plance_msf_notification'` is the option name (first parameter)
- `array(...)` is the value. You can also store a string or number, but I usually use an array so all plugin settings are grouped together.
Sometimes, though, it’s better to separate them — for example, when working on a large or evolving project.

Next up is the `dbDelta` function:

dbDelta("CREATE TABLE IF NOT EXISTS `{$wpdb -> prefix}plance_msf_user` (
	...
) {$wpdb -> get_charset_collate()};");

It’s a basic SQL dump to create a table.

That covers data initialization. Now let’s look at the uninstall method:

public static function uninstall()
{
	global $wpdb;

	$wpdb -> query("DROP TABLE IF EXISTS `{$wpdb -> prefix}plance_msf_user`");
	delete_option('plance_msf_notification');

	return TRUE;
}

The `$wpdb->query(...)` method deletes the database table,
And `delete_option` removes the plugin options from the `wp_options` table.

That’s all for now.