Custom tables in the WordPress Database

In the development of projects in WordPress, it is sometimes necessary to create new tables in the WordPress database and interact with the data they contain. This can be interesting in the case of not wanting to interfere with native WordPress tables.

Before generating new tables, we must meditate on the pros and cons that this entails. These should not interfere with the native WordPress tables and those that create the plugins that we install in our project.

The first thing to create our tables is to know which ones exist by default in our WordPress project (Source: codex WordPress):

WordPress provides the wpdb class for access to the database. Methods of this class cannot be called directly. For this we have the object $wpdb, which to be used in our PHP code we must declare it as a global variable, using the command:

global $wpdb

Before making use of the access to the database, it must be taken into account that the tables may have a different prefix than wp_, which we can know with the command:

$wpdb->prefix

And, likewise, the collation of data with the database:

$wpdb->collate

CREATE TABLE

Let’s see how to create a table: for this we define a $sql variable with the SQL command to create a table

$prefix = $wpdb->prefix;
$collate = $wpdb->collate;
$nombre_tabla = $prefix.'mitabla';

$sql = "CREATE TABLE {$nombre_tabla} (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  fecha_creacion datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
  titulo varchar(30),
  descripcion varchar(255),
  estado mediumint(8),
  PRIMARY KEY  (id)
  KEY fecha_id (fecha_creacion),
) 
COLLATE {$collate}";

For the creation and/or modification of the structure of an existing table you can use the dbDelta function

dbDelta($queries,$execute)

It is necessary to require or include the upgrade.php file, which can be done with the command:

require_once(ABSPATH.'wp-admin/includes/upgrade.php')

with which a function to create a table could have the following code, in which we pass it on behalf of the table:

function crear_tabla($mi_tabla) {
  global $wpdb;
  $prefix = $wpdb->prefix;
  $collate = $wpdb->collate;
  $nombre_tabla = $prefix.$mi_tabla;
  $sql = "CREATE TABLE {$nombre_tabla} (
      id bigint(20) NOT NULL AUTO_INCREMENT,
      fecha_creacion datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
      titulo varchar(30),
      descripcion varchar(255),
      estado mediumint(8),
      PRIMARY KEY  (id)
      KEY fecha_id (fecha_creacion),
    ) 
    COLLATE {$collate}";
  require_once(ABSPATH.'wp-admin/includes/upgrade.php');
  dbDelta($sql);
}

To execute this function:

crear_tabla('ejemplo_tabla')

The following must be taken into account in the declaration of the SQL statement:

• Each field in the table must be on a line,
• after PRIMARY KEY there must be 2 blank spaces,
• you have to define at least one key (KEY),
• the KEY key must be followed by a space, then the name of the key and the name of the field in parentheses,
• field names must be in lowercase and sentences in uppercase,
• in the text type fields the size must be indicated.

Let’s move on to interact with the table.

INSERT DATA

Using the $wpdb->insert function we can enter data in our table, in this case a row, passing it the name of the table, the data and optionally the format.

$wpdb->insert($table,$data,$format)

In the following example you can follow how to do it:

global $wpdb;
$prefix=$wpdb->prefix;
$nombre_tabla=$prefix.'ejemplo_tabla';
$fila=array(
  'titulo'=>'Mi título',
  'descripcion'=>'Mi descripción'
 );
 $resultado=$wpdb->insert($nombre_tabla,$fila); 

where $resultado gives false if it could not be inserted and 1 if the row has been inserted.

To access the ID generated by the AUTO_INCREMENT column, you can see it with the function

$wpdb->insert_id

UPDATE DATA

To update a row we can use the $wpdb->update function, in which we indicate the name of the table, the data to be set and the condition that the rows must meet. It returns false if no rows have been modified or the number of rows modified.

$wpdb->update($table,$data,$where,$format,$where_format)

Let’s see an example in which the row with the id=5 is updated:

global $wpdb;
$prefix=$wpdb->prefix;
$nombre_tabla=$prefix.'ejemplo_tabla';
$datos = array(
  'titulo'=>'Mi título',
  'descripcion'=>'Mi descripción'
);
$where=array('id'=>5);
$resultado=$wpdb->update($nombre_tabla,$datos,$where); 

DELETE DATA

To delete rows from a table, we can use the $wpdb->delete function. In which we pass the name of the table and what we want to eliminate.

$wpdb->delete($table,$where,$where_format)

Let’s see an example in which the row with id=5 is deleted:

global $wpdb;
$prefix=$wpdb->prefix;
$nombre_tabla=$prefix.'ejemplo_tabla';
$where=array('id'=>5);
$resultado=$wpdb->delete($nombre_tabla,$where); 

READ DATA

Using the $wpdb->get_results function we can obtain data from the table. In this function we pass an SQL statement and the type of return we want (OBJECT or ARRAY).

$wpdb->get_results($query,$output)

Let’s see an example, in which we return all the rows of the table:

global $wpdb;
$prefix=$wpdb->prefix;
$nombre_tabla=$prefix.'ejemplo_tabla';
$query="SELECT * FROM {$nombre_tabla} WHERE 1=1";
$resultados=$wpdb->get_results($query); 

to see each row of the table we can use the loop

foreach ($resultados as $resultado) {
  echo $resultado->titulo.': '.$resultado->descripcion;
}

EXECUTAR SENTÈNCIES SQL GENERALS

The $wpdb->query function allows you to execute any SQL statement on the WordPress database.

$wpdb->query($query)

where $query is a string of characters.

This function returns an integer value with the number of rows affected in the case of SQL commands that act on rows (SELECT, INSERT, DELETE, UPDATE, etc). In the case of commands that affect the entire table (CREATE, ALTER, TRUNCATE and DROP), it returns true if the operation is successful. If an error occurs, it returns the value false. It is convenient to use the === operator to differentiate the return value 0 (0 rows) from false (error) for statements on rows.

A good practice to execute SQL statements is to use the $wpdb->prepare function, which prepares the SQL statement for a secure execution.

Example function to delete a table:

function eliminar_tabla($tabla) {
  global $wpdb;
  $prefix=$wpdb->prefix;
  $nombre_tabla=$prefix.$tabla;
  $sql="DROP TABLE IF EXISTS {$nombre_tabla}";
  require_once(ABSPATH.'wp-admin/includes/upgrade.php');
  $wpdb->query($sql);
}

FINAL RECOMMENDATION

In the case of creating tables for custom use in a project, it is advisable to provide for their maintenance and possible generation of junk data, so it is advisable to generate cleaning options and consistency of these tables within the project. Moreover, if these tables are temporary or their non-use is decided, it is convenient that they be removed from the database, as is the case of uninstalling or deactivating plugins.