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 $wpdb as 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 );