Run SQL in WordPress with $wpdb: get_results, insert and prepare

This article is a localized edition of the Japanese original. Read the Japanese original

You can work with the WordPress database through the global variable $wpdb (an object of the wpdb class). For posts and users, functions such as WP_Query, get_posts() and get_user_meta() are the normal tools, but when you read or write a custom table created by a plugin, or need something those functions do not express well, such as aggregation, you run SQL directly with $wpdb.

The methods and return values in this article were checked against the official reference (the wpdb class on developer.wordpress.org) and wp-includes/class-wpdb.php, and the code examples were tested on WordPress 7.1 (as of October 2026).

Before you use $wpdb

Declare it global inside functions

$wpdb is a global variable, so inside a function or method you need global $wpdb;. A theme's functions.php and plugin files run after WordPress has loaded, so you can use it there right away.

<?php
function myplugin_count_published_posts() {
	global $wpdb;

	return (int) $wpdb->get_var(
		$wpdb->prepare(
			"SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type = %s AND post_status = %s",
			'post',
			'publish'
		)
	);
}

Do not hard-code wp_ in table names

The table prefix (the wp_ part) can be changed per site with $table_prefix in wp-config.php. If you write wp_posts literally, the code breaks on sites with a different prefix.

Reading data

For reads, pick one of four methods depending on the shape of the result.

You can pass one of these constants as $output. The default is OBJECT.

• OBJECT: each row as an object (read it as $row->title)
• ARRAY_A: each row as an associative array keyed by column name ($row['title'])
• ARRAY_N: each row as a numerically indexed array
• OBJECT_K: get_results() only. An array keyed by the value of the first column

As an example, we read a custom table {prefix}myplugin_items (columns id, title, price, created_at).

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

// Up to 10 rows priced 1000 or more, newest first (as associative arrays)
$rows = $wpdb->get_results(
	$wpdb->prepare(
		"SELECT id, title, price FROM %i WHERE price >= %d ORDER BY created_at DESC LIMIT %d",
		$table,
		1000,
		10
	),
	ARRAY_A
);

foreach ( $rows as $row ) {
	echo esc_html( $row['title'] ) . ': ' . (int) $row['price'] . " yen\n";
}

// The single row with ID 5 (as an object)
$item = $wpdb->get_row(
	$wpdb->prepare( "SELECT * FROM %i WHERE id = %d", $table, 5 )
);
if ( null === $item ) {
	// No matching row
}

// Just the number of rows
$total = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" );

get_row() and get_var() return null when no row matches. get_results() returns an empty array when there are no rows.

Writing data (insert, update, delete)

There are dedicated methods for adding, updating and deleting a single row. They escape the values for you, so you do not use prepare() with them. In the third and later arguments you give the format of each value: %s (string), %d (integer) or %f (float). The order matches the order of the array you pass.

Adding: insert

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

$result = $wpdb->insert(
	$table,
	array(
		'title'      => 'Sample product',
		'price'      => 1980,
		'created_at' => current_time( 'mysql' ),
	),
	array( '%s', '%d', '%s' )
);

if ( false === $result ) {
	error_log( 'insert failed: ' . $wpdb->last_error );
} else {
	$new_id = $wpdb->insert_id; // ID assigned by AUTO_INCREMENT
}

insert() returns the number of rows added (normally 1), or false on failure. You get the new row's ID from $wpdb->insert_id right after the call.

Updating: update

<?php
$updated = $wpdb->update(
	$table,
	array( 'price' => 2480 ),   // Columns and values to change
	array( 'id' => $new_id ),   // WHERE conditions
	array( '%d' ),              // Formats of the new values
	array( '%d' )               // Formats of the condition values
);

if ( false === $updated ) {
	error_log( 'update failed: ' . $wpdb->last_error );
} elseif ( 0 === $updated ) {
	// Not an error, but no row changed (no row matched, or the values were the same)
}

update() returns the number of rows changed. 0 is not an error, so test for failure with a strict comparison such as false === $updated.

Deleting: delete

<?php
$deleted = $wpdb->delete( $table, array( 'id' => $new_id ), array( '%d' ) );

In update() and delete(), all elements of the condition array are joined with AND and compared with =. When you need conditions such as IN or <, write them with query() and prepare() as shown next.

Running any SQL: query

query() runs any SQL statement. It returns:

• SELECT, INSERT, UPDATE, DELETE and similar: the number of rows affected
• CREATE, ALTER, TRUNCATE, DROP: true
• Error: false

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

// Delete every row created more than 90 days ago
$count = $wpdb->query(
	$wpdb->prepare(
		"DELETE FROM %i WHERE created_at < %s",
		$table,
		gmdate( 'Y-m-d H:i:s', time() - 90 * DAY_IN_SECONDS )
	)
);

If you run a SELECT with query(), the rows go into $wpdb->last_result, but for reading it is clearer to use get_results() and the other methods from the previous section.

Embedding values safely with prepare

When you put user input or request values into SQL, do not concatenate strings; use prepare() placeholders. Concatenation opens the door to SQL injection.

<?php
// Bad: the input is concatenated as is
$rows = $wpdb->get_results( "SELECT * FROM {$table} WHERE title = '" . $_GET['q'] . "'" );

// Good: pass it through a placeholder
$q    = isset( $_GET['q'] ) ? sanitize_text_field( wp_unslash( $_GET['q'] ) ) : '';
$rows = $wpdb->get_results(
	$wpdb->prepare( "SELECT * FROM %i WHERE title = %s", $table, $q )
);

The rules for prepare() are:

• The placeholders are %s (string), %d (integer), %f (float) and %i (an identifier such as a table or column name; WordPress 6.2 and later).
• Do not wrap %s in quotes (write %s, not '%s'). prepare() adds the quotes.
• To write a literal % in the SQL, write %% (for example DATE_FORMAT(created_at, '%%Y-%%m')).
• Pass the values either as separate arguments or as a single array (do not mix the two).
• prepare() does not run the SQL. Pass the string it returns to get_results() or query().

Partial matches with LIKE

Do not write the % for LIKE in the SQL itself; attach it to the value you pass. Run the input through esc_like() first so that any % or _ the user typed does not act as a wildcard.

<?php
$like = '%' . $wpdb->esc_like( $q ) . '%';
$rows = $wpdb->get_results(
	$wpdb->prepare( "SELECT id, title FROM %i WHERE title LIKE %s", $table, $like )
);

Passing an array to an IN clause

To pass several values to IN ( ... ), create one placeholder per value.

<?php
$ids          = array( 3, 8, 15 );
$placeholders = implode( ', ', array_fill( 0, count( $ids ), '%d' ) );

$rows = $wpdb->get_results(
	$wpdb->prepare(
		"SELECT id, title FROM %i WHERE id IN ( {$placeholders} )",
		array_merge( array( $table ), $ids )
	)
);

If $ids is an empty array, you get IN (), which is an SQL syntax error, so skip the query when the array is empty.

Checking for errors

$wpdb does not throw exceptions for SQL errors. WordPress calls mysqli_report( MYSQLI_REPORT_OFF ) when it connects to the database, so even on PHP 8.1 and later you cannot catch the errors with try / catch. Check whether the return value is false, and look at these properties for details.

<?php
$result = $wpdb->query( $sql );
if ( false === $result ) {
	error_log( sprintf( 'SQL error: %s / SQL: %s', $wpdb->last_error, $wpdb->last_query ) );
}

When both WP_DEBUG and WP_DEBUG_DISPLAY are true in wp-config.php, database errors are also shown on screen. On a production site, do not show them; write them to a log with WP_DEBUG_LOG instead.

Creating a custom table and adding indexes

The standard WordPress way to create a custom table is to call dbDelta() when the plugin is activated. If you write the indexes inside the CREATE TABLE statement, they are created at the same time, and when you later add columns or indexes, the same function applies the difference.

<?php
/**
 * Plugin Name: My Plugin Items
 */

register_activation_hook( __FILE__, 'myplugin_create_table' );

function myplugin_create_table() {
	global $wpdb;

	$table           = $wpdb->prefix . 'myplugin_items';
	$charset_collate = $wpdb->get_charset_collate();

	$sql = "CREATE TABLE {$table} (
		id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
		title varchar(191) NOT NULL DEFAULT '',
		price int(11) NOT NULL DEFAULT 0,
		created_at datetime NOT NULL,
		PRIMARY KEY  (id),
		KEY price (price),
		KEY created_at (created_at)
	) {$charset_collate};";

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

dbDelta() is strict about how the SQL is written. The official documentation (Creating Tables with Plugins in the Plugin Handbook) requires the following:

• Put each column definition on its own line
• Leave two spaces between PRIMARY KEY and (id)
• Write indexes with KEY, not INDEX, in the form KEY name (column)
• Do not wrap column names in quotes or backticks
• Write column types in lowercase and SQL keywords such as CREATE TABLE in uppercase
• Give a length for every type that accepts one (for example int(11))

How to manage table changes by version is covered in Managing WordPress custom tables with SCHEMA_VERSION.

Things to watch out for

• First check whether a core function can do it: if you change posts, metadata or options directly with $wpdb, the object cache is not updated and hooks such as save_post do not fire. For writes to core tables, use wp_update_post(), update_post_meta() and the like.
• Check the row count before a DELETE or UPDATE: run SELECT COUNT(*) with the same conditions to confirm how many rows are affected before you change them. On production, back up the database first.
• Store and reuse the results of heavy queries: $wpdb does not keep results across requests. Save slow results such as aggregates with set_transient() or wp_cache_set() so the query does not run every time.
• Escape on output: when you print values from the database into HTML, pass them through esc_html() or esc_attr(). prepare() escapes for SQL, not for HTML.

Summary

• Inside functions, write global $wpdb;, and build table names with $wpdb->posts or $wpdb->prefix . 'table_name'
• Read with get_results(), get_row(), get_col() and get_var(); write single rows with insert(), update() and delete(); use query() for everything else
• Pass outside values through prepare() placeholders (%s, %d, %f, %i). Use esc_like() for LIKE, and one placeholder per value for IN
• Errors do not arrive as exceptions. Check for a false return value and $wpdb->last_error
• Create custom tables and their indexes with dbDelta() on activation

Related articles

• Preventing SQL injection in WordPress: using $wpdb->prepare correctly
• Managing WordPress custom tables with SCHEMA_VERSION
• Advanced WP_Query: combined tax_query and meta_query, sorting, speed and SQL filters

Questions about WordPress? The publisher of this site is based in Japan and replies in Japanese. Contact (Japanese)