Don’t use WP_List_Table for anything. You’ll thank you later.
I used to spend a great deal of time doing CRUD operations in custom tables and writing different CRUD methods for every single table I had within custom plugins. From those repetitive tasks I saw the need of creating a simple DataStore that would use $wpdb methods to work with my data and not have to re-invent the wheel every time I started a new project. This simple class has saved me countless hours during development.
My goals were simple
- Utilize
$wpdbas much as I could - Create a datastore class that could be inherited throughout all my projects
- Easy to use
I noticed that I needed to have a hardcoded table name and primary key in all of my custom solutions so I started there. I tend to use id as the primary key which is set to auto-increment and decided to hardcoded too because it is one less setting I have to configure going forward when creating other datastores off of this one.
<?php
/**
* Class DataStore
*/
class DataStore {
/**
* Table where data is being stored.
*
* @var string $table_name
*/
protected static $table_name;
/**
* Primary key in the database.
*
* @var string $primary_key
*/
protected static $primary_key = 'id';
/**
* Get table name
*
* @return string
*/
public static function get_table_name(): string {
global $wpdb;
return $wpdb->prefix . static::$table_name;
}
/**
* Get primary key.
*
* @return null|string
*/
public static function get_primary_key(): ?string {
return static::$primary_key;
}
}You’ll notice that I’m also providing getter methods for retrieving the table name, which is prefixed before returning, meaning that $table_name will be set to the table name without your WordPress prefix.
Creating A New Item
The create method accepts an array which you have already sanitized and creates an item in the database. From previous experience working with $wpdb->insert() I noticed that tables with a Primary Key set to autoincrement would allow me to return the $wpdb->insert_id containing the id of the item that was just created in the database.
For tables having a Primary Key not set to increment, the $wpdb->insert_id was not available, so I had to rely in $wpdb->rows_affected to make sure my item was inserted into the database correctly.
/**
* Create an item.
*
* @param array $item Item to create.
*
* @return false|int False when it fails.
*/
public static function create( array $item ) {
global $wpdb;
$insert = $wpdb->insert( static::get_table_name(), $item ); // phpcs:ignore
// Tables with a PK SET TO AI will return the last inserted ID.
// Tables with a PK NOT SET TO AI will return the number of affected rows.
if ( false !== $insert ) {
// Item inserted into the DB successfully.
return $wpdb->insert_id > 0 ? $wpdb->insert_id : $wpdb->rows_affected;
}
return $insert; // False at this point. There was an error. Ex: table doesn't exist.
}Retrieving An Item
Retrieving an item is done via the $primary_key.
/**
* Retrieve an item from the database.
*
* @param int|string $id Item ID.
*
* @return null|object
*/
public static function retrieve( $id ) {
global $wpdb;
$table = static::get_table_name();
$primary_key = static::get_primary_key();
$sql = "SELECT * FROM $table WHERE {$primary_key} = %s;";
$stmt = $wpdb->prepare( $sql, $id ); // phpcs:ignore
return $wpdb->get_row( $stmt ); // phpcs:ignore
}Updating An Item
This function performs some extra checks in a similar fashion to the create() method.
/**
* Update an item.
*
* @param int|string $id Primary key.
* @param array $item Item data.
*
* @return false|int False when it fails. Number of rows affected when available.
* True when query ran successfully and rows affected is 0, meaning the data we're trying to update is the same
* AND NO UPDATE was made but the query still ran successfully.
*/
public static function update( $id, array $item ) {
global $wpdb;
$update = $wpdb->update( // phpcs:ignore
static::get_table_name(),
$item,
[
static::get_primary_key() => $id,
]
); // phpcs:ignore
if ( false !== $update ) {
return $wpdb->rows_affected > 0 ? $wpdb->rows_affected : true;
}
return $update; // False at this point.
}Deleting An Item
Deleting an item can only be done via it’s ID.
/**
* Delete
*
* @param string $id Item ID.
*
* @return false|int
*/
public static function delete( $id ) {
global $wpdb;
$deleted = $wpdb->delete( // phpcs:ignore
static::get_table_name(),
[
static::get_primary_key() => $id,
]
);
return false !== $deleted ? $wpdb->rows_affected : $deleted;
}Finalized Class
<?php
/**
* Class DataStore
*/
class DataStore {
/**
* Table where data is being stored.
*
* @var string $table_name
*/
protected static $table_name;
/**
* Primary key in the database.
*
* @var string $primary_key
*/
protected static $primary_key = 'id';
/**
* Get table name
*
* @return string
*/
public static function get_table_name(): string {
global $wpdb;
return $wpdb->prefix . static::$table_name;
}
/**
* Get primary key.
*
* @return null|string
*/
public static function get_primary_key(): ?string {
return static::$primary_key;
}
/**
* Create an item.
*
* @param array $item Item to create.
*
* @return false|int False when it fails.
*/
public static function create( array $item ) {
global $wpdb;
$insert = $wpdb->insert( static::get_table_name(), $item ); // phpcs:ignore
// Tables with a PK SET TO AI will return the last inserted ID.
// Tables with a PK NOT SET TO AI will return the number of affected rows.
if ( false !== $insert ) {
// Item inserted into the DB successfully.
return $wpdb->insert_id > 0 ? $wpdb->insert_id : $wpdb->rows_affected;
}
return $insert; // False at this point. There was an error. Ex: table doesn't exist.
}
/**
* Retrieve an item from the database.
*
* @param int|string $id Item ID.
*
* @return null|object
*/
public static function retrieve( $id ) {
global $wpdb;
$table = static::get_table_name();
$primary_key = static::get_primary_key();
$sql = "SELECT * FROM $table WHERE {$primary_key} = %s;";
$stmt = $wpdb->prepare( $sql, $id ); // phpcs:ignore
return $wpdb->get_row( $stmt ); // phpcs:ignore
}
/**
* Update an item.
*
* @param int|string $id Primary key.
* @param array $item Item data.
*
* @return false|int False when it fails. Number of rows affected when available.
* True when query ran successfully and rows affected is 0, meaning the data we're trying to update is the same
* AND NO UPDATE was made but the query still ran successfully.
*/
public static function update( $id, array $item ) {
global $wpdb;
$update = $wpdb->update( // phpcs:ignore
static::get_table_name(),
$item,
[
static::get_primary_key() => $id,
]
); // phpcs:ignore
if ( false !== $update ) {
return $wpdb->rows_affected > 0 ? $wpdb->rows_affected : true;
}
return $update; // False at this point.
}
/**
* Delete
*
* @param string $id Item ID.
*
* @return false|int
*/
public static function delete( $id ) {
global $wpdb;
$deleted = $wpdb->delete( // phpcs:ignore
static::get_table_name(),
[
static::get_primary_key() => $id,
]
);
return false !== $deleted ? $wpdb->rows_affected : $deleted;
}
}
Working With Our Datastore
Working with the DataStore class is very simple. All you have to do is create a class that extends it and set your $table_name. If your table’s primary key field uses a different name than id then you’ll have to set it too.
Let’s assume we have a table in our database named wp_galleries. This table contains the following fields:
- id (Primary key set to autoincrement)
- name
- description
Our datastore class would look like this
<?php
/**
* Class Galleries
*/
class Galleries extends DataStore {
/**
* Table where data is being stored.
*
* @var string $table_name
*/
protected static $table_name = 'galleries';
}Creating a new item
$data = [
'name' => 'Cuban Beaches',
'description' => 'Postcard Perfect Shores',
];
$gallery_id = Galleries::create( $data );Retrieving an Item
$gallery = Galleries::retrieve( 1 );Updating an Item
$data = [
'description' => 'Postcard-Perfect Beaches',
];
$updated = Galleries::update( 1, $data );Deleting an Item.
$deleted = Galleries::delete( 1 );WordPress introduced a new function in WordPress 5.5 allowing you to retrieve the Website’s current environment. It is very useful and even more simple to to use than you might think.
I admit way before I started using constants in the wp-config.php file I used to check in the options table what was the site_url or home_url set to before running a particular piece of code.
If you are familiar with WPEngine you know they offer you multiple environments for you to work with for every site. The power and simplicity of this function is a god send because we can choose which code is safe to run and on what environment type. For example, say you run an LMS website and send a daily report to active members on their progress. You’d only want this email to send in production otherwise the members might get 2 or 3 emails with different data and confuse them.
A simple check on the environment type will solve this problem.
if( 'production' === wp_get_environment_type() ) {
// Run/Register cron job.
}This function will return production by default when no constant has been set in your wp-config.php file. Other possible values can be:
- staging
- development
- local
- production (Already mentioned an returned as default)
How To Use It
Adding a constant to your wp-config.php with your value is all it takes to modify the value returned by wp_get_environment_type(). Multiple examples below:
// Local.
define( 'WP_ENVIRONMENT_TYPE', 'local' );
// Production.
define( 'WP_ENVIRONMENT_TYPE', 'production' );
// Staging.
define( 'WP_ENVIRONMENT_TYPE', 'staging' );
// Development.
define( 'WP_ENVIRONMENT_TYPE', 'development' );
Registering custom tables in WordPress is very easy and it is something I do all the time because it saves me a lot of typing and more importantly because 90% of the work I do involves doing CRUD on custom tables.
Things To Consider
The most important thing to consider is that you don’t override WordPress tables and that you communicate with the rest of your team how to access these tables via the $wpdb global variable.
Come up with a naming convention and stick to it. I like the accessor property to match the table’s name without the prefix so if my table’s name is wp_galleries then I register the table as galleries so I can access it like this $wpdb->galleries.
Registering Custom Tables
In our example, we are going to register a couple of tables for a Galleries plugin. These are the actual tables that would be in our database.
- wp_galleries
- wp_gal_images
- wp_gal_likes
- wp_gal_comments
With this in mind, let’s look at how I would handle this table and register them under $wpdb so I can use them later.
add_action( 'init', 'register_tables', 1 );
/**
* Register tables.
*/
function register_tables() {
$tables = [
'galleries',
'gal_images',
'gal_comments',
'gal_likes',
];
global $wpdb;
foreach ( $tables as $table ) {
$wpdb->$table = $wpdb->prefix . $table;
$wpdb->tables[] = $table;
}
}How To User These Custom Tables
Using these tables is very easy, you can now refer to any of these via the global $wpdb variable.
global $wpdb;
// Now you can access these tables via these
$wpdb->galleries;
$wpdb->gal_images;
$wpdb->gal_comments;
$wpdb->gal_likes;SQL Statement
global $wpdb;
$limit = 3;
$sql = "SELECT * FROM $wpdb->galleries LIMIT %d;";
$stmt = $wpdb->prepare( $sql, $limit );
$galleries = $wpdb->get_results( $stmt );That’s it.