wpdb{} │ WP 0.71 │ AllowDynamicProperties

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.

// method 1
esc_sql( $user_entered_data_string )

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

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

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

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

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

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

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

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

$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)

Gets the value of a single cell from the query result. By default, the first cell is taken — this is the first column and the first row.

If the query result contains more than one column and/or more than one row, the value of the specified cell will be returned (specified in the 2nd and 3rd parameters). Thus, to get the value of the second cell from the second row of the query result, you need to specify the second and third parameters: $column_offset = 1, $row_offset = 1.

Returns

String|null. The value of the database table cell as a string.

• cell value — if the query retrieves the value of a single cell (column in a row).
• value of the first cell of the first column — if the query retrieves one row and multiple columns.
• value of the first cell of the first row and column — if the query retrieves multiple rows and columns.
• NULL — when there is no result.

Usage

global $wpdb;
$wpdb->get_var( $query, $x, $y );

$query(string/null)

The query to execute. You can set this parameter to null, then the function will return the result of the last query that was executed by the class (stored in the variable).

$x(number)

The desired column (column offset). By default 0 - first column.

$y(number)

The desired row (row offset). By default 0 - first row.

Examples

$user_count = $wpdb->get_var( "SELECT COUNT(*) FROM $wpdb->users;" );
echo '<p>The number of users is: ' . $user_count . '</p>';

# Total Number of authors
function get_totalauthors() {
	global $wpdb;

	$totalauthors = intval( $wpdb->get_var(
		"
		SELECT COUNT(ID) FROM $wpdb->users
		LEFT JOIN $wpdb->usermeta ON $wpdb->usermeta.user_id = $wpdb->users.ID
		WHERE $wpdb->users.user_activation_key = '' AND $wpdb->usermeta.meta_key = '{$wpdb->prefix}user_level' AND (meta_value+0.00) > 1
		"
	) );

	return $totalauthors;
}

# Total Number of posts
function get_totalposts(){
	global $wpdb;

	$totalposts = intval( $wpdb->get_var(
		"SELECT COUNT(ID) FROM $wpdb->posts WHERE post_type = 'post' AND post_status = 'publish'"
	) );

	return $totalposts;
}

# Total Number of Pages
function get_totalpages() {
	global $wpdb;

	$totalpages = intval( $wpdb->get_var(
		"SELECT COUNT(ID) FROM $wpdb->posts WHERE post_type = 'page' AND post_status = 'publish'"
	) );

	return $totalpages;
}

# Total Number of Comments
function get_totalcomments() {
	global $wpdb;

	$totalcomments = intval( $wpdb->get_var(
		"SELECT COUNT(comment_ID) FROM $wpdb->comments WHERE comment_approved = '1'"
	) );

	return $totalcomments;
}

# Total Number of Commentators
function get_totalcommentposters() {
	global $wpdb;

	$totalcommentposters = intval($wpdb->get_var(
		"SELECT COUNT(DISTINCT comment_author) FROM $wpdb->comments WHERE comment_approved = '1' AND comment_type = ''"
	) );

	return $totalcommentposters;
}

# Total Number of links
function get_totallinks() {
	global $wpdb;

	$totallinks = intval( $wpdb->get_var("SELECT COUNT(link_id) FROM $wpdb->links") );

	return $totallinks;
}

// define the custom key to be counted
$meta_key = 'miles';
$allmiles = $wpdb->get_var( $wpdb->prepare(
	"SELECT sum(meta_value) FROM $wpdb->postmeta WHERE meta_key = %s", $meta_key
) );
echo '<p>Total number of custom fields: '. $allmiles . '</p>';

Gets the first row from the result of an SQL query. Returns the row as an object.

Use the $row_offset parameter to get the second, third, ..., n-th row from the query.

Returns

Array|Object|null|null.

• object - when $output_type = OBJECT (default).
• array - when $output_type = ARRAY_A or ARRAY_N.
• null - when data could not be retrieved (requested data is not in the database).

Usage

$wpdb->get_row( $query, $output_type, $row_offset );

$query(string)

The query to be executed.

$output_type(constant)

One of three constants. Can be:

• OBJECT - the result will be returned as an object (default).
• ARRAY_A - the result will be returned as an associative array.
• ARRAY_N - the result will be returned as a numerically indexed array.
  Default is OBJECT

$row_offset(number)

The number of the row returned from the query result.
Default is 0 (first row)

Examples

$mylink = $wpdb->get_row( "SELECT * FROM $wpdb->links WHERE link_id = 10" );

// Now, the properties (variables) of $mylink are names
// columns from the table $wpdb->links with the values of the table fields:
echo $mylink->link_id; // display "10"

$mylink = $wpdb->get_row( "SELECT * FROM $wpdb->links WHERE link_id = 10", ARRAY_A );

// the result will be an associative array
echo $mylink['link_id']; // display "10"

$mylink = $wpdb->get_row( "SELECT * FROM $wpdb->links WHERE link_id = 10", ARRAY_N );

// the result will be a numbered array
echo $mylink[1]; // display "10"

Makes a request and retrieves data from one column of the database table as an array.

If the query returns more than one column, the function will return only the data from the first column. You can specify an offset in the $column_offset parameter to get data from a column other than the first, for example, the second: $column_offset = 1.

If you specify $query = null, the function will return the specified column from the previous query. Using this property, you can retrieve data from other columns from an already made query.

Returns

Array. Indexed array with the query data. An empty array when data could not be retrieved.

Usage

global $wpdb;
$wpdb->get_col( $query, $x );

$query(string/null)

The query to be executed. You can set this parameter to null, then the function will return the result of the last query.
Default: NULL (previous query)

$x(number)

The index of the column whose data you want to return.
Default: 0 (first column)

Examples

$wpdb->query( "SELECT * FROM $wpdb->posts WHERE post_type = 'revision'" );

$ids = wp_list_pluck( $wpdb->last_result, 'ID' );

// let's specify one field in the query
$revision_ids = $wpdb->get_col( "SELECT ID FROM $wpdb->posts WHERE post_type = 'revision'" );

// specify all fields in the query
$revision_ids = $wpdb->get_col( "SELECT * FROM $wpdb->posts WHERE post_type = 'revision'" );

/*
In both cases the same array will be returned.
When all fields are specified, get_col gets only the first one, which is the ID

Array(
	[0] => 106
	[1] => 101
	[2] => 102
	[3] => 103
	...
)
*/

// first let's query
$wpdb->query( "SELECT * FROM $wpdb->posts WHERE post_type = 'revision' LIMIT 10" );

// now let's get the data of this query
$revision_ids = $wpdb->get_col( null );
$revision_names = $wpdb->get_col( null, 5 ); // 5 - post_title

/*
Array
(
	[0] => 9949
	[1] => 9957
	[2] => 10125
	[3] => 10154
	[4] => 10221
	[5] => 10235
	[6] => 10319
	[7] => 10496
	[8] => 10532
	[9] => 10568
)
Array
(
	[0] => wp_tempnam
	[1] => Page templates for post types in WP 4.7
	[2] => Privacy Policy
	[3] => walker_nav_menu_start_el
	[4] => Pagination output
	[5] => enter_title_here
	[6] => Smart Custom Fields - a simple meta-fields plugin
	[7] => register_post_type
	[8] => About site
	[9] => REST API in WordPress
)
*/

<?php
$meta_key1 = 'model';
$meta_key2 = 'year';
$meta_key3 = 'manufacturer';
$meta_key3_value = 'Ford';

$postids = $wpdb->get_col( $wpdb->prepare("
SELECT      key3.post_id
FROM        $wpdb->postmeta key3
INNER JOIN  $wpdb->postmeta key1
			on key1.post_id = key3.post_id
			and key1.meta_key = %s
INNER JOIN  $wpdb->postmeta key2
			on key2.post_id = key3.post_id
			and key2.meta_key = %s
WHERE       key3.meta_key = %s
			and key3.meta_value = %s
ORDER BY    key1.meta_value, key2.meta_value",$meta_key1, $meta_key2, $meta_key3, $meta_key3_value) );

if( $postids ){

	echo 'List of ' . $meta_key3_value . '(s), sorted by ' . $meta_key1 . ', ' . $meta_key2;

	foreach( $postids as $id ){
		$post = get_post( $id );
		setup_postdata( $post );
		?>
		<p>
			<a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title_attribute(); ?>"><?php the_title(); ?></a>
		</p>
		<?php
	}
}

<?php
$meta_key1 = 'Color';
$meta_key2 = 'Display_Order';

$postids = $wpdb->get_col($wpdb->prepare("
SELECT      key1.post_id
FROM        $wpdb->postmeta key1
INNER JOIN  $wpdb->postmeta key2
			on key2.post_id = key1.post_id
			and key2.meta_key = %s
WHERE       key1.meta_key = %s
ORDER BY    key2.meta_value+(0) ASC",
		 $meta_key2,$meta_key1));

if( $postids ){
	echo 'List of '. $meta_key1  . ' posts, sorted by ' . $meta_key2 ;

	foreach ($postids as $id) {
		$post = get_post( $id );
		setup_postdata( $post );
		?>
		<p>
			<a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title_attribute(); ?>"><?php the_title(); ?></a>
		</p>
		<?php
	}
}

Gets all data of the specified query (all rows and columns). The result is returned as an array. Each element of the array is an object with data of a separate row of the table.

Returns

Array|Object|null. The result of the database query. Will return:

• Array of objects — when $output = OBJECT or OBJECT_K.
• Array of arrays — when $output = ARRAY_A or ARRAY_N.
• array() — when no rows are found for the query or there is a query error.
• NULL — when the query is an empty string or an incorrect output type ($output_type is passed).

Usage

global $wpdb;
$wpdb->get_results( $query, $output );

$query(string)

The query to be executed.

This parameter can be set to null, then the function will return the result of the last query that was executed.

Default: null

$output(constant/string)

A flag indicating in what form the data should be returned. There are 4 options:

• OBJECT — will return an array of objects with numeric keys - the elements of the array will be objects of the table rows — [ 0 => object ].
• OBJECT_K — similar to the previous one, only the indices of the main array will be the values of the first column of the query result — [ 'field' => object ].
  Note that if the same values enter the index, the data will be overwritten.
• ARRAY_N — will return a numeric array, each element of which will also be a numeric array — [ 0 => [...] ].
• ARRAY_A — will return a numeric array, each element of which will be an associative array, where the key will be the column name — [ 'field' => [...] ].

Default: OBJECT

Examples

<?php
$fivesdrafts = $wpdb->get_results( 
	"SELECT * FROM $wpdb->posts WHERE post_status = 'draft' AND post_author = 5"
);

if( $fivesdrafts ) :
	foreach( $fivesdrafts as $post ){
		setup_postdata($post);
		?>
			<h2><a href="<?php the_permalink(); ?>" rel="bookmark"
				title="Permanent Link to <?php the_title(); ?>"><?php the_title(); ?></a></h2>
		<?php
	}
	else :
	?>
	<h2> Not found</h2>
	<?php 
endif;
?>

$fivesdrafts = $wpdb->get_results( 
	"SELECT ID, post_title FROM $wpdb->posts WHERE post_status = 'draft' AND post_author = 5"
);

foreach ( $fivesdrafts as $fivesdraft ) {
	echo $fivesdraft->post_title;
}

/**
* Returns the results of the rating (reviews in WooCommerce) grouped by ratings
*
* @param int $post_id post ID
*
* @return array of objects, where each object is a lumped data on one of the grades
*/
function get_cnt_rating_reviews_one_product( $post_id ){
	global $wpdb;

	return $wpdb->get_results( $wpdb->prepare(
		"
		SELECT COUNT(meta.meta_id) as num, meta.meta_value
		FROM $wpdb->comments as comments
		INNER JOIN $wpdb->commentmeta as meta ON comments.comment_ID = meta.comment_id
		WHERE comments.comment_post_ID = %d AND meta_key = 'rating'
		GROUP BY meta.meta_value;
		",
		$post_id
	) );

}

// usage
get_cnt_rating_reviews_one_product( 4350 );

global $wpdb;
$result = $wpdb->get_results( "SELECT * FROM invalid query" );

if ( $wpdb->last_error ) {
  echo 'wpdb error: ' . $wpdb->last_error;
}

Inserts a row (the specified data) into the specified table.

After adding data, the created AUTO_INCREMENT value can be obtained in the variable: $wpdb->insert_id. If data insertion fails, $wpdb->insert_id will be equal to 0.

Returns

Int|false.

• number — the number of inserted rows.
• false — if the data was not inserted into the table.

Usage

global $wpdb;
$wpdb->insert( $table, $data, $format );

$table(string) (required)

The name of the table into which we will insert data.

$data(array)

The data to be inserted. Each element of the array looks like this: [ 'table column' => 'value' ].

If NULL is specified in the value, then the value will be set to NULL, and the specified format will be ignored.

The passed data MUST NOT be cleaned: esc_sql().

$format(array/string)

An array of data formats that will be associated with the specified values in the $data parameter. If a string is specified, it (the format) will be associated with all data. When specifying a format, WordPress converts the passed data to the specified format before saving the data. Possible formats:

• %s - string
• %d - integer
• %f - float

If not specified, the format string will be used for all values of $data, unless otherwise specified in the property wpdb::$field_types.
Default: null

Examples

global $wpdb;

$table = $wpdb->prefix . 'my_table_name';
$data = [ 'column1' => 'data one', 'column2' => 123 ];

$wpdb->insert( $table, $data );

$my_id = $wpdb->insert_id;

// insert string with values for two fields (values for other fields will be default)
$wpdb->insert( 'table', [ 'column' => 'foo', 'field' => 'bar' ] );

// specifying data types
$wpdb->insert( 'table', [ 'column' => 'foo', 'field' => 1337 ], [ '%s', '%d' ] );

Updates the specified data in the specified row of the DB table.

Returns

Int|false.

• integer — how many rows were processed.
• 0 — the query was executed correctly, but no one row was processed. If there is already data in the database and you are trying to update it by specifying exactly the same data then wpdb::update() will return 0.
• false — the query failed or there is an error in the query.

Usage

global $wpdb;
$wpdb->update( $table, $data, $where, $format, $where_format );

$table(string) (required)

The name of the table where the data needs to be updated.

$data(array) (required)

Data to be updated. The format is: [ 'column_name' => 'new value' ].

If you specify NULL in the value, the value will be set to NULL, the corresponding format is ignored in this case.

$where(array) (required)

A named array of WHERE clauses (in [ column => value ] pairs ).
Multiple clauses will be joined with ANDs.
Both $where columns and $where values should be "raw".
Sending a null value will create an IS NULL comparison - the corresponding format will be ignored in this case.

$format(array|string)

An array of formats to be mapped to each of the values in $data parameter.
If string is specified, than format will be used for all of the items in $data parameter.
When specifying the format, WordPress translates the passed data into the specified format before creating the query. Possible formats:

• %s - string
• %d - integer
• %f - float

If omitted, all values in $where will be treated as strings. The default format is controlled by wpdb::$field_types property.
Default: null

$where_format(массив/строка)

The same as $format parameter, but for $where data.
Default: null

Examples

$wpdb->update( 'table_name',
	[ 'column1' => 'value1', 'column2' => $_GET['val'] ],
	[ 'ID' => 1 ]
);

$wpdb->update( 'table',
	[ 'column1' => 'value1', 'column2' => $_GET['val'] ],
	[ 'ID' => 1 ],
	[ '%s', '%d' ],
	[ '%d' ]
);

$wpdb->update( 'table', [ 'column' => 'foo', 'field' => 'bar' ], [ 'ID' => 1 ] );
$wpdb->update( 'table', [ 'column' => 'foo', 'field' => 1337 ], [ 'ID' => 1 ], [ '%s', '%d' ], [ '%d' ] );

Обновляет или создает строку в таблице.

Если строка с указанным ключом PRIMARY KEY уже есть все остальные указанные поля будут обновлены у строки. Если такой строки в таблице еще нет, то функция вставит (insert) новую строку.

После вставки, ID созданный для колонки AUTO_INCREMENT можно получить в свойстве $wpdb->insert_id. Если вставка не удалась $wpdb->insert_id будет равен 0.

Возвращает

Int|false. Число (кол-во обработанных строк), 0 или false.

• число - сколько строк было обработано (удалено или вставлено). Если была замена (обновление), то для каждой обновленной строки вернется +2 (удаление и вставка). Если была просто вставка, то для каждой вставленной строки вернется +1.
• 0 - запрос был выполнен корректно, но ни одна строка не была обработана.
• false - запрос провалился - ошибка запроса. Или когда невозможно заменить или создать новую строку в таблице.

Вернет количество затронутых строк - это сумма вставленных и удаленных строк. Если вернулось 1 при замене одной строки - это значит что одна строка была вставлена и ни одна не была удалена. Если число больше 1 - это значит, что одна или больше строк были удалены перед вставкой новой строки.

Использование

global $wpdb;
$wpdb->replace( $table, $data, $format );

$table(строка) (обязательный)

Название таблицы, в которой нужно заменить данные.

$data(массив) (обязательный)

Данные, которые нужно заменить/вставить в формате [ 'название колонки' => 'новое значение' ].

Если в значении указать NULL, то в значение будет установлено в NULL, соответствующий формат при этом игнорируется.

$format(массив/строка)

Массив форматов данных которые будут ассоциированы с указанными значениями в параметре $data. Если указана строка, то она (формат) будет ассоциирован со всеми данными. При указании формата, WordPress переводит переданные данные в указанный формат перед созданием запроса. Возможные форматы:

• %s - строка
• %d - целое число
• %f - дробное число

Если не указать, то для всех значений $data будет указан формат строка, если иное не указано в свойстве wpdb::$field_types.
По умолчанию: null

Примеры

$wpdb->replace( 'table_name', [
	'ID'      => 1,
	'column1' => 'value1',
	'column2' => 123
] );

$wpdb->replace( 
	'table', 
	[ 'column' => 'foo', 'field' => 1337 ], 
	[ '%s', '%d' ] 
);

Deletes rows from the table based on the condition specified in the $where parameter.

Returns

Int|false. The number of deleted rows or 0 if nothing was deleted. false is returned on query error.

Usage

global $wpdb;
$wpdb->delete( $table, $where, $where_format );

$table(string) (required)

The name of the table.

$where(array) (required)

An array of conditions that will be used to select rows for deletion in the format [ 'column name' => 'value' ]. Multiple conditions will be combined using AND. If the value is set to NULL, the query will perform a comparison IS NULL, and the corresponding format will be ignored.

$where_format(array/string)

An array of data formats that will be associated with the specified values in the $where parameter. If a string is provided, it (the format) will be associated with all data. When a format is specified, WordPress converts the provided data to the specified format before creating the query. Possible formats:

• %s - string
• %d - integer
• %f - float

If not specified, the format string will be used for all values of $data, unless otherwise specified in the wpdb::$field_types.
Default: null

Examples

// Delete row with field ID=1 from table table
$wpdb->delete( 'table', [ 'ID' => 1 ] );

// Let's specify the format of the value $where
$wpdb->delete( 'table', [ 'ID'=>'1' ], [ '%d' ] ); // 1 will be treated as (int) (%d).

$where = [
	'UID'  => 248,
	'File' => "C:\file.txt"
];

$where_format = [
	'%d',
	'%s'
];

$wpdb->delete( $table, $where, $where_format );

Allows writing an SQL query with sanitization of the parameters passed to it.

In the query string, instead of the passed parameter, you need to use a placeholder:

• %d (integer)
• %f (float)
• %s (string)

Also, for each of the placeholders, a PHP variable must be specified that will replace the placeholder. When replacing, the variable will be sanitized. The syntax is similar to sprintf().

$sql = "SELECT * FROM table WHERE id = '$var'";

SELECT * FROM table WHERE id = '2' AND id = (DROP TABLE table2)

$sql = $wpdb->prepare( "SELECT * FROM table WHERE id = %s", $var );

$name   = esc_sql( $name );
$status = esc_sql( $status );

$wpdb->get_var( "SELECT something FROM table WHERE foo = '$name' and status = '$status'" );

Returns

String|null. Sanitized query string, if there is a query to prepare.

Usage

global $wpdb;
$wpdb->prepare( $query, ...$args );

$query(string) (required)

The query string. It can use placeholders:

• %d - number
• %s - string
• %f - floating point number (floating point number, since version 3.3).

...$args(string/number/array)

Variables that will be used to replace the placeholders %s %d %f in the query string.

These variables can be specified as comma-separated (like additional function parameters) or in an array:

• $wpdb->prepare( 'query', $param1, $param2 )
• $wpdb->prepare( 'query', [ $param1, $param2 ] ).

Examples

$sql = $wpdb->prepare(
	"SELECT * FROM `table` WHERE `column` = %s AND `field` = %d OR `other_field` LIKE %s",
	[ 'foo', 1337, '%bar' ]
);

$wpdb->get_results( $sql );

$sql = $wpdb->prepare(
	"SELECT DATE_FORMAT( `field`, '%%c' ) FROM `table` WHERE `column` = %s",
	'column_val'
);

$wpdb->get_results( $sql );

$metakey = "'crash' database";
$metavalue = "WordPress can 'break' the Database if the query is not escaped";

$wpdb->query( $wpdb->prepare(
	"INSERT INTO $wpdb->postmeta ( post_id, meta_key, meta_value ) VALUES ( %d, %s, %s )",
	10, $metakey, $metavalue
) );

$wpdb->prepare( 
	"SELECT id FROM $wpdb->posts WHERE id > %d AND `post_status` = %s", 
	array( $min_id, $status )
)

foreach ( $new_status_post_id_map as $new_status => $wordcamp_ids ) {

	$ids_pholders   = implode( ', ', array_fill( 0, count( $wordcamp_ids ), '%d' ) );
	$prepare_values = array_merge( array( $new_status ), $wordcamp_ids );

	$wpdb->query( $wpdb->prepare( "
		UPDATE `$table_name`
		SET `post_status` = %s
		WHERE ID IN ( $ids_pholders )",
		$prepare_values
	) );
}

// Does NOT work due to not enough arguments being passed.
$post_date = 'post_date';
$search_string = 'search_string';

echo $wpdb->prepare(
	'SELECT * FROM table_name WHERE `post_date` > %1$s AND `post_title` LIKE %2$s OR `post_content` LIKE %2$s',
	$post_date,
	$search_string
);

/* Result:

SELECT * FROM table_name WHERE `post_date` > post_date AND `post_title` LIKE search_string OR `post_content` LIKE search_string

PHP User Notice: Function wpdb::prepare was called incorrectly. The query does not contain the correct number of placeholders (3)
*/

$post_date = 'post_date';
$search_string = 'search_string';

echo $wpdb->prepare(
	'SELECT * FROM table_name WHERE post_date > %1$s AND post_title LIKE %2$s OR post_content LIKE %3$s',
	$post_date,
	$search_string,
	$search_string
);

/*
SELECT * FROM table_name WHERE post_date > post_date AND post_title LIKE search_string OR post_content LIKE search_string
*/

echo $wpdb->prepare( 'post_date = %1$s', 'post" date' ); // post\" date
echo $wpdb->prepare( 'post_date = %s', 'post" date' );   // post_date = 'post\" date'

$my_domain = 'example.com';

$sql = $wpdb->prepare(
	"SELECT * FROM $wpdb->options WHERE option_value LIKE %s;",
	'%' . $wpdb->esc_like( $my_domain ) . '%'
);

$in_values = [ 'one', 'two' ];

$in_pholders = implode( ',', array_fill( 0, count( $in_values ), '%s' ) );

$sql = $wpdb->prepare( 
	"SELECT $wpdb->posts WHERE post_type = %s WHERE post_name IN ( $in_pholders )",
	[ 'page', ...$in_values ]
);

echo $sql;  

// SELECT wp_posts WHERE post_type = 'page' WHERE post_name IN ( 'one','two' )

Prepares a string for use in the LIKE part of an SQL query. Processes special characters % and _.

Example:

$find = 'only 43% of planets';
$like = '%' . $wpdb->esc_like( $find ) . '%';
$sql  = $wpdb->prepare(
	"SELECT * FROM $wpdb->posts WHERE post_content LIKE %s",
	$like
);

Example of a chain of calls:

$sql = esc_sql( $wpdb->esc_like( $input ) );

Returns

String. Text for the LIKE part of the query. The result is not sanitized for the SQL query, so use wpdb::prepare() or wpdb::_real_escape() to add the result to the query.

Usage

global $wpdb;
$wpdb->esc_like( $text );

$text(string) (required)

Unprocessed text, in which special characters need to be escaped for the LIKE string. The string should not have additional or removed slashes.

Examples

$find = 'only 43% of planets';
$sql  = $wpdb->prepare( 
	"SELECT * FROM $wpdb->posts WHERE post_content LIKE %s", 
	'%' . $wpdb->esc_like( $find ) . '%' 
);

echo $sql; // SELECT * FROM wp_posts WHERE post_content LIKE '{d710cab}only 43\{d710cab} of planets{d710cab}'

$esc_like = $wpdb->esc_like( 'only 43% of planets' );
echo $esc_like;                                        // only 43\% of planets
echo esc_sql( $esc_like );                             // only 43\{f5fa52} of planets

global $wpdb;
$link = $wpdb->esc_like( $link ); // prepare a string for the LIKE argument
$link = esc_sql( $link ); // clear the variable
$link = '%' . $link . '%'; // create a full LIKE search variable

// find comments in the text or link of the author, there is a specified link
$spammy = $wpdb->query("SELECT comment_approved FROM $wpdb->comments
	WHERE (comment_content LIKE '$link' OR comment_author_url LIKE '$link')
		AND comment_approved = 'spam'
	LIMIT 1;"
);

global $wpdb;

$link = '%' . $wpdb->esc_like( $link ) . '%';

$comment = $wpdb->get_row( $wpdb->prepare(
	"SELECT * FROM $wpdb->comments WHERE comment_author_url LIKE %s LIMIT 1", $link
) );

Gets an array with information about the columns of the last query.

If the columns are not defined in the query, the function will return information about all columns of the table. This can be useful when an object has been returned, about which we know nothing.

It works based on cache, so you first need to make a request using get_results(), get_col(), get_var(), etc., and then call this function - it will return the column data of the last query.

Returns

Mixed. Column data.

Usage

global $wpdb;
$wpdb->get_col_info( $info_type, $col_offset );

$info_type(string)

Indicates what information we need to obtain. List of possible values:

• name - column name.
• table - name of the table to which the column belongs.
• max_length - maximum length of the column data.
• not_null - 1 if the column cell cannot accept NULL value.
• primary_key - 1 if the column is a primary key.
• unique_key - 1 if the column cells must always be unique.
• multiple_key - 1 if the column cells can be non-unique.
• numeric - 1 if the column contains numeric data.
• blob - 1 if the column contains BLOB type data (binary data).
• type - column type.
• unsigned - 1 if the column has UNSIGNED data type.
• zerofill - 1 if the column has ZEROFILL data type.

Default: name

$col_offset(number)

A pointer to which column's information needs to be obtained:

• -1 — information about all columns will be obtained as an array. Default.
• 0, 1, 2, ... — information about the specified column will be returned, where 0 - first column, 1 - second, etc.

Default: -1

Examples

global $wpdb;

$results = $wpdb->get_results( "SELECT * FROM $wpdb->postmeta" );

$cols_data = $wpdb->get_col_info( 'name' );
/*
Array
(
	[0] => meta_id
	[1] => post_id
	[2] => meta_key
	[3] => meta_value
)
*/

$cols_data = $wpdb->get_col_info( 'max_length' );
/*
Array
(
	[0] => 6
	[1] => 5
	[2] => 45
	[3] => 20205
)
*/

$cols_data = $wpdb->get_col_info( 'type' );
/*
Array
(
	[0] => 8
	[1] => 8
	[2] => 253
	[3] => 252
)
*/

$cols_data = $wpdb->get_col_info( 'primary_key' );
/*
Notice: Undefined property: stdClass::$primary_key in /wpexample.com/public_html/wp-includes/wp-db.php on line 3435
Notice: Undefined property: stdClass::$primary_key in /wpexample.com/public_html/wp-includes/wp-db.php on line 3435
Notice: Undefined property: stdClass::$primary_key in /wpexample.com/public_html/wp-includes/wp-db.php on line 3435
Notice: Undefined property: stdClass::$primary_key in /wpexample.com/public_html/wp-includes/wp-db.php on line 3435

Array
(
	[0] =>
	[1] =>
	[2] =>
	[3] =>
)
*/

echo $wpdb->get_col_info( 'name', 0 );       //> meta_id
echo $wpdb->get_col_info( 'name', 1 );       //> post_id
echo $wpdb->get_col_info( 'max_length', 0 ); //> 6
echo $wpdb->get_col_info( 'max_length', 1 ); //> 5

WordPress uses MySQL v5.7 or higher, OR MariaDB v10.3 or higher as the database.

All tables below have the default prefix wp_. You may have a different prefix - you can change it in the wp-config.php file, there it is specified in the variable:

$table_prefix = 'wp_';

The names of all tables are in the object wpdb and to get, for example, the name of the table wp_posts with the current prefix set in the configuration file can be as follows: $wpdb->posts.

WordPress Tables (regular installation)

Posts

read more

wp_posts

Table where posts, static pages, custom post types, attachments, etc. are written.

The posts table is probably the most important table in the WordPress database. Its name is sometimes misleading to people who think it only stores blog posts. However, despite the unfortunate name, it is an extremely powerful table that stores various types of content including posts, pages, menu items, media attachments and any custom post types that the site uses.

The flexibility of the table is provided by the post_type column, which indicates whether a row is a post, page, attachment, menu item, or other type.

wp_postmeta

Completes $wpdb->posts table. Stores additional data of posts they are also called metadata.

This table stores any additional information about individual posts. This table uses key/value pairs to store the data. This technique is used in WordPress in a number of tables throughout the database, allowing WordPress core, plugins, and themes to store an unlimited amount of data with different keys.

Users

read more

wp_users

Table with the data on registered users.

wp_usermeta

Additional information about users, such as Name, Nickname, permissions, etc.
Meta-data for $wpdb->users table.

Comments

read more

wp_comments

A table with records of comments.

wp_commentmeta

Meta fields for the $wpdb->comments table.

Taxonomies

read more

wp_terms

A table containing basic information about each element of the taxonomy.

wp_termmeta

A table containing additional fields for the $wpdb->terms table.

wp_term_taxonomy

Table with information about taxonomies, their description. The data in the wp_terms table are not yet taxonomies - this table extends the data of each term and assigns to each term the taxonomy to which it belongs.

wp_term_relationships

A table linking taxonomies to content (posts, entries, etc.). Each row of this table defines a relationship between a post (object) in wp_posts and a taxonomy term from the wp_term_taxonomy table.

Other tables

wp_options

Table of options (settings).

wp_links

Table with link. Obsolete table, not used for a long time, but the functionality is still working, but it should be enabled separately.

WordPress Tables (multisite)

When a WordPress site is converted into a multi-site installation, a "network" of sub-sites is created. The current site becomes the first subsite of the network. In the database, this site becomes the network site (wp_site), and each subsite is labelled as a blog (wp_blogs).

wp_blogs

Stores information about each site in the WordPress Multisite network.

blog_id is the site ID, and site_id is the network ID. This is legacy WP 2.x, when 'site' was called 'blog'. Thus, when you add a new sub-site (blog), a new record will be added to the wp_blogs table with a new blog_id value and the same site_id.

Field	Type	Key	Default	Description
blog_id	bigint(20)	PK: blog_id		Unique identifier for the blog.
site_id	bigint(20)		0	Identifier of the network to which the blog belongs.
domain	varchar(200)	K: domain		Domain name of the blog.
path	varchar(100)	K: domain		Path of the blog relative to the domain.
registered	datetime		'0000-00-00 00:00:00'	Date and time of blog registration.
last_updated	datetime		'0000-00-00 00:00:00'	Date and time of the last update of the blog.
public	tinyint(2)		1	Flag indicating whether the blog is public (1) or private (0).
archived	tinyint(2)		0	Flag indicating whether the blog is archived (1) or not (0).
mature	tinyint(2)		0	Flag indicating whether the blog contains adult content (1) or not (0).
spam	tinyint(2)		0	Flag indicating whether the blog is marked as spam (1) or not (0).
deleted	tinyint(2)		0	Flag indicating whether the blog is deleted (1) or not (0).
lang_id	int(11)	K: lang_id	0	Language identifier of the blog.

wp_blogmeta

Introduced in WP 5.1. Stores metadata related to blogs - auxiliary information about blogs. For example: db_version, db_last_updated. With the introduction of this table, you no longer need to use the wp_options table to store some data about the sub-site and use switch_to_blog() every time such data is needed.

Field	Type	Key	Default	Description
meta_id	bigint(20) unsigned	PK: meta_id		Unique identifier for the metadata.
blog_id	bigint(20)	K: blog_id	0	Identifier of the blog to which the metadata relates.
meta_key	varchar(255)	K: meta_key	NULL	Metadata key.
meta_value	longtext		NULL	Metadata value.

wp_registration_log

Logs registrations of new blogs in the network. Contains data on when the network blog was registered.
Users who register a new site after its activation are recorded in this table.

Field	Type	Key	Default	Description
ID	bigint(20)	PK: ID		Unique identifier for the record.
email	varchar(255)			Email address used during registration.
IP	varchar(30)	K: IP		IP address from which the registration was made.
blog_id	bigint(20)		0	Identifier of the blog associated with the registration.
date_registered	datetime		'0000-00-00 00:00:00'	Date and time of registration.

wp_signups

Stores information about applications for registering new users and sites.

Contains users who were registered through the basic WordPress registration from the page: Administration > Super Admin > Settings.

This table also stores data about sub-sites (blogs) that were registered but not activated. When the network allows new sites to be registered. After the site is activated, the record is deleted and a record is created in wp_blogs.

Field	Type	Key	Default	Description
signup_id	bigint(20)	PK: signup_id		Unique identifier for the signup.
domain	varchar(200)	K: domain		Domain name requested during registration.
path	varchar(100)	K: domain		Path requested during registration.
title	longtext			Blog title specified during registration.
user_login	varchar(60)	K: user_login		User login specified during registration.
user_email	varchar(100)	K: user_email		User email specified during registration.
registered	datetime		'0000-00-00 00:00:00'	Date and time of registration.
activated	datetime		'0000-00-00 00:00:00'	Date and time of activation.
active	tinyint(1)		0	Flag indicating whether the user is active (1) or not (0).
activation_key	varchar(50)	K: activation_key		User activation key.
meta	longtext			Additional metadata about the registration.

wp_site

Contains information about networks (main sites of the network).

This table will always contain one network, although the structure of the table allows for multiple networks in one database. This has not been implemented in WordPress itself, but can be achieved with plugins such as WP Multi Network or Networks for WordPress.

Field	Type	Key	Default	Description
id	bigint(20)	PK: id		Unique identifier for the site.
domain	varchar(200)	K: domain		Domain name of the site.
path	varchar(100)	K: domain		Path of the site relative to the domain.

wp_sitemeta

Stores metadata related to networks (sites) - network options and common options for all its sites.

This table is similar to wp_options, except that it stores options for the entire network of sites, not for a specific sub-site. It stores configuration (options) related to the network, as well as other data, such as plugin settings that should be available for the entire network (for any site in the network).

Field	Type	Key	Default	Description
meta_id	bigint(20)	PK: meta_id		Unique identifier for the metadata.
site_id	bigint(20)	K: site_id	0	Identifier of the site to which the metadata relates.
meta_key	varchar(255)	K: meta_key	NULL	Metadata key.
meta_value	longtext		NULL	Metadata value.

wp_users

A list of users from all sites in the network. This is a shared user table for the entire network. This is a familiar table, but in the multisite version, two additional fields are added: spam and delete.

wp_usermeta

Contains metadata for users. User settings for different sites in the network.

wp_blog_versions

Contains the current version of the database for each site. The data is updated when the database is updated for each site in the network.

When updating the version of WordPress that your site is running, changes to the database sometimes occur. Updating a multisite installation to a new version of WordPress will make these changes in the global tables. However, the update will also need to be applied to the set of tables for subsites in the network. This table records the version of the database for each blog in the network, so WordPress knows which blogs need updating and updates them after it starts.

Base tables of each site in the network

Network site tables: wp_posts, wp_options, etc. Identical tables are created for each site in the network, but with different prefixes, for example, wp_2_posts. Here, 2 is the subsite id.

A set of tables is created for subsites:

wp_2_options
wp_2_posts
wp_2_postmeta
wp_2_comments
wp_2_commentmeta
wp_2_terms
wp_2_termmeta
wp_2_term_relationships
wp_2_term_taxonomy
wp_2_links

Data Storage Mechanisms in MySQL

The storage engine is the part of the database responsible for reading and writing data. Starting from MySQL version 5.5, the default storage engine is InnoDB. This is the most commonly used data storage engine because it has row-level locking instead of full table locking (which is very important when performing mysqldump exports/backups), supports transactions (allowing SQL queries to be committed and rolled back), and has full support for foreign keys and relationship constraints.

MyISAM was the data storage engine that was previously used in WordPress, and you may still have old WordPress sites running on it. Some sites may even have a mix of tables using both MyISAM and InnoDB.

You may have encountered character encoding issues when transferring a WordPress database from one server to another and wondered what character sets and collations are mentioned in support articles. So what are character sets and collations?

A MySQL character set is a set of characters allowed in a string. In the alphabet, there are 26 characters - from a to z. Each letter is assigned a number, for example, a = 1, b = 2, c = 3 and so on. A letter is a character, and the associated number is the encoding.

The combination of all letters from a to z and their corresponding numeric encodings makes up a character set. MySQL supports many character sets that allow virtually any character to be stored in a string.

MySQL collation is a set of rules used for comparing characters in a specific character set. To compare strings, the database uses the character encoding numbers. An example of a collation rule is case-insensitive collation, where strings are compared even if they consist of lowercase or uppercase characters. Collations can be quite complex, as described in the MySQL documentation:

most collations have multiple rules, not only for distinguishing letter case but also for distinguishing accents ("accent" is a mark attached to a character, as in the German Ö), as well as for matching multiple characters (for example, a rule that states Ö = OE in one of the two German collations).
MySQL allows character sets and collations to be set at four levels: server, database, table, and column.

For WordPress sites, the recommended character set is utf8mb4, and the recommended collation is utf8mb4_unicode_520_ci. In WordPress 4.2, tables were converted from the utf8 character set to utf8mb4, allowing for the storage of 4-byte characters, meaning that any Unicode characters can be stored in the database. Peter Tasker wrote an excellent guide for developers on how Unicode works.