Himanshu SharmaSoftware engineer
Let’s talk

WordPress · Lesson 35 of 45

The database and $wpdb

Understand the WordPress tables and use $wpdb safely with prepare(), get_results(), insert(), update() and delete(), plus custom tables with dbDelta().

16 minData and APIsUpdated 23 October 2026

By the end of this lessonRead and write the WordPress database from PHP without SQL injection, and create a custom table when the built-in ones are not the right fit.

The WordPress tables

WordPress stores everything in a MySQL (or MariaDB) database. Here are the tables you will meet most, each with the prefix chosen at installation (wp_ by default):

bash
wp db tables --all-tables
result
wp_commentmeta
wp_comments
wp_links
wp_options
wp_postmeta
wp_posts
wp_term_relationships
wp_term_taxonomy
wp_termmeta
wp_terms
wp_usermeta
wp_users
Table Holds
wp_posts posts, pages, menu items, attachments and custom post types
wp_postmeta extra key/value data for posts
wp_terms, wp_term_taxonomy, wp_term_relationships categories, tags and other taxonomies, and which posts use them
wp_users, wp_usermeta accounts and their extra data
wp_options site-wide settings
wp_comments, wp_commentmeta comments and their meta

You can look at the data from the command line too, but be careful with writes: always make a backup first (wp db export).

Using $wpdb

WordPress gives you a global object, $wpdb, connected to the database with the credentials in wp-config.php. Use the table names that it provides, because the prefix may differ:

php
<?php
global $wpdb;

echo "prefix: ", $wpdb->prefix, "\n";
echo "posts table: ", $wpdb->posts, "\n";

// One value.
$count = $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_status = 'publish' AND post_type = 'post'" );
echo "published posts: ", $count, "\n";

// One row, as an object.
$row = $wpdb->get_row( "SELECT ID, post_title FROM {$wpdb->posts} WHERE ID = 1" );
echo $row->ID, ': ', $row->post_title, "\n";

// Many rows.
$pages = $wpdb->get_results( "SELECT ID, post_title FROM {$wpdb->posts} WHERE post_type = 'page' ORDER BY ID" );
foreach ( $pages as $page ) {
	echo "page ", $page->ID, ": ", $page->post_title, "\n";
}
result
prefix: wp_
posts table: wp_posts
published posts: 1
1: Hello world!
page 2: Sample Page
page 3: Privacy Policy

The main methods:

Method Returns
get_var( $sql ) a single value
get_row( $sql ) one row, as an object (or array with ARRAY_A)
get_col( $sql ) one column as an array
get_results( $sql ) all rows, as an array of objects
query( $sql ) runs any statement; returns the number of rows affected
insert(), update(), delete(), replace() safe helpers for writing

SQL injection and prepare()

Building SQL by adding user input to a string lets an attacker change what the query means. Never do it. The tool is $wpdb->prepare(), which takes placeholders and the values separately:

php
<?php
global $wpdb;
$title = "Hello world!";

$sql = $wpdb->prepare( "SELECT ID FROM {$wpdb->posts} WHERE post_title = %s AND post_status = %s", $title, 'publish' );
echo $sql, "\n";
echo "found: ", $wpdb->get_var( $sql ), "\n";

// Hostile input stays data:
$evil = "x' OR '1'='1";
echo $wpdb->prepare( "SELECT ID FROM {$wpdb->posts} WHERE post_title = %s", $evil ), "\n";
result
SELECT ID FROM wp_posts WHERE post_title = 'Hello world!' AND post_status = 'publish'
found: 1
SELECT ID FROM wp_posts WHERE post_title = 'x\' OR \'1\'=\'1'

The placeholders are %s (string), %d (integer) and %f (float). Notice that prepare() wrapped the hostile value in quotes and put a backslash before each quote inside it, so the database searches for it as a title instead of running it as part of the query. Do not put quotes around the placeholders yourself. For LIKE queries, use $wpdb->esc_like() on the value first, then pass it to prepare().

php
<?php
global $wpdb;
$term = 'Hello';
$like = '%' . $wpdb->esc_like( $term ) . '%';
$ids  = $wpdb->get_col( $wpdb->prepare( "SELECT ID FROM {$wpdb->posts} WHERE post_title LIKE %s", $like ) );
echo implode( ', ', $ids ), "\n";
result
1

Writing safely

For simple writes use insert(), update() and delete(). They escape the values for you:

php
<?php
global $wpdb;

$wpdb->insert( $wpdb->options, [ 'option_name' => 'demo_row', 'option_value' => 'first', 'autoload' => 'no' ], [ '%s', '%s', '%s' ] );
echo "insert id: ", $wpdb->insert_id > 0 ? 'set' : 'missing', "\n";

$wpdb->update( $wpdb->options, [ 'option_value' => 'second' ], [ 'option_name' => 'demo_row' ] );
echo "value: ", $wpdb->get_var( $wpdb->prepare( "SELECT option_value FROM {$wpdb->options} WHERE option_name = %s", 'demo_row' ) ), "\n";

$deleted = $wpdb->delete( $wpdb->options, [ 'option_name' => 'demo_row' ] );
echo "deleted rows: ", $deleted, "\n";
result
insert id: set
value: second
deleted rows: 1

insert() takes the table, an array of column => value, and optional formats. update() and delete() take a second array of conditions that are joined with AND. They return the number of affected rows, or false on error. Check $wpdb->last_error when something fails. (This example used the options table only to keep it short: for options, use update_option().)

Use the WordPress functions first

Direct SQL skips WordPress's caches and hooks. Before writing a query, check whether WP_Query, get_posts(), get_option(), get_post_meta() or get_terms() do the job. Use $wpdb when you need something they cannot do: complex joins, aggregate reports, or your own tables.

A custom table

Posts, meta, taxonomies and options fit most data. For large structured data with its own columns and indexes, such as bookings or logs, create a table in the plugin's activation hook with dbDelta(), which creates the table or updates it to match the new definition:

php
<?php
/**
 * Plugin Name: Bakery Orders
 */
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

function bakery_orders_install() {
	global $wpdb;
	require_once ABSPATH . 'wp-admin/includes/upgrade.php';

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

	// dbDelta is picky: two spaces after PRIMARY KEY, each column on its own line.
	$sql = "CREATE TABLE $table (
		id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
		customer varchar(100) NOT NULL,
		total decimal(10,2) NOT NULL DEFAULT 0,
		created datetime NOT NULL,
		PRIMARY KEY  (id),
		KEY created (created)
	) $charset;";

	dbDelta( $sql );
	update_option( 'bakery_orders_db_version', '1.0' );
}
register_activation_hook( __FILE__, 'bakery_orders_install' );
bash
wp plugin activate bakery-orders
wp db query "SHOW COLUMNS FROM wp_bakery_orders" --skip-column-names
result
Plugin 'bakery-orders' activated.
Success: Activated 1 of 1 plugins.
id	bigint unsigned	NO	PRI	NULL	auto_increment
customer	varchar(100)	NO		NULL
total	decimal(10,2)	NO		0.00
created	datetime	NO	MUL	NULL

Use the table the same way as any other, with prepared statements:

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

$wpdb->insert( $table, [ 'customer' => 'Ada', 'total' => 12.50, 'created' => '2026-02-01 10:00:00' ], [ '%s', '%f', '%s' ] );
$wpdb->insert( $table, [ 'customer' => 'Sam', 'total' => 7.25, 'created' => '2026-02-02 11:30:00' ], [ '%s', '%f', '%s' ] );

$sum = $wpdb->get_var( $wpdb->prepare( "SELECT SUM(total) FROM $table WHERE created >= %s", '2026-02-01' ) );
echo "total: ", $sum, "\n";
foreach ( $wpdb->get_results( "SELECT customer, total FROM $table ORDER BY id" ) as $order ) {
	echo $order->customer, ' ', $order->total, "\n";
}
result
total: 19.75
Ada 12.50
Sam 7.25

Keep a database version in an option and compare it on plugins_loaded so you can change the table definition in later releases. Remove the table in uninstall.php, not on deactivation.

Common mistakes

  • Concatenating input into SQL. Always prepare().
  • Hard-coding wp_ instead of $wpdb->prefix or $wpdb->posts.
  • Quoting placeholders ('%s'), which doubles the quotes.
  • Querying inside loops: fetch what you need in one query.
  • Writing to core tables directly when a WordPress function exists: you skip caches and hooks.
  • Forgetting global $wpdb; inside a function.

Exercise

Write a query with $wpdb->prepare() that returns the IDs of published pages whose title contains a word given in a variable.

Show one possible answer
php
global $wpdb;
$word = 'Sample';
$like = '%' . $wpdb->esc_like( $word ) . '%';
$ids  = $wpdb->get_col( $wpdb->prepare(
	"SELECT ID FROM {$wpdb->posts} WHERE post_type = %s AND post_status = %s AND post_title LIKE %s",
	'page', 'publish', $like
) );

esc_like() escapes % and _ that the visitor typed; prepare() then escapes the whole value.

Key points

  • WordPress keeps content in tables such as wp_posts, wp_postmeta, wp_options, wp_terms and wp_users; the prefix comes from wp-config.php and is available as $wpdb->prefix.
  • $wpdb has get_var(), get_row(), get_results(), insert(), update(), delete() and query() for talking to MySQL.
  • Never put variables into SQL yourself: use $wpdb->prepare() with %s, %d and %f placeholders, or the insert(), update() and delete() helpers.
  • Prefer the WordPress functions (WP_Query, get_option) when they fit; create a custom table with dbDelta() on plugin activation only for data that does not fit posts, meta or options.

Check yourself

1. What does $wpdb->prepare( 'SELECT * FROM t WHERE id = %d', $id ) do?
2. Which is the right way to get the table name of the posts table?
3. Which method returns a single value, like a count?
4. When is a custom table a reasonable choice?