WordPress:Function Reference/wpdb Class

Interfacing With the Database

WordPress provides a class of functions for all database manipulations. The class is called wpdb and is based on the ezSQL class written and maintained by Justin Vincent. Though the WordPress class is slightly different than the ezSQL class, their use is essentially the same. Please see the ezSQL documentation for more information.

WordPress提供了一类函数，用于所有的数据库操作。这个class称为wpdb而且是以ezSQL class为基础的，由Justin Vincent编写和维护。虽然WordPress class与ezSQL class稍微不同，两者的用法本质山是相同的。更多的信息，请看看ezSQL 文档。

用法注意

Within PHP code blocks in a template the $wpdb class should work as expected, but in the case of a plugin or external script it may be necessary to scope it to global before calling it in your code. This is briefly explained in the Writing a Plugin documentation.

在模板中的PHP代码块内，$wpdb class应该如期望地那样运行，但是如果是插件或者外部脚本，可能有必要将其扩展到全局，再在代码中对其调用。这主要在编写插件文件中得到了解释。

If writing a plugin that will use a table that is not created in a vanilla WordPress installation, the $wpdb class can be used to read data from any table in the database in much the same way it can read data from a WordPress-Installed table. The query should be structured like "SELECT id, name FROM mytable", don't worry about specifying which database to look for the table in, the $wpdb object will automatically use the database WordPress is installed in. Any of the functions below will work with a query that is set up like this.

如果编写一个插件，使用的表格，不是普通WordPress安装中创建的表格，就可以使用$wpdb class阅读数据库中任何表格中的数据，方式就如$wpdb class阅读WordPress安装的表格中的数据。查询的结构应该是"从mytable中，选择id, 名称",不要忧心从哪个数据库中查找表格，$wpdb object会自动地使用WordPress安装在的数据库。下面的任何函数，都会与这样设置的查询共同运行。

查询 – 在数据库中运行任何查询

The query function allows you to execute any query on the WordPress database. It is best to use a more specific function, however, for SELECT queries.

query函数能够使你在WordPress数据库中执行任何的查询。最好为SELECT查询使用更确切的查询：

%%% <?php $wpdb->query('query'); ?> %%%

query

(string) The query you wish to run.

%%% <?php $wpdb->query('query'); ?> %%%

query

(string) 你希望运行的查询。

If there are any query results, the function will return an integer corresponding to the number of rows affected and the query results will cached for use by other wpdb functions. If there are no results, the function will return (int) 0. 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 ===).

如果有什么查询结果，函数会返回一个整数关于多少个组受到了影响而且查询结果会储存，让另一个wpdb函数使用。如果没有结果，函数会返回(int) 0。如果有MySQL错误，返回会返回错误的。（注:因为即可以返回0又可以返回错误的，确定你使用了正确的比较运算符：equality == vs. identicality ===）。

Note: It is advisable to use the wpdb->escape($user_entered_data_string) method to protect the database against SQL injection attacks by malformed or malicious data, especially when using the INSERT or UPDATE SQL commands on user-entered data in the database. See the section entitled "Escape for SQL Queries" below.

注：建议使用wpdb->escape($user_entered_data_string)方法来保护数据库，免受不好的或者恶意的数据通过SQL袭击，特别是用户向数据库中输入数据使用插入或者更新SQL命令的时候。请看看下面标题为"躲避SQL 查询"这部分。

Additionally, if you wish to access the database from your code file which is not placed inside one of the standard plugin locations, you will need to include_once() the wp-db.php file as well as the wp-config.php file. Including only the wp-db.php file will not set the database connection information resulting in an error message like "Wordpress could not connect to the database".

此外，如果你想要从代码文件中访问数据库，但是你的代码文件不在标准的插件 locations，你就需要include_once() wp-db.php文件和wp-config.php文件。只包含wp-db.php文件不会设置数据库连接信息，反而会产生错误信息像"Wordpress 不能够连接到数据库"。

It is always advisable to put your functionality inside a plugin. However, if you need it in some cases, this workaround is available. For example, this is the code in a file has_great_code.php in the root/installation directory :

总是将函数放入插件中，是可取的。然而，有时候，如果你需要函数，可以使用这个workaround。例如，这是根/安装目录中的has_great_code.php的文件的代码。

include_once('wp-config.php');
include_once('wp-includes/wp-db.php');

include_once('wp-config.php');
include_once('wp-includes/wp-db.php');

Examples

Add Post 13 to Category 2:

例子

将第13篇文章添加到类别2：

$wpdb->query("
	INSERT INTO $wpdb->post2cat (post_id, category_id)
	VALUES (13, 2)");

$wpdb->query("
	INSERT INTO $wpdb->post2cat (post_id, category_id)
	VALUES (13, 2)");

Delete the 'gargle' meta key and value from Post 13.

从第十三篇文章中删除'gargle' meta key和参数值。

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

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

Performed in WordPress by delete_post_meta().

在WordPress中由delete_post_meta()执行。

Set the parent of Page 15 to Page 7. 将第15个网页的母网页设置为网页7。

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

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

get_var – 选择一个变数

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.

get_var函数返回数据库中的单一变数。虽然只返回了一个变数，查询的整个结果已经得到了储存，以便以后使用。如果没有找到结果，返回零。

%%% <?php $wpdb->get_var('query',column_offset,row_offset); ?> %%% %%% <?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.

query

(string)你想要运行的查询。将这个参数设置为零会返回先前查询所储存的结果中某个特别的变数。

column_offset

(integer) The desired column (0 being the first). Defaults to 0.

column_offset

(integer)想得到的栏(0 是第一个)。默认为0。

row_offset

(integer) The desired row (0 being the first). Defaults to 0.

row_offset

(integer) (integer)想得到的组(0 是第一个)。默认为0。

Examples

Retrieve the name of Category 4.

返回类别4的名称。

例子

$name = $wpdb->get_var("SELECT cat_name FROM $wpdb->categories WHERE cat_ID=4");
echo $name;

$name = $wpdb->get_var("SELECT cat_name FROM $wpdb->categories WHERE cat_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>

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

get_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 numbered 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.

要得到查询中的整个组，请使用get_row。这个函数能够返回组作为object，一个相关的数据或者一个有限的数组。如果查询返回了多个组，函数只会返回特别的组，但是所有的组都会储存，便于以后使用。

%%% <?php $wpdb->get_row('query', output_type, row_offset); ?> %%%

%%% <?php $wpdb->get_row('query', output_type, row_offset); ?> %%%

query

(string) The query you wish to run. Setting this parameter to null will return the specified row from the cached results of the previous query.

query

(string)你希望运行的查询。将这个参数设置为零会返回上一个查询中储存的某个组。

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.

output_type

先前定义的三个常数中的一个。默认为OBJECT。

• OBJECT –结果会作为object输出。
• ARRAY_A –结果会作为结合的数组返回。

• ARRAY_N - result will be output as a numbered array.

row_offset

(integer) The desired row (0 being the first). Defaults to 0.

• ARRAY_N –结果会作为有限的数组返回。

row_offset

(integer)想得到的组(0是第一个)。默认为0。

Examples

Get all the information about Link 10.

例子

得到关于链接10的所有信息。

$mylink = $wpdb->get_row("SELECT * FROM $wpdb->links WHERE link_id = 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"

$mylink object的属性是从SQL查询中返回的结果的栏名称（在这里，是$wpdb->links表格中的所有的栏）。 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"

$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 numbered array:

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

$mylink = $wpdb->get_row("SELECT * FROM $wpdb->links WHERE link_id = 10", ARRAY_N); would result in a numbered array:

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

get_col – 选择一个栏

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.

要选择一栏，请使用get_col。这个函数输出多维数组。如果查询返回了几个栏，函数只会返回某个特别的栏，但是整个结果会得到保存，以便于下次使用。

%%% <?php $wpdb->get_col('query',column_offset); ?> %%%

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

query

(string)你想要执行的查询。将这个参数设置为零会返回先前的查询保存的结果中的某个栏。

column_offset

(integer) 想要的栏 (0 是第一个)默认为 0。

Examples

Get all the Categories to which Post 103 belongs.

例子

得到第103篇文章所属于的所有的类别。

$postcats = $wpdb->get_col("SELECT category_id
	FROM $wpdb->post2cat
	WHERE post_id = 103
	ORDER BY category_id");

$postcats = $wpdb->get_col("SELECT category_id
	FROM $wpdb->post2cat
	WHERE post_id = 103
	ORDER BY category_id");

Performed in WordPress by wp_get_post_cats().

在WordPress中由wp_get_post_cats()执行。

get_results – 选择一般结果

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.

一般的，多个组结果可以使用get_results，从数据库中得到。函数返回整个查询作为一个数组。这个数组中的每个元素与查询结果中的一组相对应，如get_row可以是个object，一个结合的数组或者有限的数组。

%%% <?php $wpdb->get_results('query', output_type); ?> %%%

%%% <?php $wpdb->get_results('query', output_type); ?> %%%

query

(string) The query you wish to run. Setting this parameter to null will return the data from the cached results of the previous query.

output_type

One of three pre-defined constants. Defaults to OBJECT. See [[WordPress:#SELECT a Row|SELECT a Row]] and its examples for more information.

query

(string)你想要运行的查询。将这个参数设置为零会返回先前查询结果保存的数据。

output_type

三个先前定义的常数中的一个。默认为OBJECT。请看看[[WordPress:#SELECT a Row|选择一个组]]以及例子，得到更多的信息。

• 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 numbered array.

• OBJECT –输出结果作为object。
• ARRAY_N -输出结果作为有限的数组。
• ARRAY_ -输出结果作为结合的数组。

Examples

Get the IDs and Titles of all the Drafts by User 5 and echo the Titles.

例子

得到第5个的所有草稿的IDs和标题并且echo标题。

$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;
}

$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;
}

Using the OBJECT output_type, get the 5 most recent posts in Categories 3,20, and 21 and display the permalink title to each post. (example works at WordPress Version 2.2.1)

使用OBJECT output_type，得到类别3，20，和21的最近五篇文章并且显示每篇文章的permalink 标题。（例子在WordPress2.2.1版本中能够运行）

<?php
$querystr ="
  SELECT $wpdb->posts.* FROM $wpdb->posts 
  LEFT JOIN $wpdb->post2cat ON ($wpdb->posts.ID = $wpdb->post2cat.post_id) 
  WHERE $wpdb->posts.post_status = 'publish' 
  AND $wpdb->posts.post_type = 'post' 
  AND $wpdb->post2cat.category_id IN (3,20,21) 
  ORDER BY $wpdb->posts.post_date DESC 
  LIMIT 5";
$pageposts = $wpdb->get_results($querystr, OBJECT);
?>
<?php if ($pageposts): ?>
  <?php foreach ($pageposts as $post): ?>
    <?php 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; ?>
  <?php else : ?>
    <h2> Not Found</h2>
<?php endif; ?>

<?php
$querystr ="
  SELECT $wpdb->posts.* FROM $wpdb->posts 
  LEFT JOIN $wpdb->post2cat ON ($wpdb->posts.ID = $wpdb->post2cat.post_id) 
  WHERE $wpdb->posts.post_status = '发表' 
  AND $wpdb->posts.post_type = '文章' 
  AND $wpdb->post2cat.category_id IN (3,20,21) 
  ORDER BY $wpdb->posts.post_date DESC 
  LIMIT 5";
$pageposts = $wpdb->get_results($querystr, OBJECT);
?>
<?php if ($pageposts): ?>
  <?php foreach ($pageposts as $post): ?>
    <?php 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; ?>
  <?php else : ?>
    <h2>没有找到</h2>
<?php endif; ?>

准备 – 保护你的SQL 查询免受SQL Injection攻击

If you're creating an SQL query, make sure you protect against SQL injection attacks. This can be conveniently done with the prepare method.

如果你正创建一个SQL查询，确定你能够免受SQL injection的袭击。使用prepare方法，能够轻易地做到实现保护。

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

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

Note that you may need to use PHP's stripslashes() when loading data back into WordPress that was inserted with a prepared query.

注，WordPress插入了先前的查询，你想将数据载入，返回WordPress，你可能需要使用PHP的stripslashes()。

Examples

Add Meta key => value pair "Harriet's Adages" => "WordPress' database interface is like Sunday Morning: Easy." to Post 10.

例子

添加Meta key => value pair "Harriet's Adages" => "WordPress'数据库界面就像星期天的早晨：简单。"到第十篇文章。

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

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

由 add_meta()在WordPress中执行。

Notice that you do not have to worry about quoting strings. Instead of passing the variables directly into the SQL query, place a %s marker for strings and a %d marker for integers. You can pass as many values as you like, each as a new parameter in the prepare() method.

注意你不需要担心引入的字符串问题。不要将变数直接传递到SQL查询，为字符串加上标记%s，为整数加上标记%d。你想传递多少参数值，就可以传递多少参数值，每个参数值，都是prepare() method中的一个新参数。

show/hide_errors – 显示和隐藏SQL错误

You can turn error echoing on and off with the show_errors and hide_errors, respectively.

你可以分别使用show_errors和hide_errors，打开和关闭错误echoing。

%%% <?php $wpdb->show_errors(); ?> <?php $wpdb->hide_errors(); ?> %%%

%%% <?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.

你也可以打印出大多数最近的查询print_error的错误（如果有这个错误的话）。

%%% <?php $wpdb->print_error(); ?> %%%

%%% <?php $wpdb->print_error(); ?> %%%

get_col_info – 得到栏信息

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.

你可以得到最近的get_col_info查询结果的栏信息。当函数返回一个OBJECT，但是你不知道这个OBJECT的属性的时候，得到了栏信息，就很有用。这个函数会从某个栏中输出你想要的信息，或者如果没有规定栏，函数会返回数组，关于查询结果中所有的栏的信息。

%%% <?php $wpdb->get_col_info('type', offset); ?> %%%

%%% <?php $wpdb->get_col_info('type', offset); ?> %%%

type

(string) What information you wish to retrieve. May take on any of the following values (list taken from the ezSQL docs). Defaults to name.

• name - column name. Default.

• name –栏名称。默认。

type

(string)你想要得到什么信息。可能会获得以下任何参数值（列表从ezSQL docs中得到）。默认为名称。

• table - name of the table the column belongs to
• max_length - maximum length of the column
• not_null - 1 if the column cannot be NULL
• primary_key - 1 if the column is a primary key
• table-栏所属于的某个表格的名称
• max_length –栏的最长长度
• not_null -1如果栏不能是零
• primary_key – 1如果栏是个primary key

• unique_key - 1 if the column is a unique key

• unique_key – 1如果栏是个unique key

• multiple_key - 1 if the column is a non-unique key

• multiple_key – 1如果栏是个非唯一的key

• numeric - 1 if the column is numeric

• numeric – 1如果栏是数字的

• blob - 1 if the column is a BLOB

• blob – 1如果栏是个BLOB

• type - the type of the column

• type –栏的类型

• unsigned - 1 if the column is unsigned

• unsigned – 1如果栏没有符号

• zerofill - 1 if the column is zero-filled

• zerofill – 1如果栏是zero-filled

offset

(integer) Specify the column from which to retrieve information (with 0 being the first column). Defaults to -1.

offset

(integer)指定从哪个栏中得到信息(0 代表第一个栏)。默认为-1。

• -1 - Retrieve information from all columns. Output as array. Default.

• -1 –返回所有栏中得到的信息。作为数组输出。默认。

• Non-negative integer - Retrieve information from specified column (0 being the first).

• 非负数的整数 –返回某个特别的栏中的信息(0 代表第一个栏)。

flush –清除缓存

You can clear the SQL result cache with flush.

你可以使用flush清除SQL结果缓存。

%%% <?php $wpdb->flush(); ?> %%%

%%% <?php $wpdb->flush(); ?> %%%

This clears $wpdb->last_result, $wpdb->last_query, and $wpdb->col_info.

这清除了$wpdb->last_result, $wpdb->last_query, 和$wpdb->col_info。

Class 变数

$show_errors

Whether or not [[WordPress:#Showing and Hiding SQL Errors|Error echoing]] is turned on. Defaults to TRUE.

$show_errors

[[WordPress:#Showing and Hiding SQL Errors|错误 echoing]]是否打开了。默认为正确的。

$num_queries

The number of queries that have been executed.

$num_queries

已经执行的查询的数目。

$last_query

The most recent query to have been executed.

$last_query

已经执行的最近的查询。

$queries

You may save all of the queries run on the database and their stop times be setting the SAVEQUERIES constant to TRUE (this constant defaults to FALSE). If SAVEQUERIES is TRUE, your queries will be stored in this variable as an array.

$queries

你可以保存数据库中运行的所有查询并且查询结束时间设置为SAVEQUERIES常数为正确的（这个常数默认为错误的）。如果SAVEQUERIES是正确的，你的查询就会作为数组在这个变数中查询。

$last_results

The most recent query results.

$last_results

最近的查询结果。

$col_info

The column information for the most recent query results. See [[WordPress:#Getting Column Information|Getting Column Information]].

$col_info

最近的查询结果的栏信息。请看看[[WordPress:#Getting Column Information|得到栏信息]]。

$insert_id

ID generated for an AUTO_INCREMENT column by the most recent INSERT query.

$insert_id

最近的插入查询的AUTO_INCREMENT栏产生的ID。

表格

<!—当数据库结构得到完全描述的时候，可能没有用-->

The WordPress database tables are easily referenced in the wpdb class.

在wpdb class中能够轻易地参考WordPress数据库表格。

$posts

The table of Posts.

$posts

文章表格。

$users

The table of Users.

$users

用户表格。

$comments

The Comments table.

$comments

评论 表格。

$links

The table of Links.

$links

The table of 链接表格。

$options

The Options table.

$options

选项表格。

$postmeta

The Meta Content (a.k.a. Custom Fields) table.

$postmeta

Meta 内容 (又名自定义区)表格。

$usermeta

The usermeta table contains additional user information, such as nicknames, descriptions and permissions.

$usermeta

usermeta表格包含有用户的额外信息，例如昵称，描述，和权限。

$terms

The terms table contains the 'description' of Categories, Link Categories, Tags.

$terms

术语表格包含有类别，链接类别，标签的'描述'。

$term_taxonomy

The term_taxonomy table describes the various taxonomies (classes of terms). Categories, Link Categories, and Tags are taxonomies.

$term_taxonomy

term_taxonomy表格描述了不同的分类法（术语的分类）。类别，链接类别，和标签都是分类的。

$term_relationships

The term relationships table contains link between the term and the object that uses that term, meaning this file point to each Category used for each Post.

$term_relationships

The 术语关系表格包含术语和使用这个术语的object之间的关系，指的是这个文件指向每篇文章所使用的类别。