Custom Tables in WordPress

WordPress provides a flexible platform for developing websites. While it comes with a quite extensive set of built-in database tables for handling posts, comments, and users, sometimes we need to create a custom table to store and manage our own data. Whether it’s for storing plugin-specific settings, custom data models, or other content.

In this blog post, we will walk through how to create a custom database table in WordPress and implement CRUD (Create, Read, Update, Delete) operations on it.

The $wpdb object

Database management in WordPress is simplified by the use of the $wpdb object, which is a global instance of the wpdb class. This object allows us to interact with the database in a secure and efficient manner. When we need to manage custom data outside of the standard WordPress tables we can use this to create a custom table.

Creating a Custom Table

In order to create a custom table, we can use the dbDelta() function. This function ensures that the table is created (or updated) safely preserving any changes made to the schema over time.

public function create_table() {
    global $wpdb;
    $table_name = $wpdb->prefix . 'custom_table';
    $charset_collate = $wpdb->get_charset_collate();

    // Raw SQL query to create the table.
    $sql = "CREATE TABLE $table_name (
        id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
        name VARCHAR(255) NOT NULL,
        description TEXT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id)
    ) $charset_collate;";

    // Use dbDelta to create the table.
    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    dbDelta($sql);
}

Create (Insert) Method

The create method inserts a new record into the custom table, it uses the $wpdb->insert method for doing so. It accepts two parameters: $name and $description, which correspond to the columns in the table.

public function create($name, $description) {
    global $wpdb;
    $table_name = $wpdb->prefix . 'custom_table';

    return $wpdb->insert(
        $table_name,
        array(
            'name' => $name,
            'description' => $description,
        ),
        array('%s', '%s') // Data format for tthe values.
    );
}

Read (Get All Records) Method

The get_results method retrieves all records from the custom table. It returns an array of objects, each representing a row in the table.

public function get_by_id($id) {
    global $wpdb;
    $table_name = $wpdb->prefix . 'custom_table'; 

    $sql = $wpdb->prepare("SELECT * FROM $table_name WHERE id = %d", $id);
    return $wpdb->get_row($sql);
}

Update Method

The update method updates an existing record with a new name and description, identified by the record’s id.

public function update($id, $name, $description) {
    global $wpdb;
    $table_name = $wpdb->prefix . 'custom_table'; 

    return $wpdb->update(
        $table_name,
        array(
            'name' => $name,
            'description' => $description,
        ),
        array('id' => $id),
        array('%s', '%s'),
        array('%d')
    );
}

Delete Method

The delete method deletes a record based on its ID. This method helps clean up the custom table when data is no longer needed.

public function delete($id) {
    global $wpdb;
    $table_name = $wpdb->prefix . 'custom_table'; 

    return $wpdb->delete(
        $table_name,
        array('id' => $id),
        array('%d')
    );
}

Conclusion

By leveraging WordPress’s $wpdb object and dbDelta(), we can manage custom data securely and efficiently. This approach can be very helpful when building plugins or themes that need to store custom data outside of the default WordPress tables, providing a robust and scalable solution for managing our data needs.

Lakshyajeet.