Managing Custom Tables in WordPress

Once upon a time I tried to build a theme and plugin store into the WordPress dashboard. Kind of like iTunes for WordPress. I called it WP App Store. Long story short, it didn’t work, I started this company (Delicious Brains Inc.), and the rest is history.

Maybe you already knew that. But did you know that the store itself was a custom app built on WordPress? Yup. I used a few common plugins but mostly it was a lot of custom code. The entire ecommerce system was custom.1

I learned a lot. One of the most important things was why it is sometimes necessary to use custom tables in WordPress. In fact, I wrote a blog post about it at the time. It wasn’t long after that I stopped working on WP App Store, but I did write some code for managing custom tables, a very simple object-relational mapping (ORM).

If you’re not a fan of static methods, you’re probably not going to like the following code much. The use of static methods was inspired by a custom CMS that I had worked with in the past.

First, I should warn you that this class requires PHP 5.3+ due to the get_called_class() function. The class provides CRUD methods to operate on a custom table. It is an abstract class, intended to be extended by other classes whose names match the name of the table.

Let’s look at the example from my blog post. In it, I suggested creating a custom table to store order meta data. Something like this…

CREATE TABLE IF NOT EXISTS `wp_wpas_order_meta` (
  `order_id` bigint(20) NOT NULL,
  `publisher_id` bigint(20) NOT NULL,
  `product_title` varchar(255) NOT NULL,
  `product_type` varchar(255) NOT NULL,
  `commission_amount` float NOT NULL,
  `is_test` tinyint(4) NOT NULL,
  `is_refunded` tinyint(4) NOT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

We can extend the model class above with just a few lines of code to operate on this table.

class WPAS_Model_Order_Meta extends WPAS_Model {
    static $primary_key = 'order_id';
}

The name of the class is important. If you look at the WPAS_Model::__table() method, it derives the name of the database table from the name of the class. So if your class name doesn’t match your table name, you will end up with SQL errors.

You’ll also notice that we’re overriding the $primary_key class variable. Our custom table uses order_id as the primary key column whereas the WPAS_Model class defaults to id.

Now let’s take this new class for a spin. First, let’s add some data…

$order_id = 4556;

WPAS_Model_Order_Meta::insert( array(
    'order_id' => $order_id,
    'publisher_id' => 151,
    'product_title' => 'Canvas',
    'product_type' => 'theme',
    'commission_amount' => '10.23',
    'is_test' => '0',
    'is_refunded' => '0',
) );

And now let’s retrieve the data from the database and print it out…

$order_meta = WPAS_Model_Order_Meta::get( $order_id );
print_r( $order_meta );

Output:

stdClass Object
(
    [order_id] => 4556
    [publisher_id] => 151
    [product_title] => Canvas
    [product_type] => theme
    [commission_amount] => 10.23
    [is_test] => 0
    [is_refunded] => 0
)

Now let’s update the product title to “Storefront”…

$data = array( 'product_title' => 'Storefront' );
$where = array( 'order_id' => $order_id );
WPAS_Model_Order_Meta::update( $data, $where );

That’s quite a few lines for just updating a single field, so let’s add some convenience functions for flipping on the is_test and is_refunded flags…

Now we can very concisely set those flags…

WPAS_Model_Order_Meta::set_as_test( $order_id );
WPAS_Model_Order_Meta::set_as_refunded( $order_id );

Finally, let’s remove our data from the database…

WPAS_Model_Order_Meta::delete( $order_id );

To be clear, I’m not suggesting that this eliminates the need for object classes. In our example I would still have a WPAS_Order class with lots of methods for things like calculating tax and handling order completion. These model classes are intended to house the database-related code and keep it out of the object classes.

It’s been 3 years since I wrote this code and I have mixed feelings about it. If I were in need of a simple ORM like this today, I’d probably recode it, getting rid of the static methods, and returning an array of model object instances rather than an array of stdClass objects. I might also use the Factory design pattern. Or maybe I’d say the heck with it and go looking for a simple ORM that’s already out there and well tested. If I needed more than a simple ORM, I’d probably go with Eloquent (used by Laravel).

Do I really need an ORM at all? Sure, it maintains a separation between the database layer and business logic layer, but how much of a help is that. On one hand, it greatly increases the number of files you have to bounce between during development and on the other it reduces the length of the object classes.

There’s pros and cons for sure. It’s probably overkill for most WordPress plugins, but large complex plugins could probably benefit from it. What do you think?

More On Custom Tables



1 Why did I build the store from scratch? Why not use WooCommerce, EDD, etc? Because it required a very custom checkout experience and it would have been very tough (and likely hacky) to customize an existing ecommerce system.

About the Author

Brad Touesnard

As founder of Delicious Brains Inc., Brad wears many hats; from coding and design, to marketing and partnerships. Before starting Delicious Brains, Brad was a busy freelance web developer, specializing in front-end development.

  • Mike Hemberger

    Great stuff Brad. Seems like a basic example of what Pods is doing. I’m just getting into custom tables for a big data/relationship site(web app) that I need to really scale well. Thanks for posting this to help us all wrap our heads around it a bit more.

    • Awesome to hear, thanks Mike.

    • Bernhard

      Pod’s is great 😉 – even more with the upcoming 3.0 release ( expected this year) which will integrate CMB2 ( https://wordpress.org/plugins/cmb2/ ) and add’s ui support for repeating fields – repeating fields where already possible with relation’s, but now you can choose to store it as serialize data. Take a look into the “table storage” option for pods’ – it allows to store CPT Meta Data in Tables …

  • 1. What’s so bad about static methods?

    2. When do you insert the new tables? I would add it into the plugin activation hook and set an option after they are set up so that happens only once.

    3. Did you ever write an update function that creates a record if one does not exist before? A good example of this would be core’s `update_post_meta()` that I always use instead of `add_post_meta()` because it does the same (in most use cases I have). However as far as I know such a function requires three database interactions: a) read, to check if the value exists before b) write if it doesn’t c) update if it does. I was wondering if there is somehow a more straightforward way to do this.

    Thanks for the article!

    • 1. Nothing. In fact, I think they’re a great way to keep functions out of the global scope. In this case though, I think it would be beneficial for the functions to return class instances containing the data rather than a stdClass. Would be much better for catching errors due to changes to the data schema for example.

      2. In this case the custom tables were part of my custom theme, so I just created them manually. But for our plugins, we usually create tables upon plugin activation using the `dbDelta()` function.

      3. Nope, I’ve never created a custom update function like that, but I would assume that it would require 2 calls to the database. Though a good caching system (like the post meta functions have) might skirt the first call.

  • tiptronic

    Very interesting read, thanks!

    Let me first add, that I’m not a PHP guy. A while ago I was looking for a convenient way to manage custom databases from within WP without diving too deep into php. At one point I found Pods (pods.io), which abstracts away a good portion of code to be written in the backend, and simplifies the process of handling all kinds of databases/tables from within the WP-admin. Together with WP’s REST-API, you can really do very nice things easily. It (pods) doesn’t work 100% perfect when using SQLite as mysql-replacement (which is not officially supported anyway), but when using mysql it’s really helpful and reduces the amount of code drastically.

  • Hey Brad,

    At Ninja Forms, we took the Factory Design Pattern route. I think it will serve us well with our custom tables.

    http://kylebjohnson.me/the-factory-design-pattern/

    https://github.com/wpninjas/ninja-forms/blob/3.0/includes/Abstracts/ModelFactory.php

  • phamthuan

    Hi Brad,
    Do you have any tutorial or suggestion for using custom tables with multilingual ?

    • You can certainly work in multiple language support when implementing custom tables, but I haven’t seen any examples of that no, sorry.

  • Luke Cavanagh

    It will be cool when WC has CRUD methods built in.