WordPress database: $wpdb, custom queries, and schema

For performing database operations WordPress provides a class wpdb which is present in the file - wp-includes\wp-db.php. This class abstracts the database functions for WordPress and most WordPress functions directly or indirectly use this class. We can create an object of this class to perform database operations but WordPress creates an object of this class during the load of WordPress.

This object is $wpdb and is a global object. So in case we want to perform any database operation we should use this global $wpdb object and call functions on them. By the help of $wpdb object we can perform all types of operations like in CRUD.

How to create a table in WordPress:

global $wpdb;

$table_name = $wpdb->prefix . 'chairs';

$sql = "CREATE TABLE IF NOT EXISTS $table_name (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255),
    min_weight INT,
    max_weight INT,
    min_height INT,
    max_height INT,
    image_url VARCHAR(255),
    badge_url VARCHAR(255),
    link VARCHAR(255),
    PRIMARY KEY (id)
)";

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

dbDelta( $sql );

Rather than executing the SQL query directly, we use the dbDelta() function from wp-admin/includes/upgrade.php (we have to load this file, as it is not loaded by default). It compares the requested schema with the existing one, so the same call both creates the table and upgrades it later.

Rules of dbDelta()

dbDelta() parses the query with regular expressions, so it is picky about formatting:

• You must put each field on its own line in your SQL statement.
• You must have two spaces between the words PRIMARY KEY and the definition of your primary key.
• You must use the keyword KEY rather than its synonym INDEX, and you must include at least one KEY.
• KEY must be followed by a single space, then the key name, then a space, then the field name in parentheses.
• You must not use any apostrophes or backticks around field names.
• Field types must be all lowercase.
• SQL keywords, like CREATE TABLE and UPDATE, must be uppercase.
• You must specify the length of all fields that accept a length parameter — int(11), for example.

Creating a table on plugin activation

The charset and collation should be taken from WordPress rather than hardcoded:

function plugin_install() {

    global $wpdb;

    $charset_collate = $wpdb->get_charset_collate();
    $table_name      = $wpdb->prefix . "table_name";

    $sql = "CREATE TABLE $table_name (
      id mediumint(9) NOT NULL AUTO_INCREMENT,
      time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
      name tinytext NOT NULL,
      text text NOT NULL,
      url varchar(55) DEFAULT '' NOT NULL,
      PRIMARY KEY  (id)
    ) $charset_collate;";

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

    dbDelta( $sql );
}

register_activation_hook( __FILE__, 'plugin_install' );

And the matching cleanup when the plugin is removed:

function plugin_uninstall() {

    global $wpdb;

    $table_name = $wpdb->prefix . "ach_payments_receipts";

    $wpdb->query( "DROP TABLE IF EXISTS $table_name" );
}

How to remove a table:

global $wpdb;

$table_name = $wpdb->prefix . 'chairs';

$sql = "DROP TABLE IF EXISTS $table_name";

$wpdb->query($sql);

How to insert a row:

global $wpdb;

$table_name = $wpdb->prefix . 'your_table_name';

$data = array(
    'column1' => 'value1',
    'column2' => 'value2',
);

$wpdb->insert($table_name, $data);

$wpdb->insert() escapes the values itself. When you write the query by hand, wrap it in $wpdb->prepare() — otherwise the query is open to SQL injection:

$result = $wpdb->query( $wpdb->prepare(
    "INSERT INTO $table_name ( user_id, cost, name, email, created_date ) VALUES ( %s, %s, %s, %s, %s )",
    [ $user_id, $cost, $name, $email, $created_date ]
) );

echo $wpdb->insert_id;

$wpdb->insert_id holds the ID generated by the last insert.

Get records:

global $wpdb;

$table_name = $wpdb->prefix . 'chairs';

return $wpdb->get_results( "SELECT * FROM $table_name" );

Get a single row:

$wpdb->get_row( "SELECT * FROM $wpdb->links WHERE link_id = 10" );