Tutorial: How to change plugin table structure in WordPress

Some of us will have problem updating or changing your table structure in your WordPress plugin after it has been released to the public. Many people will come up with different ideas to change their existing plugin structure to a new one. Idea such as checking for that particular column existence either through pure SQL or mixture of SQL and PHP. However, the approach here may be a bit overkill. There is a much simpler way.

Mistakes Made By WordPress Developers

In most plugin tutorial on Google, we will see the normal declaration that everyone is familiar.

$table = $wpdb->prefix."hungred_post_thumbnail";
$structure = "CREATE TABLE  `".$table."` (
	hpt_post varchar(255) NOT NULL,
	hpt_name longtext NOT NULL,
	hpt_url longtext NOT NULL,
	hpt_loc longtext NOT NULL,
	UNIQUE KEY id (hpt_post)
);";
$wpdb->query($structure);

This is the usual code instruction during a plugin tutorial. However, the problem with this is that it makes maintenance of plugin difficult. Assuming you are trying to update the table structure with the above statement. You will find difficulty and resort to different means of getting your plugin table structure updated while keeping the same declaration in WordPress. This is not the right way to create a WordPress table!

Powerful way of creating plugin table in WordPress

We need something more powerful to take the job. In WordPress, there is a function dbDelta which will compare the structure between the existing table and the one in the WordPress database. It will automatic update the missing or extra field and alter the table for you. However, this method doesn't exist in WordPress default setup. You will have to import update.php to get this function. Furthermore, dbDelta will required a few criteria to be met before it is usable.

• You have to put each field on its own line in your SQL statement.
• You have to have two spaces between the words PRIMARY KEY and the definition of your primary key.
• You must use the key word KEY rather than its synonym INDEX

Hence, you will have the following declaration.

$table = $wpdb->prefix."hungred_post_thumbnail";
$structure = "CREATE TABLE `".$table."` (
	hpt_post varchar(255) NOT NULL,
	hpt_name longtext NOT NULL,
	hpt_url longtext NOT NULL,
	hpt_loc longtext NOT NULL,
	UNIQUE KEY id (hpt_post)
);";
require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta($structure);

Just that simple. Once you utilized this, changing the structure of your WordPress plugin table will be an ease.