This entry is part 5 of 5 in the Building a Database Abstraction Layer Series
- Custom Database API – Reasons for Custom Tables and an API
- Custom Database API – Structuring Your Data
- Custom Database API – Creating the Tables
- Custom Database API – The Basic API Class
- Custom Database API – A Sample Orders Class
In the previous part of this series we wrote the base class that we can then extend for each table in our database. For this part, we’re going to look at what an orders table might look at and build the database class for interacting with the orders table.
Note: this is meant purely as a sample and could be extended much further depending on your specific needs. It is intended that the class we write in this tutorial be a foundational API and not necessarily a complete one.
Method Summary
Looking back at our base database class, we have the following methods:
- __construct()
- get_columns()
- get_column_defaults()
- get()
- get_by()
- get_column()
- get_column_by()
- insert()
- update()
- delete()
- table_exists()
Here’s the complete base class:
Our orders class will extend the base class, meaning all of these methods are accessible in our orders DB class. We will name our new class PW_Orders_DB. Since we are extending the base class, we will only need to write a few new methods. The new methods we will write include:
- __construct() – This will define our table name, primary column ID, and the table version.
- get_columns() – This will retrieve an array of table columns and their data types.
- get_column_defaults() – This will retrieve default values for each column.
- get_orders() – This will return an array of order objects.
- count() – This will count the number of orders.
- create_table() – This will create our database table.
We could also write additional methods, such as get_order(), but those are not necessary for this tutorial. They are trivial to write once the foundational concepts are solid.
Methods
As before, we will go over each method and detail what it does and how it works. Let’s start with the __construct() method.
__construct()
The constructor, which is invoked automatically when the class is instantiated, does nothing more than define the name of our table, the primary column, and the version number.
create_table()
The create_table() method is responsible for creating and updating the database table structure. Refer back to our part on creating database tables if you would like a refresher.
Our table will contain the following columns:
- order_id
- customer_id
- subtotal
- total
- tax
- gateway
- ip
- status
- date
Note: these are just example columns. When building an orders table for production usage, you may have additional tables as well.
The create_table() method looks like this:
To trigger the table creation, we would run something like this:
$db = new PW_Orders_DB; $db->create_table();
Usually this would be done during a plugin install / activation routine. Running create_table() also works when you need to update the structure of the table after its been created, such as when you release a new version of your plugin with new table columns included.
get_columns() and get_column_defaults()
These two methods define the name and datatypes of each column, and the default value of each. While helpful on their own for certain scenarios, they are primarily used in the insert() and update() methods of the base class.
get_orders()
The get_orders() method is the primary class method that will be used the most. It will provide a way to easily retrieve order records from the database and will include options to specify the number of orders to retrieve, the sorting method, and several other parameters.
For example, our method will permit queries like this:
$orders_db = new PW_Orders_DB; $orders = $orders_db->get_orders( array( 'email' => 'susan@sample.com' ) );
That would retrieve all orders made with the email address susan@sample.com.
Another example to retrieve the 10 most valuable orders, in terms of purchase total:
$orders_db = new PW_Orders_DB; $orders = $orders_db->get_orders( array( 'number' => 10, 'orderby' => 'total' ) );
This is where a database abstraction layer becomes immensely helpful. When you have robust methods for retrieving database rows, you avoid having to write extensive SQL queries every time you wish to query data from a table.
The get_orders() method is significantly more complex than our previous methods, but when broken down, it is not difficult to grok.
Our complete method:
At the top of the method, we use the wp_parse_args() function to set up default values. If you have not made it a habit of using wp_parse_args(), I highly recommend it.
After we have set up the default parameters, we set up the where statements for each of the optional parameters, of which there are four:
- order_id – Supports passing in a single order ID or array of order IDs.
- status – Supports a single status or an array of statuses.
- email – Supports a single email address or an array of addresses.
- date – Supports a single date or array of start and/or end dates.
These four where clauses allow us to easily retrieve specific subsets of orders. The first three where clauses are nearly identical and look like this:
The fourth where clause for the date parameter is a bit different. This one permits for orders in specific date ranges to be queried and is much more complex. It allows for two date formats to be passed in:
- A single date string, such as January 1, 2017 or 2016-03-12
- An array with start and/or end dates
We parse the date supplied and then construct the SQL:
Next we set up the orderby option:
Notice that we use the get_columns() method to ensure that the value specified is a valid column name. If it is not, we fall back to ordering by the primary table column.
If ordering by subtotal or total, we have to append +0 to the column name in order to ensure that float values are sorted properly.
Up next is caching. Please do not forget to cache database queries.
To ensure that subsequent queries are cached without interfering with our ability to adjust the query parameters and retrieve non-stale data, we create a unique cache key based on the query arguments supplied to get_orders(). That key is constructed like this:
$cache_key = ( true === $count ) ? md5( 'pw_orders_count' . serialize( $args ) ) : md5( 'pw_orders_' . serialize( $args ) );
And finally, we construct the final query and then cache the results:
Now we have just one method left.
count()
The count() method supports the exact same parameters as the get_orders() method but simply returns an integer instead of an array of orders. It is actually just a wrapper to:
get_orders( $args, true );
The method looks like this:
And we are done! Here’s our final orders database class:
There are numerous ways this class could be expanded to be even more helpful. Here are a few suggestions:
- Support querying orders by the customer_id column
- Add additional columns for billing address details and customer / admin notes
- Include a get_order() method for retrieving a specific order
Pippin, Thanks for the tutorial!
I’m wondering what a basic form would look like that lets you add/edit order information using the insert-method of the PW_DB-class? Do you have a sample or a link to another tutorial that could push me into the right direction?
It would be just like any other form that submits data to the server and is then parsed by PHP. Here are a few tutorials / links to help:
http://www.html-form-guide.com/php-form/php-form-processing.html
http://code.tutsplus.com/tutorials/using-wordpress-for-web-application-development-features-saving-data–wp-34268
https://wordpress.org/support/topic/how-to-save-form-data-to-database-after-submit?replies=7
Hi Pippin,
This is a very helpful summary that’s given me many ideas. Thank you.
In part one, you mentioned that caching is something you cannot rely on when using custom tables. Do you think you could give some insight on how we could write that ourselves?
Take a look at the WP object caching API: https://codex.wordpress.org/Class_Reference/WP_Object_Cache
Hi Pippin,
This is a great tutorial, but I’m confused on part of it. Why is it that the COUNT query doesn’t get sanitized with `$wpdb->prepare`? Does that automatically happen in `get_var` or something?
Thanks!
Good catch! That’s actually an oversight on my part. It should have a prepare statement.
Hi Pippin, Thank you very much for such a great series of tutorial, I am creating a plugin for stripe subscriptions, after following your tutorials I am now redoing users, subscriptions, plans and order classes.
Thanks
Just to leave thanks here, I needed a custom table for a project I am working on and your tutorials series saved me a lot of time, very appreciated!
Thanks PW! Great information, great presentation.
Excellent tutorial series Pippin! Thanks for taking the time to explain the concept of creating a WordPress database API. I wish I had read this months ago!
I created a complex plugin that uses a couple of different post types but it is using wp_posts and wp_postmeta tables; as you mentioned in this series, it is so inefficient and redundant! I am thinking about rewriting it.
Two things that isn’t clear to me and maybe you can shine a light on it.
1) Security: you mentioned some security issues like SQL injection and cross scripting, does this example is taking care of that?
2) WordPress Integration: how do we properly integrate WordPress queries to work with our custom database API? What I mean is, say you access example.com/order/1234, how do we steer WordPress to use our custom database API instead of the default get_post( array( ‘id’ => 1234, ‘post_type’ => ‘order’ ) ). You know what I mean?
Thanks!
1. This sample does do some basic sanitization and data validation for you but there’s still more that can be done. I’d recommend reading this page: https://codex.wordpress.org/Data_Validation
2. The first thing you need to do is extract the order ID from the URL. There are a number of ways to do this and it really depends on how your plugin is setup. For example, if you use pretty URLs with rewrite rules, you can likely use get_query_var() to retrieve the order ID, otherwise you can retrieve the ID from the $_GET super global. Once you have the order ID, it’s simply a matter of passing that ID to your API function for retrieving the order. In my sample class is a method called get( $row_id ). The $row_id parameter would refer to the order ID in this case.
Thanks for the reply!
Regarding your reply, I know what you are saying, I understand that. I guess what I don’t understand clearly is what filter is appropriate to use, and if I should modify the global $post or not with my own custom properties, or perhaps I should define a custom global variable for specific post types? You know what I mean?
Hi Pippin, thanks for this valuable tutorial.
Somee question came up:
Now that we have an API, what would be needed to be done to grant access to the API by using a key which could be given to a user/customer?
Could the Data also be made available via REST or Json ?
Could this also be handled by either EDD or Restricted Content PRO ?
Wow! This article series makes a lot of sense to me. I am a beginner in the WordPress space. I have been developing Windows desktop apps for years. Would anyone have a plugin or code to recommend that would do the same thing as this series does (allow me to design my tables) and then serve them by Rest?
Hello, just want to say thanks for a great class – it really helped in my new project with multiple custom tables. Thanks!
This is very useful – but the columns are hardcoded. Would it not make sense to use something like `SHOW COLUMNS FROM $this->table_name` to get a dynamic key and expected value pairs from the table?
That would definitely be a nice improvement!
very good post , thanks…
Thank you for informing me
Thanks for sharing this great info in a well detailed fashion
I will try to implement this by the weekend hence I am fully chanced..
Thanks again
Thank you for sharing this with us
I have recently created a plugin and created a custom table for that and after going through all the tutorials of this series I realized that my implementation is not good enough. I’m thinking about updating that. I really love the well-structured code. And your implementation seems perfect for me. Thank you so much for this awesome series
so great and informative
thank you