Using wpdb in WordPress

In this article, I will show you how to get started with the wpdb class, how to update/retrieve data from WordPress original tables and how to interact with your custom tables.

WordPress defines a class calledwpdb, which contains a set of functions used to interact with a database. Because this function is built into WordPress, there is no need to open a separate database connection.

Because methods in the wpdb class should not be called directly. We have to use the global $wpdb object instead! WordPress database has 12 (11 before vesion 4.4) tables by default. To interact with one of them, you can use this syntax: $wpdb->table_name, here are the name of tables:

posts.
comments.
links.
options.
postmeta.
terms.
term_taxonomy.
term_relationships.
termmeta.
commentmeta.
users.
usermeta.

To interact with custom tables. You can use this syntax:

$wpdb->prefix . 'table_name'
$wpdb->base_prefix . 'table_name' // if you are using WordPress multisite and want to use prefix that was defined in the wp-config.php

1 2	$wpdb->prefix . 'table_name' $wpdb->base_prefix . 'table_name' // if you are using WordPress multisite and want to use prefix that was defined in the wp-config.php

Ok, so now, Let’s get into wpdb methods:

SELECT a Variable

The get_var function returns a single variable from the database. It returns NULL if no result is found

$wpdb->get_var( $query );

1	$wpdb->get_var( $query );

For example: Retrieve the number of posts.

$post_count = $wpdb->get_var( "
	SELECT COUNT(*) 
	FROM $wpdb->posts" 
);

$post_count = $wpdb->get_var( "

SELECT COUNT(*)

FROM $wpdb->posts"

);

SELECT a Row

To retrieve an entire row from a query, use get_row. The function can return the row as an object, an associative array, or as a numerically indexed array.

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

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

$query: (string) The query you wish to run.
$output_type: One of three pre-defined constants. Defaults to OBJECT.
- OBJECT - result will be output as an object.
- ARRAY_A - result will be output as an associative array.
- ARRAY_N - result will be output as a numerically indexed array.
$row_offset: (integer) The desired row (0 being the first). Defaults to 0.

For example: Get all publish posts.

$list_posts = $wpdb->get_row( "
	SELECT * 
	FROM $wpdb->posts 
	WHERE post_status = 'publish'" 
);

$list_posts = $wpdb->get_row( "

SELECT *

FROM $wpdb->posts

WHERE post_status = 'publish'"

);

SELECT a Column

To SELECT a column, use get_col. This function outputs a one dimensional array.

$wpdb->get_col( $query );

1	$wpdb->get_col( $query );

For example: Get all user login names.

$list_posts = $wpdb->get_col( "
	SELECT user_login
	FROM $wpdb->users"
);

$list_posts = $wpdb->get_col( "

SELECT user_login

FROM $wpdb->users"

);

SELECT Generic Results

Generic, multiple row results can be pulled from the database with get_results. The function returns the entire query result as an array.

$wpdb->get_results( $query, $output_type );

1	$wpdb->get_results( $query, $output_type );

$query: (string) The query you wish to run.
$output_type: Defaults to OBJECT. See SELECT a Row for more information.

For example: Get ID, post title, post excerpt from posts.

$list_posts = $wpdb->get_results( "
	SELECT ID, post_title, post_excerpt
	FROM $wpdb->posts"
);

$list_posts = $wpdb->get_results( "

SELECT ID, post_title, post_excerpt

FROM $wpdb->posts"

);

Running General Queries

The query function allows you to execute any SQL query on the WordPress database.

$wpdb->query( $query );

1	$wpdb->query( $query );

For example: I have a custom table named wp_test, this table has two columns (ID and value), I want to delete a row that has ID equal 3.

$wpdb->query( "
	DELETE FROM {$wpdb->prefix}test
	WHERE ID = 3"
);

$wpdb->query( "

DELETE FROM {$wpdb->prefix}test

WHERE ID = 3"

);

Protect Queries Against SQL Injection Attacks

All data in SQL queries must be SQL-escaped before the SQL query is executed to prevent against SQL injection attacks. To do that, use preparefuncion.

$sql = $wpdb->prepare( 'query' , value_parameter[, value_parameter ... ] );

1	$sql = $wpdb->prepare( 'query' , value_parameter[, value_parameter ... ] );

$query: (string) The SQL query you wish to execute, with placeholders.
$value_parameter: (int|string|array) The value to substitute into the placeholder.

For example:

$wpdb->query( 
	$wpdb->prepare( "
		INSERT INTO {$wpdb->prefix}test
			( ID, value )
		VALUES ( %d, %s )", 
        2,
        $value,
	) 
);

$wpdb->query(

$wpdb->prepare( "

INSERT INTO {$wpdb->prefix}test

( ID, value )

VALUES ( %d, %s )",

$value,

)

);

INSERT row

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

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

$table: (string) The name of the table to insert data into.
$data: (array) Data to insert (in column => value pairs).
$format: (array|string) (optional) An array of formats to be mapped to each of the values in $data.

For example:

$wpdb->insert( 
	$wpdb->prefix . 'test',
	array( 
		'ID'		=> 5, 
		'value'		=> 'this is it',
	), 
	array( 
		'%d',	// value1
		'%s',	// value2
	), 
);

$wpdb->insert(

$wpdb->prefix . 'test',

array(

'ID' => 5,

'value' => 'this is it',

array(

'%d', // value1

'%s', // value2

);

UPDATE rows

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

1	$wpdb->update( $table, $data, $where, $format = null, $where_format = null );

$table: (string) The name of the table to update.
$data: (array) Data to update (in column => value pairs).
$where: (array) A named array of WHERE clauses (in column => value pairs). Multiple clauses will be joined with ANDs.
$format: (array|string) (optional) An array of formats to be mapped to each of the values in $data. If string, that format will be used for all of the values in $data.
$where_format: (array|string) (optional) An array of formats to be mapped to each of the values in $where. If string, that format will be used for all of the items in $where.

For example:

$wpdb->update( 
	$wpdb->prefix . 'test',
	array( 
		'value'		=> 'new value',
	), 
	array( 
		'ID'		=> 5,
	), 
);

$wpdb->update(

$wpdb->prefix . 'test',

array(

'value' => 'new value',

array(

'ID' => 5,

);

DELETE Rows

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

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

$table: (string) (required) Table name.
$where: (array) (required) A named array of WHERE clauses (in column -> value pairs). Multiple clauses will be joined with ANDs.
$where_format: (string/array) (optional) An array of formats to be mapped to each of the values in $where. If a string, that format will be used for all of the items in $where.

For example:

$wpdb->delete( 
	$wpdb->prefix . 'test',
	array( 
		'ID'		=> 5, 
	), 
	array( 
		'%d',	// value1
	), 
);

$wpdb->delete(

$wpdb->prefix . 'test',

array(

'ID' => 5,

array(

'%d', // value1

);

Very simple but important to know, thanks for your reading!

Duc Doan

Using wpdb in WordPress

doanmanhduc

SELECT a Variable

SELECT a Row

SELECT a Column

SELECT Generic Results

Running General Queries

Protect Queries Against SQL Injection Attacks

INSERT row

UPDATE rows

DELETE Rows

doanmanhduc

Leave a Reply Cancel reply