This entry is part 3 of 5 in the Building a Database Abstraction Layer Series
Once you have laid out the structure for your database table, it is time to create the actual tables. This is where it gets fun. When the tables are created, you can really begin building your API for interacting with the database and that’s when this whole project begins to truly take shape.
To create our table, we will use a function in WordPress called dbDelta(), but before we dive into that, we need to briefly review the MySQL syntax for creating a table.
We use use the CREATE TABLE statement to instruct MySQL to create our table. The statement includes a lot of optional parameters so if this is your first time using it, do not get overwhelmed when reading the documentation page linked above. We will only be using a few of the basics.
The basic statement looks like this:
CREATE TABLE table_name (create_definition) [table_options] [partition_options] |
- table_name – This is the name we will give the table
- (create_definition) – This is where we will specify all of our columns for the table
- [table_options] – This is where we can specify the store engine and the character set and encoding we want to use
- [partition_options] – This is for some very advanced options we are not going to cover
Let’s go ahead and look at the syntax for creating a simple table that has four columns. Our columns will be “id”, “first_name”, “last_name”, “email”.
CREATE TABLE `people` ( `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `first_name` varchar(60) NOT NULL DEFAULT '', `last_name` varchar(64) NOT NULL DEFAULT '', `email` varchar(100) NOT NULL DEFAULT '', PRIMARY KEY (`ID`), KEY `email` (`email`) ) CHARACTER SET utf8 COLLATE utf8_unicode_ci; |
To help explain what is happening here, let’s write our exactly what we have here.
- It creates a table with four columns
- The ID column is setup with a datatype of bigint. It has a maximum size of 20 digits, is unsigned, never null, and automatically increments its value when new rows are inserted into the table.
- The first_name column has a datatype of varchar with a max length of 60 characters. It is never null and has a default value of empty string.
- The last_name column has a datatype of varchar with a max length of 60 characters. It is never null and has a default value of empty string.
- The email column has a datatype of varchar with a max length of 100 characters. It is never null and has a default value of empty string.
- The primary row identifier is the ID column.
- The email column is keyed, meaning MySQL will create an index on that column, letting us much more quickly look up people records by their email address.
- The table uses a character set of utf8
- The table is collated with utf8_unicode_ci
If you are confused by any of this, that is okay. It will all make more sense when you start making tables yourself.
Here is a sample view of the table we have just created in Sequel Pro:
Now let’s translate this back to our WordPress project. Earlier I mentioned a function called dbDelta(). It is a handy function provided by WordPress that provide an easy way to create and modify existing tables.
To create our table with dbDelta(), we simply pass our query as the first argument in the function:
$created = dbDelta( "CREATE TABLE people ( ID bigint(20) unsigned NOT NULL AUTO_INCREMENT, first_name varchar(60) NOT NULL DEFAULT '', last_name varchar(64) NOT NULL DEFAULT '', email varchar(100) NOT NULL DEFAULT '', PRIMARY KEY (ID), KEY email (email) ) CHARACTER SET utf8 COLLATE utf8_general_ci;" ); |
The return value of the function will be an array of all successfully executed queries. If it fails for any reason, the return value will be NULL.
Most of the time, this table creation process will be run during plugin install. The plugin activation hook is an excellent place to run it. I showed how to do this in a tutorial several years ago.
Here’s a quick example to show how we might include this in an activation hook:
function pw_sample_plugin_create_table() { $created = dbDelta( "CREATE TABLE people ( ID bigint(20) unsigned NOT NULL AUTO_INCREMENT, first_name varchar(60) NOT NULL DEFAULT '', last_name varchar(64) NOT NULL DEFAULT '', email varchar(100) NOT NULL DEFAULT '', PRIMARY KEY (ID), KEY email (email) ) CHARACTER SET utf8 COLLATE utf8_general_ci;" ); } register_activation_hook( __FILE__, 'pw_sample_plugin_create_table' ); |
Note: we are going to create a slightly modified version of this in the next few parts of this series. Before we’re done with this series, we will have a complete database class that includes a create_table() method. This sample function is only to serve as an example.
One of my favorite features of the dbDelta() function is that it also allows you to update a table structure. As plugins grow and bugs are fixed or new features are added, we often have to alter our table structure to add new columns, change datatypes, or make other adjustments.
Typically changing a database table would require you to write an ALTER TABLE statement. dbDelta() does it for us automatically.
Let’s extend our example from above and pretend that six months have gone by and we now want to introduce a new feature that allows the country of residence to be stored along with people’s name and email. For this, we need to add a new country column.
We could do this by writing an alter statement, like this:
ALTER TABLE `people` ADD `country` varchar(64) NOT NULL DEFAULT ''; |
Or we could just modify our original creation statement and run dbDelta() again:
$updated = dbDelta( "CREATE TABLE people ( ID bigint(20) unsigned NOT NULL AUTO_INCREMENT, first_name varchar(60) NOT NULL DEFAULT '', last_name varchar(64) NOT NULL DEFAULT '', email varchar(100) NOT NULL DEFAULT '', country varchar(64) NOT NULL DEFAULT '', PRIMARY KEY (ID), KEY email (email) ) CHARACTER SET utf8 COLLATE utf8_general_ci;" ); |
If we look at the dbDelta() function source, we see that it includes logic for detecting when a table already exists and then sets up the ALTER TABLE statement for us.
// For every field in the table. foreach ($tablefields as $tablefield) { // If the table field exists in the field array ... if (array_key_exists(strtolower($tablefield->Field), $cfields)) { // Get the field type from the query. preg_match("|".$tablefield->Field." ([^ ]*( unsigned)?)|i", $cfields[strtolower($tablefield->Field)], $matches); $fieldtype = $matches[1]; // Is actual field type different from the field type in query? if ($tablefield->Type != $fieldtype) { // Add a query to change the column type $cqueries[] = "ALTER TABLE {$table} CHANGE COLUMN {$tablefield->Field} " . $cfields[strtolower($tablefield->Field)]; $for_update[$table.'.'.$tablefield->Field] = "Changed type of {$table}.{$tablefield->Field} from {$tablefield->Type} to {$fieldtype}"; } // Get the default value from the array // todo: Remove this? //echo "{$cfields[strtolower($tablefield->Field)]} "; if (preg_match("| DEFAULT '(.*?)'|i", $cfields[strtolower($tablefield->Field)], $matches)) { $default_value = $matches[1]; if ($tablefield->Default != $default_value) { // Add a query to change the column's default value $cqueries[] = "ALTER TABLE {$table} ALTER COLUMN {$tablefield->Field} SET DEFAULT '{$default_value}'"; $for_update[$table.'.'.$tablefield->Field] = "Changed default value of {$table}.{$tablefield->Field} from {$tablefield->Default} to {$default_value}"; } } // Remove the field from the array (so it's not added). unset($cfields[strtolower($tablefield->Field)]); } else { // This field exists in the table, but not in the creation queries? } } // For every remaining field specified for the table. foreach ($cfields as $fieldname => $fielddef) { // Push a query line into $cqueries that adds the field to that table. $cqueries[] = "ALTER TABLE {$table} ADD COLUMN $fielddef"; $for_update[$table.'.'.$fieldname] = 'Added column '.$table.'.'.$fieldname; } |
That is freakin’ magnificent!
In the next part of this series we will begin writing our actual class and the methods that our class will contain. That’s when the DB interaction API really begins to take shape.
It may be worth noting some of the nuances in the dbDelta () SQL syntax.
For example, using double space after primary key and making sure the whitespace and column name definitions on secondary indices are EXACTLY to the DB API specifications.
Failure to follow whitespace and other guidelines will either not create indices properly or create duplicate indices, especially during subsequent dbDelta() calls. Duplicate indices can kill performance and, at 64 attempts, have significant performance penalties as maximum index entry warnings start firing in the background.
I wasted many hours tracking this down in my plugins. Attention to detail is critical. Hopefully saves other devs some future angst.
YES! Thank you for that.
Another thing dbDelta() is super picky about is the use of backticks. It does not want column names wrapped with backticks.
I can’t believe I found this tutorials just when I’m in the process of creating custom tables in one of the wordpress sites I’m developing. This is up to date as it gets, any chance you’ll be posting the following posts shortly? Thanks so much for that!
Yes the next part in the series should be coming soon.
I’m just fighting with how picky dbDelta is. Anyway, for the character set and collation, I find the following useful.
$wpdb->get_charset_collate();
https://developer.wordpress.org/reference/classes/wpdb/get_charset_collate/
Definitely a helpful function.
Hello there… I’m very new to a lot of things… PHP, HTML, etc.
But I do know pretty well SQL, Databases, etc.
So I did my tables, even a new database on same server. Complex relation ships between the tables (foreign keys, contrainsts, etc…), so I know all about the names of the columns their data types, the fields and so on.
Where do I put my *.php file (where are the classes and all representing my API I suppose), and how do I call the functions to show the data in the pages I build in WP?
Sorry, until now I have always built heavy interfaces GUI and not used yet thin client.
Sounds like building plugins are new to you. I’d recommend reading through my Plugin Development 101 series. It will help a lot.
My god! So quick and helpful answer!!! Straight to the point…
Thank you really a lot… Soon I’ll get as good as in Delphi I hope 😉
Very kind regards,
Daric
I just downloaded and installed EDD in a test site. I created a test product and then I looked in the database. I see that products are still the builtin custom post type and is stored in the wp_posts table and post meta is still stored in wp_postmeta. I had the idea that you had converted EDD products and orders to use it’s own tables?
EDD products are currently stored in wp_posts and wp_postmeta. We are still in the process of migrating orders and other records to custom tables.
Oh, I thought it was already migrated. 🙂 I wonder how you connect/setup so products so they still can use categories and tags, now that products will not be CPT anymore. I suspect that you still use builtin custom categories and tags?
Products, categories, and tags will all stay where they are in the standard WordPress tables.
Okay I see. But what I am curious to know is, if I should build something similar to orders, lets say “documents”, with custom tables, but I would still like to use built-in categories and tags for “documents”, then what would be the best way to that, when using custom tables? Maybe that’s another tutorial?
Could you share an example of the expected data you will be storing?
Well, it’s actually for invoices. So I would need, invoice_files, invoice_amount, invoice_company, invoice_number, invoice_account, invoice_bank, invoice_payment_type, invoice_due_date, invoice_text, and more. Furthermore I would like to use built-in categories, tags and comments.
Definitely create a custom table for that!
Cool thanks! But do you or anyone have any comments to my question about incorporating built-in categories, tags and comments/diskussions with this type of post type that has own tables?
I’ve never done it, nor seen it done, but it’s definitely possible!
Maybe it could be a part 6 in these tutorial series? 🙂