Overview

When your plugin stores large amounts of structured data that does not fit posts, meta or options — event logs, votes, analytics — it can create its own table with dbDelta() and query it through the global $wpdb object using prepared statements.

Do you really need a table?

Usually you do not. Posts, post meta, terms, user meta and options cover most needs and come with caching, revisions, export and REST support for free. Reach for a custom table when you have many rows of simple, uniform data that you need to query efficiently — like one row per "favourite" for thousands of users.

Creating the table

<?php
define( 'GDC_BOOKS_DB_VERSION', '1.1' );

function gdc_books_install_table() {
    global $wpdb;

    $table   = $wpdb->prefix . 'gdc_books_favourites';
    $charset = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE {$table} (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        user_id bigint(20) unsigned NOT NULL,
        book_id bigint(20) unsigned NOT NULL,
        created_at datetime NOT NULL,
        PRIMARY KEY  (id),
        UNIQUE KEY user_book (user_id,book_id),
        KEY book_id (book_id)
    ) {$charset};";

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

    update_option( 'gdc_books_db_version', GDC_BOOKS_DB_VERSION );
}
register_activation_hook( __FILE__, 'gdc_books_install_table' );

dbDelta() compares the SQL with the existing table and creates it or alters it to match. It is famously strict about formatting:

• One column per line.
• Two spaces between PRIMARY KEY and the opening bracket.
• Use KEY, not INDEX, and give every key a name.
• Always use $wpdb->prefix — sites do not all use wp_, and multisite gives each site its own prefix.

Upgrading the schema

Activation hooks do not run when a plugin is updated, only when it is activated. Compare a stored version on every load instead:

<?php
function gdc_books_maybe_upgrade() {
    if ( get_option( 'gdc_books_db_version' ) !== GDC_BOOKS_DB_VERSION ) {
        gdc_books_install_table(); // dbDelta adds the new columns and keys.
    }
}
add_action( 'plugins_loaded', 'gdc_books_maybe_upgrade' );

Writing

<?php
global $wpdb;

$wpdb->insert(
    $wpdb->prefix . 'gdc_books_favourites',
    array(
        'user_id'    => get_current_user_id(),
        'book_id'    => $book_id,
        'created_at' => current_time( 'mysql', true ),
    ),
    array( '%d', '%d', '%s' ) // Formats: integer, integer, string.
);

$wpdb->delete(
    $wpdb->prefix . 'gdc_books_favourites',
    array( 'user_id' => get_current_user_id(), 'book_id' => $book_id ),
    array( '%d', '%d' )
);

insert(), update() and delete() escape every value for you using the formats you pass.

Reading with prepare()

<?php
global $wpdb;
$table = $wpdb->prefix . 'gdc_books_favourites';

// Most-favourited books.
$rows = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT book_id, COUNT(*) AS total
         FROM {$table}
         WHERE created_at >= %s
         GROUP BY book_id
         ORDER BY total DESC
         LIMIT %d",
        gmdate( 'Y-m-d H:i:s', strtotime( '-30 days' ) ),
        10
    )
);

foreach ( $rows as $row ) {
    echo esc_html( get_the_title( (int) $row->book_id ) . ': ' . (int) $row->total );
}

Watch out: Every value that reaches a query must go through a placeholder — %d, %f or %s. Never put a variable directly into the SQL string, however clean you think it is. Since WordPress 6.2, %i also safely escapes table and column names.

Practice

• Create the favourites table on activation and inspect it in phpMyAdmin or Adminer.
• Add a note column, bump GDC_BOOKS_DB_VERSION, reload, and watch dbDelta() add it.
• Write a "top 10 books" query with prepare() and drop the table in uninstall.php.