wpdb::query() │ public │ WP 0.71

Executes any queries to the WordPress database.

This method implies executing all queries except SELECT. For SELECT, there are special methods: $wpdb->get_results, $wpdb->get_row, $wpdb->get_col, $wpdb->get_var.

Keep in mind that, as with all functions of the wpdb class, the parameters passed need to be sanitized from SQL injections, which can be done in two ways:

// method 1
esc_sql( $user_entered_data_string )

// method 2
$wpdb->prepare(  'query' , value_parameter[, value_parameter ... ] )

Read more in the section "Protecting queries from SQL injections"

Method of the class: wpdb{}

Used By: wpdb::get_row(), wpdb::delete(), wpdb::update()

Hooks from the method

query

Returns

Int|true|false.

true — for queries CREATE, ALTER, TRUNCATE, DROP.
Number — of affected rows, for queries: DELETE/UPDATE/SELECT.
false — when the query caused an error.

Usage

global $wpdb;
$wpdb->query( $query );

$query(string) (required): Database query.

Examples

#1 Remove the custom 'gargle' field and its value in post 13

$wpdb->query( "DELETE FROM $wpdb->postmeta WHERE post_id = 13 AND meta_key = 'gargle'" );

#2 Set parent page 7 for page 15

$wpdb->query( "UPDATE $wpdb->posts SET post_parent = 7 WHERE ID = 15 AND post_status = 'static'" );

#3 Delete orphaned custom fields

$wpdb->query( "
	DELETE pm FROM $wpdb->postmeta pm 
	LEFT JOIN wp_posts wp ON wp.ID = pm.post_id 
	WHERE wp.ID IS NULL
" );

#4 Change the key of ACF repeater fields

$wpdb->query( "
	UPDATE $wpdb->postmeta
	SET meta_key = REPLACE(meta_key, 'knowledge-base-type', 'knowledge-base-list')
	WHERE `meta_key` LIKE '%knowledge-base-type%'
" );

#5 Example using prepared statements

It is IMPORTANT to always use $wpdb->prepare() method when build your custom query:

$wpdb->query( $wpdb->prepare( 
	"DELETE FROM $wpdb->postmeta WHERE post_id = %d AND meta_key = %s",
	13, 'gargle'
) );

#6 Note about Return value for INSERT / DELETE / UPDATE / REPLACE requests

Be aware that because of the following check exists inside this function:

preg_match( '/^\s*(insert|delete|update|replace)\s/i', $query )

You may be surprised to not receive the expected $wpdb->rows_affected (and will miss the $wpdb->insert_id too) if you have anything except white-space characters before the UPDATE/INSERT/etc. statement, e.g.:

$rows_affected = $wpdb->query( "
	# this comment breaks the $return_val
	UPDATE wp_postmeta SET meta_value = 'baz'
	WHERE meta_key = 'foo' AND meta_value = 'bar'
" );

echo $rows_affected; // will output 0 (actually $num_rows)

This will not work as expected – it will update your records but won’t return any value.

Changelog

Since 0.71

Introduced.

`wpdb::query() wpdb::query` code ^{WP 6.9.1}

wp-includes/class-wpdb.php

public function query( $query ) {
	if ( ! $this->ready ) {
		$this->check_current_query = true;
		return false;
	}

	/**
	 * Filters the database query.
	 *
	 * Some queries are made before the plugins have been loaded,
	 * and thus cannot be filtered with this method.
	 *
	 * @since 2.1.0
	 *
	 * @param string $query Database query.
	 */
	$query = apply_filters( 'query', $query );

	if ( ! $query ) {
		$this->insert_id = 0;
		return false;
	}

	$this->flush();

	// Log how the function was called.
	$this->func_call = "\$db->query(\"$query\")";

	// If we're writing to the database, make sure the query will write safely.
	if ( $this->check_current_query && ! $this->check_ascii( $query ) ) {
		$stripped_query = $this->strip_invalid_text_from_query( $query );
		/*
		 * strip_invalid_text_from_query() can perform queries, so we need
		 * to flush again, just to make sure everything is clear.
		 */
		$this->flush();
		if ( $stripped_query !== $query ) {
			$this->insert_id  = 0;
			$this->last_query = $query;

			wp_load_translations_early();

			$this->last_error = __( 'WordPress database error: Could not perform query because it contains invalid data.' );

			return false;
		}
	}

	$this->check_current_query = true;

	// Keep track of the last query for debug.
	$this->last_query = $query;

	$this->_do_query( $query );

	// Database server has gone away, try to reconnect.
	$mysql_errno = 0;

	if ( $this->dbh instanceof mysqli ) {
		$mysql_errno = mysqli_errno( $this->dbh );
	} else {
		/*
		 * $dbh is defined, but isn't a real connection.
		 * Something has gone horribly wrong, let's try a reconnect.
		 */
		$mysql_errno = 2006;
	}

	if ( empty( $this->dbh ) || 2006 === $mysql_errno ) {
		if ( $this->check_connection() ) {
			$this->_do_query( $query );
		} else {
			$this->insert_id = 0;
			return false;
		}
	}

	// If there is an error then take note of it.
	if ( $this->dbh instanceof mysqli ) {
		$this->last_error = mysqli_error( $this->dbh );
	} else {
		$this->last_error = __( 'Unable to retrieve the error message from the database server' );
	}

	if ( $this->last_error ) {
		// Clear insert_id on a subsequent failed insert.
		if ( $this->insert_id && preg_match( '/^\s*(insert|replace)\s/i', $query ) ) {
			$this->insert_id = 0;
		}

		$this->print_error();
		return false;
	}

	if ( preg_match( '/^\s*(create|alter|truncate|drop)\s/i', $query ) ) {
		$return_val = $this->result;
	} elseif ( preg_match( '/^\s*(insert|delete|update|replace)\s/i', $query ) ) {
		$this->rows_affected = mysqli_affected_rows( $this->dbh );

		// Take note of the insert_id.
		if ( preg_match( '/^\s*(insert|replace)\s/i', $query ) ) {
			$this->insert_id = mysqli_insert_id( $this->dbh );
		}

		// Return number of rows affected.
		$return_val = $this->rows_affected;
	} else {
		$num_rows = 0;

		if ( $this->result instanceof mysqli_result ) {
			while ( $row = mysqli_fetch_object( $this->result ) ) {
				$this->last_result[ $num_rows ] = $row;
				++$num_rows;
			}
		}

		// Log and return the number of rows selected.
		$this->num_rows = $num_rows;
		$return_val     = $num_rows;
	}

	return $return_val;
}