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 );