编辑“Function Reference/wpdb Class”

== Interfacing With the Database ==

WordPress provides a class of functions for all database manipulations.  The class is called <code>wpdb</code> and is based on the [http://www.woyano.com/jv/ezsql ezSQL] class written and maintained by [http://www.jvmultimedia.com Justin Vincent].  Though the WordPress class is slightly different than the ezSQL class, their use is essentially the same.  Please see the [http://justinvincent.com/docs/ezsql/ez_sql_help.htm ezSQL documentation] for more information.

=== Note On Use ===

Within PHP code blocks in a [[WordPress:Templates|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 [[WordPress:Writing_a_Plugin#User_Interfaces|Writing a Plugin]] documentation.

== query - Run Any Query on the Database ==

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

%%% <?php $wpdb->query('query'); ?> %%%
; query : (string) The query you wish to run.

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 <code>wpdb</code> 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 <code>==</code> vs. identicality <code>===</code>).

Note: It is advisable to use the <code>wpdb->escape($user_entered_data_string)</code> 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.

'''Additionally''', if you wish to access the database from your code file which is not placed inside one of the [http://codex.wordpress.org/Writing_a_Plugin#Names.2C_Files.2C_and_Locations standard plugin locations], you will need to <code>include_once()</code> the <code>wp-db.php</code> file ''as well as'' the <code>wp-config.php</code> file. Including only the <code>wp-db.php</code> file will not set the database connection information resulting in an error message  like "Wordpress could not connect to the database". 
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 :
<pre>
include_once('wp-config.php');
include_once('wp-includes/wp-db.php');
</pre>

=== Examples ===
Add Post 13 to Category 2:
<pre>
$wpdb->query("
	INSERT INTO $wpdb->post2cat (post_id, category_id)
	VALUES (13, 2)");
</pre>


Delete the 'gargle' meta key and value from Post 13.
<pre>
$wpdb->query("
	DELETE FROM $wpdb->postmeta WHERE post_id = '13'
	AND meta_key = 'gargle'");
</pre>
''Performed in WordPress by <code>[[WordPress:Function Reference/delete post meta|delete_post_meta()]]</code>.''


Set the parent of [[WordPress:Pages|Page]] 15 to Page 7.
<pre>
$wpdb->query("
	UPDATE $wpdb->posts SET post_parent = 7
	WHERE ID = 15 AND post_status = 'static'");
</pre>

== get_var - SELECT a Variable ==

The <code>get_var</code> 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 <tt>NULL</tt> 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 <code>null</code> 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 the name of Category 4.
<pre>
$name = $wpdb->get_var("SELECT cat_name FROM $wpdb->categories WHERE cat_ID=4");
echo $name;
</pre>

Retrieve and display the number of users.
<pre>
<?php
$user_count = $wpdb->get_var("SELECT COUNT(*) FROM $wpdb->users;");?>
<p><?php echo 'user count is ' . $user_count; ?></p>
</pre>

== get_row - SELECT a Row ==

To retrieve an entire row from a query, use <code>get_row</code>.  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.

%%% <?php $wpdb->get_row('query', output_type, row_offset); ?> %%%
; query : (string) The query you wish to run.  Setting this parameter to <code>null</code> will return the specified row from the cached results of the previous query.
; 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 numbered array.
; row_offset : (integer) The desired row ('''0''' being the first).  Defaults to '''0'''.

=== Examples ===
Get all the information about Link 10.
<pre>
$mylink = $wpdb->get_row("SELECT * FROM $wpdb->links WHERE link_id = 10");
</pre>
The properties of the <code>$mylink</code> object are the column names of the result from the SQL query (in this all of the columns from the <code>$wpdb->links</code> 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 numbered array:
 echo $mylink[1]; // prints "10"

== get_col - SELECT a Column ==

To SELECT a column, use <code>get_col</code>.  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); ?> %%%
; query : (string) the query you wish to execute.  Setting this parameter to <code>null</code> 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 ===
Get all the Categories to which Post 103 belongs.
<pre>
$postcats = $wpdb->get_col("SELECT category_id
	FROM $wpdb->post2cat
	WHERE post_id = 103
	ORDER BY category_id");
</pre>
''Performed in WordPress by <code>[[WordPress:Function Reference/wp get post cats|wp_get_post_cats()]]</code>.''

== get_results - SELECT Generic Results ==

Generic, mulitple row results can be pulled from the database with <code>get_results</code>.  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 <code>get_row</code> can be an object, an associative array, or a numbered array.

%%% <?php $wpdb->get_results('query', output_type); ?> %%%
; query : (string) The query you wish to run.  Setting this parameter to <code>null</code> 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.
:* 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.

=== Examples ===
Get the IDs and Titles of all the Drafts by User 5 and echo the Titles.
<pre>
$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;
}
</pre>

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)
 
<pre>
<?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; ?>
</pre>

== escape - Escape For SQL Queries ==

If you're making a SQL query, make sure any untrusted data is escaped properly first.  This can be conveniently done with the <code>escape</code> method.

Note that values taken from <code>$_GET</code>, <code>$_POST</code>, <code>$_REQUEST</code>, <code>$_COOKIE</code> and <code>$_SERVER</code> ''will already be escaped'', regardless of the server's <code>magic_quotes</code> setting.

%%% <?php $safe_string = $wpdb->escape($unsafe_string); ?> %%%
; $unsafe_string : (string)  The string of text to be escaped for proper insertion.

Note that you may need to use PHP's <code>stripslashes()</code> when loading the escaped data back into WordPress.

===Examples===
Add Meta key => value pair "Harriet's Adages" => "WordPress' database interface is like Sunday Morning: Easy." to Post 10.
<pre>
$metakey = $wpdb->escape("Harriet's Adages");
$metavalue = $wpdb->escape("WordPress' database interface is like Sunday Morning: Easy.");

$wpdb->query("
	INSERT INTO $wpdb->postmeta
	(post_id,meta_key,meta_value)
	VALUES ('10','$metakey','$metavalue')");
</pre>
''Performed in WordPress by <code>add_meta()</code>.''

== show/hide_errors - Show and Hide SQL Errors ==

You can turn error echoing on and off with the <code>show_errors</code> and <code>hide_errors</code>, respectively.

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

You can also print the error (if any) generated by the most recent query with <code>print_error</code>.

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

== get_col_info - Getting Column Information ==
You can retrieve information about the columns of the most recent query result with <code>get_col_info</code>.  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); ?> %%%
; type : (string) What information you wish to retrieve.  May take on any of the following values (list taken from the [http://justinvincent.com/home/docs/ezsql/ez_sql_help.htm ezSQL docs]).  Defaults to '''name'''.
:*name - column name.  Default.
:*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
:*unique_key - 1 if the column is a unique key
:*multiple_key - 1 if the column is a non-unique key
:*numeric - 1 if the column is numeric
:*blob - 1 if the column is a BLOB
:*type - the type of the column
:*unsigned - 1 if the column is unsigned
:*zerofill - 1 if the column is zero-filled
; offset : (integer) Specify the column from which to retrieve information (with '''0''' being the first column).  Defaults to '''-1'''.
:*-1 - Retrieve information from all columns.  Output as array.  Default.
:*Non-negative integer - Retrieve information from specified column ('''0''' being the first).

== flush - Clearing the Cache ==

You can clear the SQL result cache with <code>flush</code>.

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

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

== Class Variables ==
; $show_errors : Whether or not [[WordPress:#Showing and Hiding SQL Errors|Error echoing]] is turned on.  Defaults to TRUE.
; $num_queries : The number of queries that have been executed.
; $last_query : The most recent query to have been executed.
; $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.
; $last_results : The most recent query results.
; $col_info : The column information for the most recent query results.  See [[WordPress:#Getting Column Information|Getting Column Information]].
; $insert_id : ID generated for an AUTO_INCREMENT column by the most recent INSERT query.

== Tables ==
<!-- will likely not be useful when the database structure is fully described -->
The WordPress database tables are easily referenced in the <code>wpdb</code> class.
; $posts : The table of [[WordPress:Database Description#Table:_wp_posts|Posts]].
; $users : The table of [[WordPress:Database Description#Table:_wp_users|Users]].
; $comments : The [[WordPress:Database Description#Table:_wp_comments|Comments]] table.
; $links : The table of [[WordPress:Database Description#Table:_wp_links|Links]].
; $options : The [[WordPress:Database Description#Table:_wp_options|Options]] table.
; $postmeta : The [[WordPress:Database Description#Table:_wp_postmeta|Meta Content]] (a.k.a. [[WordPress:Using Custom Fields|Custom Fields]]) table.
; $usermeta : The [[WordPress:Database Description#Table:_wp_usermeta|usermeta]] table contains additional user information, such as nicknames, descriptions and permissions.
; $terms : The [[WordPress:Database Description#Table:_wp_terms|terms]] table contains the 'description' of Categories, Link Categories, Tags.
; $term_taxonomy : The [[WordPress:Database Description#Table:_wp_term_taxonomy|term_taxonomy]] table describes the various taxonomies (classes of terms).  Categories, Link Categories, and Tags are taxonomies.
; $term_relationships : The [[WordPress:Database Description#Table:_wp_term_relationships|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.
说明	输入内容	输出结果
斜体	''斜体文字''	斜体文字
粗体	'''粗体文字'''	粗体文字
粗斜体	'''''粗斜体文字'''''	*粗斜体文字*