WordPress defines a class called $wpdb, which contains a set of functions used to interact with a database. Its main purpose is to provide an interface to the WordPress database, but it can be used to communicate with any other appropriate database. The source code of the class is loosely based on the ezSQL class.

WordPress is created using PHP which allows running the mysql_query() and mysql_fetch_array() functions but it is not recommended to use them directly within WordPress for the following reasons:

• The wpdb class provides security enhancements to protect your queries against SQL injection or attacks.

• In a possible migration or change of database engine for example from MySQL to PostgreSQL they might not work and break everything.

WordPress provides a $wpdb global object variable which is an instance of the wpdb class defined in /wp-includes/wp-db.php.

By default, $wpdb is the instance to talk to the WordPress database. To access your WordPress PHP code with $wpdb, you must either declare $wpdb as a global variable using the word global, or use the superglobal $GLOBALS as follows.

// Declarating $wpdb as global
global $wpdb;
$results = $wpdb->get_results( 'SELECT * FROM wp_options WHERE option_id = 1', OBJECT );

// Utilizando el superglobal $GLOBALS
$results = $GLOBALS['wpdb']->get_results( 'SELECT * FROM wp_options WHERE option_id = 1', OBJECT );

It is important not to forget to add the global line $wpdb before starting to perform our functions.

The $wpdb object is not limited to the default tables created by WordPress; can be used to read data from any table in the database (such as add-in custom tables). For example, to select information from a custom table named wp_form, you can do the following.

$myrows = $wpdb->get_results( "SELECT id, name FROM wp_form" );

With the wpdb class we can interact with the database creating different types of queries, but today I am only going to explain the 4 most basic ones which correspond to CRUD (CREATE, READ, UPDATE AND DELETE).

But before this we need to have a table created within our database, this is widely used sometimes when we install a plugin which needs to create tables independently and in this way it is done. All these tests will be placed in the functions.php file.

/**************** Create a table with the wpdb Class ***************/
function crear_base() {
  
  global $wpdb;
  
    // Con esto creamos el nombre de la tabla y nos aseguramos que se cree con el mismo prefijo que ya tienen las otras tablas creadas (wp_form).
    $table_name = $wpdb->prefix . 'form';
 
    // Declaramos la tabla que se creará de la forma común.
    $sql = "CREATE TABLE $table_name (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `nombre` varchar(255) NOT NULL,
      `email` varchar(255) NOT NULL,
      UNIQUE KEY id (id)
    );";
    // upgrade contiene la función dbDelta la cuál revisará si existe la tabla.
    require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
    // Creamos la tabla
    dbDelta($sql);
}
// Ejecutamos nuestra funcion en WordPress
add_action('wp', 'crear_base');

With this we have added a new table to our entire database with the name wp_form and now we will proceed to perform operations on our table.

Insert

As we observed previously, a new table with three fields (id, name, email) was created, the id will not be touched because it only serves to auto-increment each time more data is added, then we will insert a name and an email for each row created.

The way to insert a new row is done as follows:

<?php $wpdb->insert( $table, $data, $format ); ?>

table: It is of type (string) and corresponds to the name of the table where the row or field will be inserted.

data: It is the information to be added and must go through an (array).

format: This is an optional type (array | string) is an array of formats that will be assigned to each of the values ​​in the data.

In our case it would be as follows:

/****************** Insert *****************/
function insertar_wpdb(){
  
  global $wpdb;
  $wpdb->insert( 'wp_form', 
  
    array( 
      'nombre' => 'Andres Dev', 
      'email' => '[email protected]' 
    )
  );
}
// We execute our function in WordPress
add_action('wp', 'insertar_wpdb');

Notice that I am using the wp action hook which is executed after having updated WordPress, so in order for them to execute each of the functions it is only necessary to update WP.

Update

Now we will update the row created by means of the following structure:

<?php $wpdb->update( $table, $data, $where, $format = null, $where_format = null ); ?>

In our case it would be as follows:

/****************** Update *****************/
function actualizar_wpdb(){
  global $wpdb;
  $wpdb->update( 'wp_form', 
    // Datos que se remplazarán
    array( 
      'nombre' => 'Bogotá',
      'email' => 'www.andres-dev.com'
    ),
    // Cuando el ID del campo es igual al número 1
    array( 'ID' => 1 )
  );
}
// We execute our function in WordPress
add_action('wp', 'actualizar_wpdb');

And in this way we have updated the field.

Read

Now we are going to read the information that is in the wp_form table and that is displayed in our WP:

/****************** Read *****************/
function leer_wpdb(){
  global $wpdb;
  $registros = $wpdb->get_results( "SELECT nombre, email FROM wp_form" );
  echo "Registro #1. Nombre: " . $registros[0]->nombre . ", Email: " . $registros[0]->email . "<br/>";
  }
// We execute our function in WordPress
add_action('wp', 'leer_wpdb');

What we did was show the query from the header of our WP.

Delete

And finally, we are going to delete our record through the following structure provided by the wpdb class, which is very similar to the insert one.

<?php $wpdb->delete( $table, $where, $where_format = null ); ?>

In our case it would be as follows:

/****************** Delete *****************/
function borrar_wpdb(){
  global $wpdb;
  $wpdb->delete('wp_form', array('ID' => 1 ));
}
// We execute our function in WordPress
add_action('wp', 'borrar_wpdb');

Completely removing our row.

Most used functions

The four functions seen above are part of the basic ones used whenever we want to interact with a database of any type, for this reason I will leave you below a list of others that you can also use with the wpdb class.

• get_var (): Returns a variable (a single result, a value, ..)
• get_row (): Returns a row from a table in the database
• get_col (): Returns a column from a table in the database
• get_results (): Returns a list of results (the most common)
• insert (): To perform interactions $ wpdb-> insert ($ table, $ data, $ format)
• replace (): To update tables and replacements
• update (): To update a row
• delete (): To delete a row
• query (): For any query
• prepare (): Used to protect against sql injection attacks.

The above is the correct way to work with the WordPress database, whether we are creating a template, a plugin or anything else that comes to mind, do not forget to comment if you liked this article and share it with the whole community ?

*This is a translation of one of my articles on my blog in Spanish andres-dev.com