WordPress 中文文档

出自WordPress Chinese 中文文档

Interfacing With the Database

WordPress provides a class of functions for all database manipulations. The class is called codewpdb/code 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.

Notes On Use

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.

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.

Run Any Query on the Database

The codequery/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 codewpdb/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 codewpdb-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 Protect Queries Against SQL Injection Attacks below.

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 coderequire_once()/code the codewp-load.php/code file as this file will will now require any other files you will need for connecting to the database. If you are going to be using the database connection inside of a function, do not forget to declare code$wpdb/code as global. 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 require_once('wp-load.php');

function some_function() {

   global $wpdb;
   /* your function code would go here */

} /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 codedelete_post_meta()/code.

Set the parent of Page 15 to Page 7. pre $wpdb-query( UPDATE $wpdb-posts SET post_parent = 7 WHERE ID = 15 AND post_status = 'static'); /pre

SELECT a Variable

The codeget_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 ttNULL/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 codenull/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 name FROM $wpdb-terms WHERE term_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

SELECT a Row

To retrieve an entire row from a query, use codeget_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 codenull/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

SELECT a Column

To SELECT a column, use codeget_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 codenull/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 codewp_get_post_cats()/code.

SELECT Generic Results

Generic, mulitple row results can be pulled from the database with codeget_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 codeget_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 codenull/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 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); ?
   h2a 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

Protect Queries Against SQL Injection Attacks

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

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

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

Examples

Add Meta key = value pair Harriet's Adages = WordPress' database interface is like Sunday Morning: Easy. to Post 10. pre $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 ) );

/pre Performed in WordPress by codeadd_meta()/code.

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

Show and Hide SQL Errors

You can turn error echoing on and off with the codeshow_errors/code and codehide_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 codeprint_error/code.

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

Getting Column Information

You can retrieve information about the columns of the most recent query result with codeget_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 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).

Clearing the Cache

You can clear the SQL result cache with codeflush/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 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 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 codewpdb/code class.

$posts 

The table of Posts.

$users 

The table of Users.

$comments 

The Comments table.

$links 

The table of Links.

$options 

The Options table.

$postmeta 

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

$usermeta 

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

$terms 

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

$term_taxonomy 

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

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