Codex tools: Log in / create account
WordPress provides a class of functions for all database manipulations. The class is called wpdb and is loosely based on the ezSQL class written and maintained by Justin Vincent.
Methods in the wpdb class should not be called directly. WordPress provides a global variable $wpdb, which is an instantiation of the class already set up to talk to the WordPress database. Always use the global $wpdb variable. (Remember to globalize $wpdb before using it in any custom functions.)
The $wpdb object can be used to read data from any table in the WordPress database, not just the standard tables that WordPress creates. For example to SELECT some information from a custom table called "mytable", you can do the following.
$myrows = $wpdb->get_results( "SELECT id, name FROM mytable" );
The $wpdb object can talk to any number of tables, but only one database: the WordPress database. In the rare case you need to connect to another database, you will have to instantiate your own object from the wpdb class with the appropriate connection details. For extremely complicated setups with many databases, consider using hyperdb instead.
The query function allows you to execute any SQL query on the WordPress database. It is best to use a more specific function (see below), however, for SELECT queries.
<?php $wpdb->query('query'); ?>
The function returns an integer corresponding to the number of rows affected/selected. If there is a MySQL error, the function will return FALSE. (Note: since both 0 and FALSE can be returned, make sure you use the correct comparison operator: equality == vs. identicality ===).
Note: As with all functions in this class that execute SQL queries, you must SQL escape all inputs (e.g., wpdb->escape($user_entered_data_string)). See the section entitled Protect Queries Against SQL Injection Attacks below.
Delete the 'gargle' meta key and value from Post 13.
$wpdb->query("
DELETE FROM $wpdb->postmeta WHERE post_id = '13'
AND meta_key = 'gargle'");
Performed in WordPress by delete_post_meta().
Set the parent of Page 15 to Page 7.
$wpdb->query("
UPDATE $wpdb->posts SET post_parent = 7
WHERE ID = 15 AND post_status = 'static'");
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); ?>
null will return the specified variable from the cached results of the previous query.
Retrieve the name of Category 4.
$name = $wpdb->get_var("SELECT name FROM $wpdb->terms WHERE term_ID=4");
echo $name;
Retrieve and display the number of users.
<?php
$user_count = $wpdb->get_var("SELECT COUNT(*) FROM $wpdb->users;");?>
<p><?php echo 'user count is ' . $user_count; ?></p>
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.
<?php $wpdb->get_row('query', output_type, row_offset); ?>
Get all the information about Link 10.
$mylink = $wpdb->get_row("SELECT * FROM $wpdb->links WHERE link_id = 10");
The properties of the $mylink object are the column names of the result from the SQL query (in this all of the columns from the $wpdb->links table).
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"
To SELECT a column, use get_col. This function outputs a dimensional array. If more than one column is returned by the query, only the specified column will be returned by the function, but the entire result is cached for later use.
<?php $wpdb->get_col('query',column_offset); ?>
null will return the specified column from the cached results of the previous query.
Get all URLs from the links table.
$link_urls = $wpdb->get_col("SELECT link_url
FROM $wpdb->links" );
Not a real-world example
Generic, mulitple row results can be pulled from the database with get_results. The function 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.
<?php $wpdb->get_results('query', output_type); ?>
null will return the data from the cached results of the previous query.
Get the IDs and Titles of all the Drafts by User 5 and echo the Titles.
$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;
}
Get all information on the Drafts by User 5.
<?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
endforeach;
else :
?>
<h2> Not Found</h2>
<?php endif; ?>
Insert a row into a table.
Insert two columns in a row, the first value being a string and the second a number:
$wpdb->insert( 'table', array( 'column1' => 'value1', 'column2' => 123 ), array( '%s', '%d' ) )
Update a row in the table.
Update a row, where the ID is 1, the value in the first column is a string and the value in the second column is a number:
$wpdb->update( 'table', array( 'column1' => 'value1', 'column2' => 'value2' ), array( 'ID' => 1 ), array( '%s', '%d' ), array( '%d' ) )
For a more complete overview of SQL escaping in WordPress, see database Data Validation. That Data Validation article is a must-read for all WordPress code contributors and plugin authors.
Briefly, though, all data in SQL queries must be SQL-escaped before the SQL query is executed to prevent against SQL injection attacks. This can be conveniently done with the prepare method, which uses a sprintf()-like syntax.
<?php $sql = $wpdb->prepare( 'query'[, value_parameter, value_parameter ... ] ); ?>
%s and %d placeholders.
Add Meta key => value pair "Harriet's Adages" => "WordPress' database interface is like Sunday Morning: Easy." to Post 10.
$metakey = "Harriet's Adages";
$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 ) );
Performed in WordPress by add_meta().
Notice that you do not have to worry about quoting strings. Instead of passing the variables directly into the SQL query, use a %s placeholder for strings and a %d placedolder for integers. You can pass as many values as you like, each as a new parameter in the prepare() method.
You can turn error echoing on and off with the show_errors and hide_errors, respectively.
<?php $wpdb->show_errors(); ?>
<?php $wpdb->hide_errors(); ?>
You can also print the error (if any) generated by the most recent query with print_error.
<?php $wpdb->print_error(); ?>
You can retrieve information about the columns of the most recent query result with get_col_info. This can be useful when a function has returned an OBJECT whose properties you don't know. The function will output the desired information from the specified column, or an array with information on all columns from the query result if no column is specified.
<?php $wpdb->get_col_info('type', offset); ?>
You can clear the SQL result cache with flush.
<?php $wpdb->flush(); ?>
This clears $wpdb->last_result, $wpdb->last_query, and $wpdb->col_info.
The WordPress database tables are easily referenced in the wpdb class.