Codex tools: Log in
WordPress包含了一些預設用來操作資料庫(Database)函數(Function)的類別(Class)——wpdb，該類基於ezSQL（由Justin Vincent維護的資料庫操作專案）編寫，包含了其基本的功能。
請不要直接調用wpdb類中的方法。WordPress定義了$wpdb的全域變數，所以請直接調用該全域變數$wpdb的實例來操作資料庫。（調用之前不要忘了聲明引用全域變數$wpdb。參考globalize）
$wpdb物件可以用來操作WordPress資料庫中的每一個表，不僅僅是WordPress自動創建的基本表。例如，你有一個自訂的表叫做mytable，那麼可以使用如下語句來查詢：
$myrows = $wpdb->get_results( "SELECT id, name FROM mytable" );
$wpdb物件可以讀取多個表，但是其只針對WordPress的資料庫。如果你需要連接其他資料庫，那麼你應該使用你自己的資料庫連接資訊，並調用wpdb類來創建一個你自己的資料庫操作實例。如果你有多個資料庫需要連接，那麼你可以考慮使用hyperdb來替代$wpdb。
這個查詢函數允許你在wordpress的資料庫裡運行任何SQL查詢。當然了，最好能利用如下的特定函數，
<?php $wpdb->query('query'); ?>
本函數傳回特定受影響或已選擇行(rows)數的整數(integer)。當發生MySQL錯誤, 本函數將傳回FALSE. (注意: 因著0及FALSE可以同時傳回, 請認清你使用了正確的比較運算符
== vs. 恒等(identicality) ===).
註: 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.
刪去 meta key 為 'gargle' 及 post id 為 13 的記錄：
$wpdb->query("
DELETE FROM $wpdb->postmeta WHERE post_id = '13'
AND meta_key = 'gargle'");
在WordPress中已可用 delete_post_meta() 運行
設定第15頁的父頁為第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 and display the number of users.
<?php
$user_count = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM $wpdb->users;"));
echo '<p>User count is ' . $user_count . '</p>';
?>
Retrieve and display the sum of a Custom Field value.
<?php
$meta_key = 'miles';//set this to appropriate custom field meta key
$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>';
?>
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.
<?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. Returns an empty array if no result is found.
<?php $wpdb->get_col('query',column_offset); ?>
null will return the specified column from the cached results of the previous query.
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 form of the wpdb Class 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
}
}
?>
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.
Since this function uses the '$wpdb->query()' function all the class variables are properly set. The results count for a 'SELECT' query will be stored in $wpdb->num_rows.
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.
<?php $wpdb->insert( $table, $data, $format ); ?>
Possible format values: %s as string; %d as decimal number; and %f as float.
After insert, the ID generated for the AUTO_INCREMENT column can be accessed with:
$wpdb->insert_id
This function returns false if the row could not be inserted.
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' ) )
更新資料庫的記錄。
<?php $wpdb->update( $table, $data, $where, $format = null, $where_format = null ); ?>
Possible format values: %s as string; %d as decimal number and %f as float. If omitted, all values in $where will be treated as strings.
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 supports both a sprintf()-like and vsprintf()-like syntax.
<?php $sql = $wpdb->prepare( 'query' [, value_parameter, value_parameter ... ] ); ?>
%s and %d 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 %%.
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().
The same query using vsprintf()-like syntax.
$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 )",
array(10, $metakey, $metavalue) ) );
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.
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.
If you are using Multi-Site, you also have access to the following:
The WordPress database tables are easily referenced in the wpdb class.
