WordPress Advanced Guide: Creating and Managing Custom Database Tables

First, find the secret path to the resources necessary to create custom database tables. With a step-by-step guide, I'm sure you'll learn how to build these tables one step at a time. Finally, we'll look at how to effectively interact with these newly created database tables to ensure that you're able to manage and use this data without any problems. The entire process will be walked through in its entirety, and whether you're a beginner or a developer with some basic knowledge, you'll gain valuable knowledge and skills.

Why create custom database tables?

The default database architecture of WordPress can handle almost all types of content needs. By registering custom post types and utilizing post metadata, we can easily handle most data needs.

However, sometimes we encounter special situations where the standard architecture is not enough.

Take an e-commerce site, for products, a custom post type would be appropriate because the information needed for a product page, such as title, image and detailed description, is pretty much the same as a regular post. If additional information is needed, it can also be added through the post's metadata.

This is not the case with orders, which contain very different information than regular posts, so it would be more appropriate to put the order information in a customized database table.

In short, creating a custom database table of your own is a good option when you need to work with data that doesn't fit in the default database schema.

Where to find information

While the WordPress developer documentation does not contain anything about customizing database tables, there is an older version of the developer documentation called Codex that does. You can find it in Codex'sCreating Tables with Pluginspage to find all the information about customized database tables.

Creating custom database tables

The first thing you'll notice is that this is usually done in a plugin.

Additionally, it is possible and recommended to use functions to create customizations when activating the pluginregister_activation_hook a meter (measuring sth)The

This allows this function to run once, rather than every time the plugin is loaded.

Create Table

To create plugin-activated custom tables, you need to use a few things.

First, create a function to manage table creation:

function wp_learn_create_database_table() {
}
function wp_learn_create_database_table() {
}
function wp_learn_create_database_table() {
}

Then, you need to use the$wpdbsecurity situationWordPress Database Objects, because it contains all the methods needed to interact with the database.

This will allow you to set a new table name using the WordPress database prefix.

function wp_learn_create_database_table() {
    function wp_learn_create_database_table() { global $wpdb.
    $table_name = $wpdb->prefix . 'custom_table';
}
function wp_learn_create_database_table() {
    function wp_learn_create_database_table() { global $wpdb.

    $table_name = $wpdb->prefix . 'custom_table';
}
function wp_learn_create_database_table() {
    function wp_learn_create_database_table() { global $wpdb.

    $table_name = $wpdb->prefix . 'custom_table';
}

It also allows you to access theget_charset_collatemethodologies, the method will return the correct character set and sorting rules for the database.

    $charset_collate = $wpdb->get_charset_collate();
    $charset_collate = $wpdb->get_charset_collate();
    $charset_collate = $wpdb->get_charset_collate();

To create tables, you need to understand SQL in order to execute SQL statements on the database. This is accomplished through thedbDelta function (math.)Done.dbDeltaThis feature is usually used during WordPress updates if the default WordPress tables need to be updated or changed. It checks the current table structure, compares it to the desired table structure, and adds or modifies tables as needed.

In order to usedbDeltaYou need to write SQL statements in a specific way.

You can find it on CodexCreate or Update Forms section of the page for more information on these requirements.

After creating the SQL statement, you need to pass it to the functiondbDelta. This is accomplished by includingwp-admin/includes/upgrade.phpThe file containing the function declaration does this.

function wp_learn_create_database_table() {
    function wp_learn_create_database_table() { global $wpdb.
    $table_name = $wpdb->prefix . 'custom_table';
    $charset_collate = $wpdb->get_charset_collate();
    $sql = "CREATE TABLE $table_name (
        id mediumint(9) NOT NULL AUTO_INCREMENT, time datetime DEFAULT
        time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
        time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL, name tinytext NOT NULL,
        text text NOT NULL, url varchar(55)
        url varchar(55) DEFAULT '' NOT NULL, PRIMARY KEY (id)
        PRIMARY KEY (id)
    ) $charset_collate;" ;
    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta( $sql ) ;
}
function wp_learn_create_database_table() {
    function wp_learn_create_database_table() { global $wpdb.

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

    $charset_collate = $wpdb->get_charset_collate();

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

    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta( $sql ) ;
}
function wp_learn_create_database_table() {
    function wp_learn_create_database_table() { global $wpdb.

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

    $charset_collate = $wpdb->get_charset_collate();

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

    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta( $sql ) ;
}

In this example, a new table namedcustom_tableIt has 5 fields: an It has 5 fields: an ida timea nameatextand a urlIt is. Yes.idAuto-incrementing integer, yestimeDate Time Field, Yesnametinytext field, yestextText field,urlis a varchar field. Thisidis the primary key.

Hooking this function into your plugin activation hook will ensure that the table is created when the plugin is activated.

register_activation_hook( __FILE__, 'wp_learn_create_database_table' ).
register_activation_hook( __FILE__, 'wp_learn_create_database_table' ).
register_activation_hook( __FILE__, 'wp_learn_create_database_table' ).

insert data

You can also use plugin activation hooks to insert data into the table when the plugin is activated.

To do this, you can use the objectinsertmethodologies$wpdb, passes an array of the names and values of the fields to be inserted.

Here is an example.

register_activation_hook( __FILE__, 'wp_learn_insert_record_into_table' );
function wp_learn_insert_record_into_table(){
    function wp_learn_insert_record_into_table(){ global $wpdb.
    $table_name = $wpdb->prefix . 'custom_table';
    $wpdb->insert(
        $table_name,
        array(
            'time' => current_time( 'mysql' ),
            'name' => 'John Doe',
            'text' => 'Hello World!
            'url' => 'https://wordpress.org'
        )
    );
}
register_activation_hook( __FILE__, 'wp_learn_insert_record_into_table' );
function wp_learn_insert_record_into_table(){
    function wp_learn_insert_record_into_table(){ global $wpdb.

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

    $wpdb->insert(
        $table_name,
        array(
            'time' => current_time( 'mysql' ),
            'name' => 'John Doe',
            'text' => 'Hello World!
            'url' => 'https://wordpress.org'
        )
    );
}
register_activation_hook( __FILE__, 'wp_learn_insert_record_into_table' );
function wp_learn_insert_record_into_table(){
    function wp_learn_insert_record_into_table(){ global $wpdb.

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

    $wpdb->insert(
        $table_name,
        array(
            'time' => current_time( 'mysql' ),
            'name' => 'John Doe',
            'text' => 'Hello World!
            'url' => 'https://wordpress.org'
        )
    );
}

Updated data

To update the data in a custom table, use theupdateMethods of this object$wpdb, passing an array of field names and values to be updated, and an array of field names and values to be used to find the records to be updated.

function wp_learn_update_record_in_table() {
    function wp_learn_update_record_in_table() { global $wpdb.
    $table_name = $wpdb->prefix . 'custom_table';
    $wpdb->update(
        $table_name,
        array(
            'time' => current_time( 'mysql' ),
            'name' => 'Jane Doe',
            'text' => 'Hello Planet!
            'url' => 'https://wordpress.org'
        ),
        array( 'id' => 1 )
    );
}
function wp_learn_update_record_in_table() {
    function wp_learn_update_record_in_table() { global $wpdb.

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

    $wpdb->update(
        $table_name,
        array(
            'time' => current_time( 'mysql' ),
            'name' => 'Jane Doe',
            'text' => 'Hello Planet!
            'url' => 'https://wordpress.org'
        ),
        array( 'id' => 1 )
    );
}
function wp_learn_update_record_in_table() {
    function wp_learn_update_record_in_table() { global $wpdb.

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

    $wpdb->update(
        $table_name,
        array(
            'time' => current_time( 'mysql' ),
            'name' => 'Jane Doe',
            'text' => 'Hello Planet!
            'url' => 'https://wordpress.org'
        ),
        array( 'id' => 1 )
    );
}

In this example, the record with id 1 will be updated with the new value.

Select data

Using Objectsget_resultsmethod to select data from a customized table$wpdbTheget_resultsA valid SELECT SQL statement will be accepted.

function wp_learn_select_records_from_table() {
    global $wpdb.
    $table_name = $wpdb->prefix . 'custom_table';
    $results = $wpdb->get_results( "SELECT * FROM $table_name" );
    foreach ( $results as $result ) {
        echo $result->name . ' . $result->text . '<br>';
    }
}
function wp_learn_select_records_from_table() {
    global $wpdb.

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

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

    foreach ( $results as $result ) {
        echo $result->name . ' . $result->text . '<br>';
    }
}
function wp_learn_select_records_from_table() {
    global $wpdb.

    $table_name = $wpdb-&gt;prefix . 'custom_table';

    $results = $wpdb-&gt;get_results( "SELECT * FROM $table_name" );

    foreach ( $results as $result ) {
        echo $result-&gt;name . ' . $result-&gt;text . '<br>';
    }
}

By default, get_results returns an array of objects that you can loop through and access the row fields as properties.

clean

It is also possible to delete your customized forms. To do this, you can use thequeryMethods of this object$wpdbIf you want to delete the table, pass an SQL statement to delete the table.

function wp_learn_delete_table() {
    global $wpdb.
    $table_name = $wpdb->prefix . 'custom_table';
    $wpdb->query( "DROP TABLE IF EXISTS $table_name" );
}
function wp_learn_delete_table() {
    global $wpdb.

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

    $wpdb->query( "DROP TABLE IF EXISTS $table_name" );
}
function wp_learn_delete_table() {
    global $wpdb.

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

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

ought toquerymethod will run any valid SQL query, but it's best to use it only for queries that don't insert or update data, since this function doesn't perform any query cleanup.

Depending on your requirements or the requirements of the plugin user, you can delete tables in two ways.

If the users of your plugin don't need the data in this table after deactivating the plugin, you can add the data in thePlugin Deactivation HookTrigger this action on the

register_deactivation_hook( __FILE__, 'wp_learn_delete_table' ).
register_deactivation_hook( __FILE__, 'wp_learn_delete_table' ).
register_deactivation_hook( __FILE__, 'wp_learn_delete_table' ).

However, if the data in that table is important and your users may want to keep it, even if the plugin is deactivated, you can use the plugin's availableTwo uninstallation methodsOne of them deletes the table.

For example, if you choose to use theregister_uninstall_hook.

register_uninstall_hook( __FILE__, 'wp_learn_delete_table');
register_uninstall_hook( __FILE__, 'wp_learn_delete_table');
register_uninstall_hook( __FILE__, 'wp_learn_delete_table');

Or, you can leave the table as is and let the user decide what to do with it.

It is usually recommended to check with your users when uninstalling the plugin to see if they want to keep the table and then use one of the uninstallation methods.

reach a verdict

This tutorial just took you through a quick overview of the basics of custom tables. In future tutorials, we'll dive deeper into how to apply these techniques in real projects.