Creating Your Own Tables in WordPress Database

WordPress Database

The data schema in WordPress is quite flexible, and after the appearance of arbitrary post types in the core of WordPress, almost any data can be placed in a table in some way wp_postsand work with it using standard tools. But in practice, this is not always optimal, and sometimes it becomes necessary to create your own table in the WordPress database. There are obvious downsides to this approach:

  • No option to use WP_Query to access data
  • Data in the custom table does not use object caching
  • You have to build the user interface for working with data yourself
  • Roles, privileges, output patterns, and permalink formats also have to be taken care of.
  • No option to use WordPress taxonomy to group data

There is of course one plus that often justifies using custom tables in WordPress: we define the structure, data types, and indexes ourselves. And when working with a large amount of data, this can significantly affect the speed of the site.

dbDelta() function

The built-in function dbDelta()allows you to create tables in the WordPress database, and make changes to their structure. But before creating a new table, you need to decide on its name and encoding.

Also See: Zend Server Z-Ray for WordPress Developers

The name of the new table must have the same prefix used by the WordPress core (default wp_) and an additional prefix for our plugin or project, for example, wp_my_table_name. The prefix wp_can be obtained using the get_blog_prefix()global object method, and the encoding used can be determined using the and $wpdbproperties :charsetcollate

global $wpdb;
$table_name = $wpdb->get_blog_prefix() . 'my_products';
$charset_collate = "DEFAULT CHARACTER SET {$wpdb->charset} COLLATE {$wpdb->collate}";

Next, using the function, dbDelta()we can create a new table:

require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
$sql = "CREATE TABLE {$table_name} (
id int(11) unsigned NOT NULL auto_increment,
name varchar(255) NOT NULL default '',
price int(11) unsigned NOT NULL default '0',
PRIMARY KEY (id),
KEY price (price)
) {$charset_collate};";

// Create table.
dbDelta($sql);

The function dbDelta()is different from a simple MySQL query to the WordPress database. It breaks the table structure into parts and compares it with the table that already exists. This allows dbDelta()you to make changes to the table structure without having to delete and re-create the table.

Also See: Zend Server Z-Ray for WordPress Developers

For example, to add a new column to our table, we can modify our main query CREATE TABLElike this:

$sql = "CREATE TABLE {$table_name} (
id int(11) unsigned NOT NULL auto_increment,
name varchar(255) NOT NULL default '',
price int(11) unsigned NOT NULL default '0',
color varchar(255) NOT NULL default '',
PRIMARY KEY (id),
KEY price (price)
) {$charset_collate};";

At the same time, by splitting the query into parts, the function dbDelta()will understand that a new column has been added color, and will execute the corresponding query ALTER TABLEon the existing table in the WordPress database. In order to check what exactly the function did, dbDeltawe can display its result, for example, using var_dump().

Also See: Add Footnotes in WordPress with Simple Footnotes

This behavior of the function dbDelta()allows it to be used repeatedly on the same table, but keep in mind that on each call dbDelta()it will poll the database for the structure of the required table, so doing it dbDelta()on every page load is not optimal.

Table versions and dbDelta()

In our plugin, we can specify the current version of the data schema and update it only when we actually change the schema of our own table. And when updating the schema with, dbDelta()we can record the version in the WordPress option so that such an update is not performed more than once.

class My_Plugin_Class {
public $db_version = 1;

function __construct() {
add_action( 'init', array( $this, 'init' ) );
}

function init() {
$installed_db_version = get_option( 'my_db_version', 0 );
if ( version_compare( $this->db_version, $installed_db_version, '>' ) )
$this->upgrade();
}

function upgrade() {
// dbDelta() is called here
update_option( 'my_db_version', $this->db_version );
}
}
new My_Plugin_Class;

So during the init event, our plugin checks the installed schema version in the WordPress options.

If the installed version is missing or less than the current version, we call upgrade()our plugin method where we can use a function dbDelta()to update the data schema, and finally update the version in the WordPress options.

With this approach, after changing the data structure in the request, dbDelta()we only need to increase the class variable $db_version.

Multisite

In Multisite mode, the code described above will work for each site in the network separately, i.e. each site will have its own table wp_*_my_products. In most cases, this is exactly what the developer intended.

Also see: Add Footnotes in WordPress with Simple Footnotes

However, sometimes we need to create a so-called “global” table, which will be the only one in the entire network. In this case, we need to make two small changes:

  • Specify 0 in the call get_blog_prefix()to use the global WordPress database prefix.
  • Use the get_site_option()and functions update_site_option()to store the schema version of the data only at the primary site on the network.

Requests

As we mentioned at the beginning of the article, we cannot use a class WP_Queryor any other helper functions in WordPress to get data from our new table. Queries must be built manually and run using the object $wpdb:

function get_my_product( $product_id ) {
global $wpdb;
$table_name = $wpdb->get_blog_prefix() . 'my_products';

$product = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table_name} WHERE `id` = %d LIMIT 1;", $product_id ) );
return $product;

This function will return a product with a specific ID from the database. Note that if you call the same function again with the same ID, it will re-query the MySQL database. To avoid this, you can use WordPress object caching.

Separately, you should pay attention to the method of the prepare()object $wpdb. It allows you to specify the format of the variables passed to the WordPress database and in most cases protects against SQL injection.

You can read more about using an object $wpdbto query the WordPress database in the codex.

Leave a comment

Your email address will not be published. Required fields are marked *