WordPress Database Table Tools

A package to aid in WordPress theme and plugin development by adding classes that quickly allow you to add and manage custom tables to the WordPress database. This uses native WordPress API functions and makes it easy to setup model based I/O using abstract parent classes.

PHP Version Requirements

Version 1 of this library should work on PHP 7.4 or newer.

How to Install

Composer Install

composer require claytonkreisel/wpdb-tools

or

Manual Install

Download the package and manually place it in your application then simply include the autoload.php file like this at the beginning of your application:

<?php

include "PATH/TO/wpdb-tools/autoload.php";

?>

How to Use

Custom Tables

This library contains a parent abstract class of Table. This class allows you to create a child class that will manage your database columns using an array defined in a child method and provides you with methods to insert, select, update and delete rows within that table.

Creating a Custom Table Class

In order to create a custom table you will create a child class that extends the Table class within this package.
NOTE: If you wish to create a table that also has a relational “metadata” table much like the post and postmeta structure native to WordPress then please refer to the TableMeta class. This class will simply create a single table without a corresponding meta table. That class will create and manage both the main table and the metadata table without the need of defining a Table class.
To create a Table you will put the following code in your functions.php file or another file that is included in your plugin or theme. This assumes you have already installed or included the classes through composer or manually.

<?php

use WPDBToolsCustomTableTable;

class Your_Table_Name extends Table{

  /*
  This version number is stored in the wp_options table and tells
  WordPress when to check for updated structure. Be sure to pass a
  string with proper versioning xx.xx.xx.
  */
  public function version(){
    return "0.0.1";
  }

  /*
  Return a name for the table. This will be the name of the table
  after the WordPress prefix is added. IE "test_name" becomes a table
  with the name "wp_test_name" in the WordPress database.
  */
  public function name(){
    return 'your_table_name';
  }

  /*
  Return an associative array of columns that defines the table structure in
  the database. The formatting for this is extremely important and it will
  require you to have an understanding of how to write typicall mysql table
  creation syntax.

  The key of each array iteration is the name of the column and the value is
  the creation rules.
  */
  public function columns(){
    return [
      'id' => 'bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT',
      'int_column' => 'int(11) UNSIGNED NOT NULL DEFAULT 0',
      'varchar_column' => 'varchar(15) NOT NULL',
      'varchar_column_2' => 'varchar(30) NOT NULL DEFAULT "Default Stuff"',
      'boolean_column' => 'boolean NOT NULL DEFAULT 0',
      'text_column' => 'text NOT NULL',
      'longtext_column' => 'longtext NOT NULL',
      'datetime_column' => 'datetime NOT NULL DEFAULT "2000-01-01 12:00:00"'
    ]
  }

  /*
  This function should return a string that defines which key in your database
  columns array you wish to define as the primary key for your database table.
  This will typically be the first column (IE "id").
  */
  public function primary_key(){
    return "id";
  }

}

?>

After you have written this code you can now create a new instance of this class. Upon construction this class will fire a method that checks to see if the version of the database has changed. If it has then a database altering and cleanup will occur.
NOTE: Columns that are removed from the columns array method will remain in the database until you explicitly remove them using the remove_column method. This is to protect from accidental deletion of data.

<?php

/*
Initiates the new table and performs a check on the database in order to update
structure if needed.
*/
$your_new_table = new Your_Table_Name();

?>

Inserting a Row in a Custom Table

In order to insert a new row into a custom table you simply call the insert method on the object you initiated.

<?php

/*
This method inserts one new row into the database.

@params $data(array)[required] - An associative array using the column names as keys
and values as the column value.
*/
$your_new_table->insert([
  'int_column' => 30,
  'varchar_column' => 'Short Value',
  'boolean_column' => true,
  'text_column' => '',
  'longtext_column' => 'This is a considerable amount of text that will go into the database',
  'datetime_column' => '2021-07-01 10:30:00'
  //Not all columns are required in order to insert data...
]);

?>

Inserting Multiple Rows in a Custom Table

In order to insert multiple rows into a custom table you simply call the insert method on the object you initiated with multiple associative arrays of…