Skip to content
Tutorials Center
My learning

Topics

Lesson 36 of 68 ยท Plugins

$wpdb and Custom Tables

Use the global $wpdb safely: prepare() placeholders, get_results, insert, update, delete, dbDelta for custom tables, and how to avoid SQL injection.

Most of the time you do not write SQL in WordPress. You call WP_Query, get_option() or get_post_meta(), and core talks to the database for you. Sometimes that is not enough: a plugin may store thousands of form submissions, event bookings or log lines that do not fit the posts table. For those cases WordPress gives you a database object, the global $wpdb, which runs SQL on the site's MySQL or MariaDB database.

This lesson covers the everyday methods, the all-important prepare() method that protects you from SQL injection, and how to create your own table with dbDelta(). It also explains when a custom table is the wrong choice. It builds on the database schema and activation and uninstall, because tables are usually created when a plugin is activated.

The real $wpdb needs a MySQL or MariaDB server, so all real WordPress code below is read-only. The runnable examples use plain PHP with pdo_sqlite. They copy the shape of prepare() and of the CRUD methods, but they are stand-ins: real prepare() escapes strings for MySQL, not SQLite, so the exact quoting differs.

Meeting $wpdb

$wpdb is an instance of the wpdb class. WordPress creates it very early while loading, and you reach it with global $wpdb; inside a function. It also knows the names of the core tables, so you never hard-code the table prefix:

PHP
<?php
global $wpdb;

echo $wpdb->prefix;      // "wp_" on a default install
echo $wpdb->posts;       // "wp_posts"
echo $wpdb->postmeta;    // "wp_postmeta"
echo $wpdb->options;     // "wp_options"

// Your own table: always build the name from the prefix.
$table = $wpdb->prefix . 'bookings';

The prefix is chosen at install time (see setting up a local site). On a multisite network each site has its own prefix, so a hard-coded wp_bookings would hit the wrong table or none at all.

flowchart LR
    A["Your plugin code"] --> B["$wpdb->prepare()"]
    B --> C["$wpdb query methods"]
    C --> D["mysqli connection"]
    D --> E[("MySQL or MariaDB")]
    E --> C
    C --> A

Your code builds a safe query with prepare(), hands it to a $wpdb method, and receives rows back.

Reading data

Four read methods cover almost every need. Each takes a complete SQL string, which you should build with prepare() whenever a value comes from outside your code:

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

// One value (first column of first row), or null.
$count = $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" );

// One row as an object (the default), or null.
$row = $wpdb->get_row(
    $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", $id )
);

// One column of many rows, as a list.
$names = $wpdb->get_col( "SELECT guest_name FROM {$table} ORDER BY id" );

// Many rows: a list of objects, or arrays with ARRAY_A.
$rows = $wpdb->get_results(
    $wpdb->prepare( "SELECT * FROM {$table} WHERE status = %s", 'confirmed' ),
    ARRAY_A
);

The second argument of get_row() and get_results() is the output type. OBJECT (the default) gives objects, ARRAY_A gives associative arrays and ARRAY_N gives numeric arrays. A failed query returns null, false or an empty list depending on the method, so check $wpdb->last_error while you are debugging. Table names in the example are interpolated because they come from your own code, never from a visitor.

prepare(): the placeholders

$wpdb->prepare() works like sprintf() with two big differences: it escapes and quotes every value for the database, and it only accepts a small set of placeholders:

Placeholder Meaning Example result
%s A string, quoted and escaped 'O\'Brien'
%d An integer 42
%f A float 3.5
%i An identifier such as a table or column name (WordPress 6.2 and later) `wp_bookings`
%% A literal percent sign %

Two rules trip people up:

  • Do not put quotes around %s. WordPress adds them. Writing '%s' produces double quoting and wrong results.
  • Never skip prepare() for user input. Escaping with esc_sql() by hand is possible but easy to get wrong. Placeholders are the documented, safer path.

Placeholders protect values, not SQL structure. For a dynamic ORDER BY, check the input against an allowlist and then use %i or the validated string. For a LIKE search, escape the wildcard characters first. For an IN list, build one placeholder per item:

PHP
<?php
global $wpdb;

// LIKE: escape % and _ in the visitor's text, then add your own wildcards.
$like = '%' . $wpdb->esc_like( $term ) . '%';
$sql  = $wpdb->prepare( "SELECT id FROM {$table} WHERE guest_name LIKE %s", $like );

// IN list: one %d per id.
$ids          = array_map( 'absint', $ids );
$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
$sql          = $wpdb->prepare(
    "SELECT * FROM {$table} WHERE id IN ({$placeholders})",
    ...$ids
);

A prepare() stand-in you can run

The stand-in below finds %s, %d, %f, %i and %% in the query and replaces them in order. It uses PDO's quoting, which is SQLite-style (a single quote is doubled). It also shows the classic attack: the same malicious input run through string concatenation and through the stand-in's prepare().

PHP A prepare() stand-in and an injection demo

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The unsafe query returns every login because the quote in the input closed the string and added an always-true condition. The prepared query treats the whole input as one string, so no row matches.

Writing data

You do not need prepare() for the three write helpers, because they escape values themselves. Pass the table, an array of column values, and optionally an array of formats (%s, %d, %f) in the same order:

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

$ok = $wpdb->insert(
    $table,
    [ 'guest_name' => $name, 'seats' => $seats ],
    [ '%s', '%d' ]
);
// $ok is the number of rows inserted, or false on error.
$new_id = $wpdb->insert_id;

$wpdb->update(
    $table,
    [ 'status' => 'confirmed' ],   // columns to change
    [ 'id' => $new_id ],           // WHERE (combined with AND)
    [ '%s' ],                      // format for the data
    [ '%d' ]                       // format for the WHERE
);

$wpdb->delete( $table, [ 'id' => $new_id ], [ '%d' ] );

update() and delete() return the number of affected rows (which can be 0) or false on failure, so compare with false using ===. Their WHERE arrays only support simple equality joined by AND. For anything else, write the SQL and use prepare(), then run it with $wpdb->query().

A CRUD wrapper you can run

This stand-in mirrors the method names and return values: insert() returns the rows added and sets insert_id, update() and delete() return affected rows, and get_results(), get_row() and get_var() read.

PHP A mini $wpdb over SQLite

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

Notice that updating a row that does not exist returns 0, not an error. In real WordPress that is the same: 0 means "ran fine, changed nothing", while false means "the query failed".

Creating a table with dbDelta()

To create or change a table, WordPress offers dbDelta(). You give it the CREATE TABLE statement you want, and it compares that with the existing table, then creates the table or adds missing columns and indexes. You normally call it from your activation routine, and again when your plugin's stored database version is older than the code's version.

PHP
<?php
function acme_bookings_install(): void {
    global $wpdb;

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

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

    $sql = "CREATE TABLE {$table} (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  guest_name varchar(190) NOT NULL,
  seats smallint(5) unsigned NOT NULL DEFAULT 1,
  status varchar(20) NOT NULL DEFAULT 'pending',
  created_at datetime NOT NULL,
  PRIMARY KEY  (id),
  KEY status (status)
) {$charset};";

    dbDelta( $sql );

    update_option( 'acme_bookings_db_version', '1.0' );
}
register_activation_hook( __FILE__, 'acme_bookings_install' );

dbDelta() parses your SQL text with regular expressions, so it is picky about formatting:

  • Put each column on its own line.
  • Write two spaces between PRIMARY KEY and the opening parenthesis.
  • Use KEY (not INDEX) for secondary indexes, and give every index a name.
  • Do not use backticks around names, and end the statement with ) plus the charset.
  • Use $wpdb->get_charset_collate() so the table matches the site's character set.

dbDelta() never drops columns or tables. If you remove a column from your code, the old one stays unless you run your own ALTER TABLE.

flowchart TD
    A["Plugin activated or updated"] --> B{"Stored db version<br/>older than code version?"}
    B -- "no" --> C["Do nothing"]
    B -- "yes" --> D["dbDelta(CREATE TABLE ...)"]
    D --> E["Table created or columns added"]
    E --> F["update_option with new db version"]

A version number stored in an option decides whether dbDelta() has to run again.

When not to use a custom table

A custom table is more work than it looks. You must write the schema, migrations, cleanup on uninstall, export and privacy support, and your own caching. Reach for one only when the data really does not fit WordPress's own storage. Ask these questions first:

Custom tables make sense for large, flat, frequently queried data: logs, analytics events, or thousands of rows per site that you need to filter by several columns. Plain posts and meta become slow there because meta is stored one row per value (see the schema lesson). Remember too that direct queries skip the object cache. Wrap expensive reads with wp_cache_get() and wp_cache_set() (see caching and WP-Cron).

Common pitfalls

  • Concatenating input into SQL. Always use prepare() or the insert, update and delete helpers.
  • Quoting placeholders. Write %s, not '%s'.
  • Hard-coding wp_. Use $wpdb->prefix or the table properties.
  • Treating 0 as failure. Compare false with ===.
  • SELECT * on big tables. Select only the columns you need and add LIMIT.
  • Running dbDelta() on every request. Compare a stored version first.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which statement about $wpdb->prepare() is correct?

    prepare() quotes string values itself. Adding your own quotes around %s leads to double quoting and broken queries.
  2. 2. What does $wpdb->update() return when the WHERE row does not exist?

    update() returns the number of affected rows. No match means 0, and false is reserved for a query that failed.
  3. 3. Why should you build a custom table name with $wpdb->prefix?

    The prefix is configurable and differs per site on a network, so a hard-coded wp_ name can point at the wrong table.
  4. 4. Which formatting rule matters for dbDelta()?

    dbDelta() parses the SQL text with regular expressions. It wants one column per line, KEY for indexes and two spaces after PRIMARY KEY.
  5. 5. When is a custom table a good choice?

    Settings belong in options and content in post types. Custom tables fit big row-based data that would be slow as posts and meta.

Summary

  • $wpdb is the global database object; build table names from $wpdb->prefix or its table properties.
  • get_var(), get_row(), get_col() and get_results() read; insert(), update() and delete() write and escape for you.
  • prepare() supports %s, %d, %f, %i and %%; never quote %s yourself and never concatenate input into SQL.
  • Use an allowlist for dynamic ORDER BY, esc_like() for LIKE, and one placeholder per item for IN.
  • Create and update tables with dbDelta(), usually on activation, guarded by a stored version number.
  • Prefer posts, taxonomies, meta and options; use a custom table only for large, flat data.
  • The runnable code is a plain-PHP stand-in over SQLite, not real WordPress.

Next: Custom Post Types