In the previous sections of this series, we have looked at reasons you should build a custom database API, we have discussed how to structure your data, and we have looked at how to create the database tables. Now it is time to build the basic API that we will use to actually interact with our database. This will involve writing an API class with all of the necessary methods for retrieving, inserting, updating, and deleting data.

The class that we are building here will be the foundation for our API. It is designed as an abstract class that is then extended for each database table. For example, if your application uses three different tables, you will have four versions of this class; one base class and then one for each table.

Our basic class structure looks like this:

Note that this is a shell of the EDD_DB class that we use in Easy Digital Downloads.

Let’s briefly discuss the purpose of each method.

__construct()

This is the standard class constructor that gets things started. It will be used for defining the name, version, and primary key of the database. For example.

get_columns()

This is used to retrieve an array of the columns the database table contains.

get_column_defaults()

This is used to retrieve the default values of each column.

get()

This is for retrieving a single row from the database identified by the primary key.

get_by()

This is used for retrieving a single row from the database identified by a specified column and value.

get_column()

This is for retrieving the value of a single row’s column identified by the primary key.

get_column_by()

This is for retrieving the value of a single row’s column identified by a specified column and value.

insert()

This is for adding a new row to the table.

update()

This is used to update an existing row in the table.

delete()

This is for deleting a row from the table.

table_exists()

This checks if the database table has been created.

Each of these methods are relatively simple. Let’s walk through what each of these looks like in the base class. Note: we are working only with the base class for this tutorial. In the next part, we’ll look at what a table-specific version of the class looks like.

The first method to look at is __construct(). In our base class, the method is actually intentionally left blank as it serves no purpose in he abstract.

In the table-specific classes, we will use the constructor to setup class properties, but that’s for the next part of this series.

The get_columns() and get_column_defaults()  methods are pretty simple; it just returns an array of our columns and their datatypes. For the base class, however, they return empty arrays:

Next is the get() method. This is the first method where we write an actual query. The purpose of this method is for retrieving a single row from the database identified by the primary column. We use the get_row() method of the WPDB class to retrieve the row.

Up next is get_row_by(). This method is nearly identical to get() except that it allows for a column name to be passed in that will be used to locate the record instead of the primary key.

For example, if you wanted to retrieve a row where the email column was equal to so-and-so@test.com, you would pass in those two values to the method.

The next two methods, get_column() and get_column_by() are nearly identical except that they retrieve a single column’s value instead of the entire row. For these two methods, we use the get_var() method of WPDB:

Now it’s time to write the insert() method that will be used to add data to our table. This method is designed to accept an array of key value pairs that align with the column names in the table. For example, if our table contains columns for email, name, country, the array passed to the insert method would look something like this:

$args = array(
	'name'    => 'Elizabeth Alexander',
	'email'   => 'lis@sample-domain.com',
	'country' => 'UK'
);

Because we are accepting user input that is being stored in the database, we also need to ensure the data is properly validated and sanitized. For this, we do a few things:

• We ensure only array keys that match column names are processed
• We force the data to be inserted with the proper datatypes, according to our column definitions
• We utilize the insert() method of WPDB which handles additional sanitization

Here’s our method:

The $type parameter is used in the do_action() call so that each time insert() is called, we can include a type for the data. For example, if we’re inserting a customer record, we might set the type to “customers”. If we’re creating a payment, we’d use “payment”. Its sole purpose is to allow other developers to identify the kind of data being stored.

Next we have our update() method. This is very similar to the insert() method except that it requires an existing row ID to be supplied to identify the row that should be updated. It also permits a column name to be passed in as the $where parameter if the row needs to be identified by a column other than the primary key (ID).

Up next is the delete() method for removing records from a table. This one is very simply and just executes a DELETE query on the specified row ID:

The table_exists() method is designed to return a boolean value of true if the table exists or false if it does not. This is primarily used for install routines.

And that’s it for our base class.

These are only the basic methods for creating, getting, updating, and deleting data. For this reason, they do not include any caching, filters, or other common components of a database API. These basic methods will be leveraged in our table-specific classes where we’ll create more advanced methods that include those additional components.

All together now, our class looks something like this: