Creating custom database table
WordPress by default comes with an organized structure of tables, but if we are dealing with our custom post types it does not make any sense to use to store the data in the default table as it won’t be classified with the same. For this we can create our custom table in the wordpress and use it to handle the data modifications and retrievals.
There are some things we must keep in mind while creating our table :
- We must try to match the structure WordPress provides, for consistency
- Don’t overuse the tables, if something can be efficiently did with the default tables, let it be that way
- We should properly use the primary keys and collation settings
By default WordPress provides us with a function which can be used to modify the database structure ( which includes creating new tables )
dbDelta()
We must do the database creation in the activation hook of our plugin so that the table creation code is not run multiple times causing issues.
We can just directly pass a create table sql query to the dbDelta function and it should do all the works for us.
Some points when writing the query:
use base_prefix from $wpdb to get the prefix for different blog ids if we want to create different tables for different blog ids on a multi site setup.
use prefix from $wpdb to get the default wordpress prefix if we do not want the blog id to be added to the prefix and we want to create a single table for all our sites.
register_activation_hook( __FILE__, 'activate_plugin' );
function activate_plugin() {
require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta( $our_good_query );
}Example of creating a table
register_activation_hook( __FILE__, 'activate_plugin' );
function activate_plugin() {
global $wpdb;
$collate = $wpdb->get_charset_collate();
$table = $wpdb->prefix . 'test';
$query = "CREATE TABLE $table (
id INT(255) NOT NULL AUTO_INCREMENT,
test1 VARCHAR(20) NOT NULL,
test2 DATETIME NOT NULL,
UNIQUE KEY id (id)
) $collate;";
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
dbDelta( $query );
}This would register a table for us at the time of plugin activation.
Another thing to keep in mind, that when creating a table we must use the syntax CREATE IF NOT EXISTS -> so that if the table is already there it should not cause us any error.
A simple code for creating tables on activation and deleting them on uninstallation
register_activation_hook( __FILE__, 'activate_plugin' );
function activate_plugin() {
global $wpdb;
$collate = $wpdb->get_charset_collate();
$table = $wpdb->prefix . 'test';
$query = "CREATE TABLE $table (
id INT(255) NOT NULL AUTO_INCREMENT,
test1 VARCHAR(20) NOT NULL,
test2 DATETIME NOT NULL,
UNIQUE KEY id (id)
) $collate;";
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
dbDelta( $query );
}
register_uninstall_hook( __FILE__, 'plugin_uninstall');
function plugin_uninstall() {
global $wpdb;
$table = $wpdb->prefix . 'test';
$query = "DROP TABLE $table;";
$wpdb->query( $query );
}We should prefer deleting tables at uninstall instead of deactivation so that the table is not accidentally deleted when the user deactivates a plugin.