In a previous tutorial we learnt about how you can create your own custom tables in WordPress, this went over the basics of how to use the dbDelta() function to create tables in database. This tutorial also explained how you can use the $wpdb object to insert data and get results from your custom tables. In this tutorial we are going to be using the $wpdb object and create a base class you can use to run the CRUD queries on your database tables. CRUD is a term used to represent functions to Create, Read, Update and Delete, in this tutorial we will create methods to perform these tasks, this class will then be used as a base class your database table classes can all extend.
Create A Base Class
First we are going to create our base class, which will be an abstract class so that it can't be instantiated, we are going to pass in the table name into this constructor so we can use this value on each of the CRUD queries.
<?php
/**
* Abstract class which has helper functions to get data from the database
*/
abstract class Base_Custom_Data
{
/**
* The current table name
*
* @var boolean
*/
private $tableName = false;
/**
* Constructor for the database class to inject the table name
*
* @param String $tableName - The current table name
*/
public function __construct($tableName)
{
$this->tableName = $tableName;
}
}
?>
Then you can create a class that extends this base class.
class Table extends Base_Custom_Data
{
public function __construct($tableName)
{
parent::__construct($tableName);
}
}
Insert Query
The insert query takes an array parameter which will be the data that you are going to insert into your custom table. This should consist of a key value pair where the key is the name of your column and the value is the data that will be inserted into the database. We need to get access to the $wpdb object by using the global keyword, will means that we will be able to access the insert method. The insert method takes two parameters the first is the table name and the second is the data being added to the database. When you use this method to add data into your database the $wpdb will have a property of insert_id, if the insert was successful this will be populated with the added ID value.
/**
* Insert data into the current data
*
* @param array $data - Data to enter into the database table
*
* @return InsertQuery Object
*/
public function insert(array $data)
{
global $wpdb;
if(empty($data))
{
return false;
}
$wpdb->insert($this->tableName, $data);
return $wpdb->insert_id;
}
To use this method you first need to create a class that extends the base class and passes in the table name in the constructor.
$table = new Table( 'tablename' );
$table->insert( array('id' => 1, 'name' => 'John') );
The above code will now insert a new row into the database table with an ID of 1 and a name of John. ## Get All Table Data
We need a way of reading the data easily from these tables, there are two ways we can do this, first create a method to get all the data from the table. The second is to get the data using WHERE conditions. In this method we are going to return all the data in the table, and add an order by is specified. The get_all() method is going to take one parameter which will be used to specify the column to order the returned data. We will then use the get_results() method to return all the rows from the table.
/**
* Get all from the selected table
*
* @param String $orderBy - Order by column name
*
* @return Table result
*/
public function get_all( $orderBy = NULL )
{
global $wpdb;
$sql = 'SELECT * FROM `'.$this->tableName.'`';
if(!empty($orderBy))
{
$sql .= ' ORDER BY ' . $orderBy;
}
$all = $wpdb->get_results($sql);
return $all;
}
To use this method you first need to create a class that extends the base class and passes in the table name in the constructor.
$table = new Table( 'tablename' );
$all_rows = $table->get_all();
The above code will return all the rows in the table. ## Get By Query
The other way to get the results from the table will be to use a WHERE clause to specify the results you need from the database table. This will take a condition value parameter which is the key value pair of the column you want to search on and the value you want to search for. The second parameter is the condition on what you will search for, this will allow you to do = queries and IN queries.
/**
* Get a value by a condition
*
* @param Array $conditionValue - A key value pair of the conditions you want to search on
* @param String $condition - A string value for the condition of the query default to equals
*
* @return Table result
*/
public function get_by(array $conditionValue, $condition = '=', $returnSingleRow = FALSE)
{
global $wpdb;
try
{
$sql = 'SELECT * FROM `'.$this->tableName.'` WHERE ';
$conditionCounter = 1;
foreach ($conditionValue as $field => $value)
{
if($conditionCounter > 1)
{
$sql .= ' AND ';
}
switch(strtolower($condition))
{
case 'in':
if(!is_array($value))
{
throw new Exception("Values for IN query must be an array.", 1);
}
$sql .= $wpdb->prepare('`%s` IN (%s)', $field, implode(',', $value));
break;
default:
$sql .= $wpdb->prepare('`'.$field.'` '.$condition.' %s', $value);
break;
}
$conditionCounter++;
}
$result = $wpdb->get_results($sql);
// As this will always return an array of results if you only want to return one record make $returnSingleRow TRUE
if(count($result) == 1 && $returnSingleRow)
{
$result = $result[0];
}
return $result;
}
catch(Exception $ex)
{
return false;
}
}
To use this method you first need to create a class that extends the base class and passes in the table name in the constructor.
$table = new Table( 'tablename' );
$john_record = $table->get_by( array('name' => 'John') );
The above code will return the records where name is equal to John. ## Update Query
Along with the insert query the $wpdb object comes with a update method, you can use this to pass in new data you want to replace specifying a where clause. This update method takes two parameters, the data you want to replace and the condition value used in the where clause. The data array will be a key value pair, the key is the columns you are going to update and the value is the data you want to place in the database. The condition value array will be a key value pair for the where clause. We can then use the update method on the $wpdb object and pass in the table name, the data and the where clause on the update query.
/**
* Update a table record in the database
*
* @param array $data - Array of data to be updated
* @param array $conditionValue - Key value pair for the where clause of the query
*
* @return Updated object
*/
public function update(array $data, array $conditionValue)
{
global $wpdb;
if(empty($data))
{
return false;
}
$updated = $wpdb->update( $this->tableName, $data, $conditionValue);
return $updated;
}
To use this method you first need to create a class that extends the base class and passes in the table name in the constructor.
$table = new Table( 'tablename' );
$updated = $table->update( array('name' => 'Fred'), array('name' => 'John') );
The above code will search for all records where name is equal to John and replace the name with Fred. ## Delete Query
Finally the class needs a delete method, which will allow you to delete rows from the table using a where clause. This delete method will take one parameter which is an array for the where clause, the key will be the column and the value of the array is the data used to search. We can then use the $wpdb delete method and pass through the table name and the where clause array.
/**
* Delete row on the database table
*
* @param array $conditionValue - Key value pair for the where clause of the query
*
* @return Int - Num rows deleted
*/
public function delete(array $conditionValue)
{
global $wpdb;
$deleted = $wpdb->delete( $this->tableName, $conditionValue );
return $deleted;
}
To use this method you first need to create a class that extends the base class and passes in the table name in the constructor.
$table = new Table( 'tablename' );
$deleted = $table->delete( array('name' => 'John') );
The above code will search for all records where name is equal to John and delete these from the table. ## Complete CRUD Class
<?php
/**
* Abstract class which has helper functions to get data from the database
*/
abstract class Base_Custom_Data
{
/**
* The current table name
*
* @var boolean
*/
private $tableName = false;
/**
* Constructor for the database class to inject the table name
*
* @param String $tableName - The current table name
*/
public function __construct($tableName)
{
$this->tableName = $tableName;
}
/**
* Insert data into the current data
*
* @param array $data - Data to enter into the database table
*
* @return InsertQuery Object
*/
public function insert(array $data)
{
global $wpdb;
if(empty($data))
{
return false;
}
$wpdb->insert($this->tableName, $data);
return $wpdb->insert_id;
}
/**
* Get all from the selected table
*
* @param String $orderBy - Order by column name
*
* @return Table result
*/
public function get_all( $orderBy = NULL )
{
global $wpdb;
$sql = 'SELECT * FROM `'.$this->tableName.'`';
if(!empty($orderBy))
{
$sql .= ' ORDER BY ' . $orderBy;
}
$all = $wpdb->get_results($sql);
return $all;
}
/**
* Get a value by a condition
*
* @param Array $conditionValue - A key value pair of the conditions you want to search on
* @param String $condition - A string value for the condition of the query default to equals
*
* @return Table result
*/
public function get_by(array $conditionValue, $condition = '=', $returnSingleRow = FALSE)
{
global $wpdb;
try
{
$sql = 'SELECT * FROM `'.$this->tableName.'` WHERE ';
$conditionCounter = 1;
foreach ($conditionValue as $field => $value)
{
if($conditionCounter > 1)
{
$sql .= ' AND ';
}
switch(strtolower($condition))
{
case 'in':
if(!is_array($value))
{
throw new Exception("Values for IN query must be an array.", 1);
}
$sql .= $wpdb->prepare('`%s` IN (%s)', $field, implode(',', $value));
break;
default:
$sql .= $wpdb->prepare('`'.$field.'` '.$condition.' %s', $value);
break;
}
$conditionCounter++;
}
$result = $wpdb->get_results($sql);
// As this will always return an array of results if you only want to return one record make $returnSingleRow TRUE
if(count($result) == 1 && $returnSingleRow)
{
$result = $result[0];
}
return $result;
}
catch(Exception $ex)
{
return false;
}
}
/**
* Update a table record in the database
*
* @param array $data - Array of data to be updated
* @param array $conditionValue - Key value pair for the where clause of the query
*
* @return Updated object
*/
public function update(array $data, array $conditionValue)
{
global $wpdb;
if(empty($data))
{
return false;
}
$updated = $wpdb->update( $this->tableName, $data, $conditionValue);
return $updated;
}
/**
* Delete row on the database table
*
* @param array $conditionValue - Key value pair for the where clause of the query
*
* @return Int - Num rows deleted
*/
public function delete(array $conditionValue)
{
global $wpdb;
$deleted = $wpdb->delete( $this->tableName, $conditionValue );
return $deleted;
}
}
?>
If you can think of ways to improve this class please contribute to the Gist. Base Data Class Gist