⚡ NEW: Class Reference/wpdb

More Information

An instantiated wpdb class can talk to any number of tables, but only to one database at a time. In the rare case you need to connect to another database, instantiate your own object from the wpdb class with your own database connection information.

Note: Each method contained within the class is listed in the Methods section (below). In addition, each method has its own help page; this is where you’ll find detailed usage information for the method you’re interested in.

An Important Note Regarding Escaping

Some of the methods in this class take an SQL statement as input. All untrusted values in an SQL statement must be escaped to prevent SQL injection attacks. Some methods will escape SQL for you; others will not. Check the documentation to be sure before you use any method in this class. For more on SQL escaping in WordPress, see the section entitled Protect Queries Against SQL Injection Attacks below.

Using the $wpdb global object

WordPress provides a global object, $wpdb, which is an instantiation of the wpdb class. By default, $wpdb is instantiated to talk to the WordPress database.

The recommended way to access $wpdb in your WordPress PHP code is to declare $wpdb as a global variable using the global keyword, like this:

<?php
// 1st Method - Declaring $wpdb as global and using it to execute an SQL query statement that returns a PHP object
global $wpdb;
$results = $wpdb->get_results( "SELECT * FROM {$wpdb->prefix}options WHERE option_id = 1", OBJECT );

Alternatively, if the above doesn’t suit your needs for whatever reason, use the superglobal $GLOBALS in the following manner:

<?php
// 2nd Method - Utilizing the $GLOBALS superglobal. Does not require global keyword ( but may not be best practice )
$results = $GLOBALS['wpdb']->get_results( "SELECT * FROM {$wpdb->prefix}options WHERE option_id = 1", OBJECT );

The $wpdb object can be used to read data from any table in the WordPress database, not just those created by WordPress itself.

Protect Queries Against SQL Injection Attacks

For a more complete overview of SQL escaping in WordPress, see database Data Validation. It is a must-read for all WordPress code contributors and plugin authors.

All data in SQL queries must be SQL-escaped before the SQL query is executed to prevent against SQL injection attacks. The prepare method performs this functionality for WordPress, which supports both a sprintf()-like and vsprintf()-like syntax.

Using it looks like this:

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

query: (string) The SQL query you wish to execute, with placeholders (see below).
value_parameter: (int|string|array) The value to substitute into the placeholder. Many values may be passed by simply passing more arguments in a sprintf()-like fashion. Alternatively the second argument can be an array containing the values as in PHP’s vsprintf() function. Care must be taken not to allow direct user input to this parameter, which would enable array manipulation of any query with multiple placeholders.Note: Values cannot be SQL-escaped.

Placeholders

The query parameter for prepare accepts sprintf()-like placeholders:

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

Notes on placeholders

Any other % characters may cause parsing errors unless they are escaped.
All % characters inside SQL string literals, including LIKE wildcards, must be double-% escaped as %%.
Leave %d, %f, and %s unquoted in the query string.

Examples

Add Meta key => value pair “Funny Phrases” => “WordPress’ database interface is like Sunday Morning: Easy.” to Post 10.

<?php
$metakey   = 'Funny Phrases';
$metavalue = "WordPress' database interface is like Sunday Morning: Easy.";

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

The same query using vsprintf()-like syntax. Note that in this example we pack the values together in an array. This can be useful when we don’t know the number of arguments we need to pass until runtime.

<?php
$metakey   = 'Funny Phrases';
$metavalue = "WordPress' database interface is like Sunday Morning: Easy.";

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

In both cases, we do not need to escape the strings because we are passing them using placeholders. You can pass as many values as you like, provided each one has a corresponding placeholder in the prepare() method call.

Common Tasks

Each of the class’s properties and methods are listed below, and most have their own help page that you should consult for detailed usage information. This section, however, gives an overview of some of the more common tasks this class can be used to perform.

SELECT a Variable

The get_var function returns a single variable from the database. Though only one variable is returned, the entire result of the query is cached for later use. Returns NULL if no result is found.

<?php $wpdb->get_var( 'query', column_offset, row_offset ); ?>

query: (string) The query you wish to run. Setting this parameter to null will return the specified variable from the cached results of the previous query.
column_offset: (integer) The desired column (0 being the first). Defaults to 0.
row_offset: (integer) The desired row (0 being the first). Defaults to 0.

Examples

Retrieve and display the number of users.

<?php
$user_count = $wpdb->get_var( "SELECT COUNT(*) FROM $wpdb->users" );
echo "<p>User count is {$user_count}</p>";
?>

Retrieve and display the sum of a Custom Field value.

<?php
// set the meta_key to the appropriate custom field meta key
$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 miles is {$allmiles}</p>";
?>

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. If more than one row is returned by the query, only the specified row is returned by the function, but all rows are cached for later use. Returns null if no result is found, consider this when using the returned value in arguments, see example below.

<?php $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.

Examples

Get all the information about Link 10.

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

The properties of the <code>$mylink</code> object are the column names of the result from the SQL query (in this example all the columns from the <code>$wpdb->links</code> table, but you could also query for specific columns only).

echo $mylink->link_id; // prints "10"

In contrast, using

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

would result in an associative array:

echo $mylink['link_id']; // prints "10"

and

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

would result in a numerically indexed array:

echo $mylink[1]; // prints "10"

If there is no record with ID 10 in the links table, null will be returned. The following would then be false:

if ( null !== $mylink ) {
// do something with the link
return true;
} else {
// no link found
return false;
}

SELECT a Column

To SELECT a column, use get_col. This method outputs a one dimensional array. If more than one column is returned by the query, only the specified column will be returned, but the entire result is cached for later use. Returns an empty array if no result is found.

get_col( 'query', column_offset ); ?>

query: (string) the query you wish to execute. Setting this parameter to null will return the specified column from the cached results of the previous query.
column_offset: (integer) The desired column (0 being the first). Defaults to 0.

Examples

For this example, assume the blog is devoted to information about automobiles. Each post describes a particular car (e.g. 1969 Ford Mustang), and three Custom Fields, manufacturer, model, and year, are assigned to each post. This example will display the post titles, filtered by a particular manufacturer (Ford), and sorted by model and year.

The get_col method is used to return an array of all the post ids meeting the criteria and sorted in the correct order. Then a foreach construct is used to iterate through that array of post ids, displaying the title of each post. Note that the SQL for this example was created by Andomar.

<?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( intval( $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
}
}
?>

This example lists all posts that contain a particular custom field, but sorted by the value of a second custom field.

<?php
// List all posts with custom field Color, sorted by the value of custom field Display_Order
// does not exclude any 'post_type'
// assumes each post has just one custom field for Color, and one for Display_Order
$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( intval( $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
}
}
?>

SELECT Generic Results

Generic, multiple row results can be pulled from the database with get_results. The method returns the entire query result as an array. Each element of this array corresponds to one row of the query result and, like get_row, can be an object, an associative array, or a numbered array. If no matching rows are found, or if there is a database error, the return value will be an empty array. If your $query string is empty, or you pass an invalid $output_type, NULL will be returned.

get_results( 'query', output_type );

⚡ NEW: Class Reference/wpdb - High Quality

class wpdb {}

In this article

More Information

An Important Note Regarding Escaping

Using the $wpdb global object

Protect Queries Against SQL Injection Attacks

Placeholders

Examples

Common Tasks

SELECT a Variable

Examples

SELECT a Row

Examples

SELECT a Column

Examples

SELECT Generic Results