使用Wordpress中wpdb类操作数据库的实现方法

​​​​​​​$myrows = $wpdb->get_results( "SELECT id, name FROM mytable" );

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

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

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

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

​​​​​​​<?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); ?>
query
(string) The query you wish to run.
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 numerically indexed array.
row_offset
(integer) The desired row (0 being the first). Defaults to 0.

$mylink = $wpdb->get_row("SELECT * FROM $wpdb->links WHERE link_id = 10");

$mylink = $wpdb->get_row("SELECT * FROM $wpdb->links WHERE link_id = 10", ARRAY_A);

$mylink = $wpdb->get_row("SELECT * FROM $wpdb->links WHERE link_id = 10", ARRAY_N);

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); ?>
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.示例
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); ?>
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 four pre-defined constants. Defaults to OBJECT. See SELECT a Row and its examples for more information.
OBJECT - result will be output as a numerically indexed array of row objects.
OBJECT_K - result will be output as an associative array of row objects, using first column's values as keys (duplicates will be discarded).
ARRAY_A - result will be output as an numerically indexed array of associative arrays, using column names as keys.
ARRAY_N - result will be output as a numerically indexed array of numerically indexed arrays.
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.

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

<?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="链接到 <?php the_title(); ?>"><?php the_title(); ?></a></h2>
<?php
endforeach;
else :
?>
<h2> 未找到</h2>
<?php endif; ?>

<?php $wpdb->insert( $table, $data, $format ); ?>
table
(string) 插入数据的数据表名称。
data
(array) 插入的数据 (为 column => value 键值对). $data columns 和 $data values 都可以是 "raw" 数据 (neither should be SQL escaped).
format
(array|string) (optional) An array of formats to be mapped to each of the value in $data. If string, that format will be used for all of the values in $data. If omitted, all values in $data will be treated as strings unless otherwise specified in wpdb::$field_types.
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

$wpdb->insert( 'table', array( 'column1' => 'value1', 'column2' => 123 ), array( '%s', '%d' ) )

<?php $wpdb->update( $table, $data, $where, $format = null, $where_format = null ); ?>
table
(string) 要更新的表名称。
data
(array) 需要更新的数据（使用格式：column => value）。Both $data columns and $data values should be "raw" (neither should be SQL escaped).
where
(array) A named array of WHERE clauses (in column => value pairs). Multiple clauses will be joined with ANDs. Both $where columns and $where values should be "raw".
format
(array|string) (optional) An array of formats to be mapped to each of the values in $data. If string, that format will be used for all of the values in $data.
where_format
(array|string) (optional) An array of formats to be mapped to each of the values in $where. If string, that format will be used for all of the items in $where.
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.

$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 Validationarticle 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 asprintf()-like and vsprintf()-like syntax.
<?php $sql = $wpdb->prepare( 'query' [, value_parameter, value_parameter ... ] ); ?>
query
(string) The SQL query you wish to execute, with %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 %%.
value_parameter
(int|string|array) The value to substitute into the placeholder. Many values may be passed by simply passing more arguments in a sprintf()-like fashion. Alternatively the second argument can be an array containing the values as in PHP's vsprintf() function. Care must be taken not to allow direct user input to this parameter, which would enable array manipulation of any query with multiple placeholders. Values must not already be SQL-escaped.示例
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.显示和隐藏SQL错误
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); ?>
type
(string) What information you wish to retrieve. May take on any of the following values (list taken from theezSQL 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).

$commentmeta
The table contains additional comment information.
$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.
$users

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

$options
The Options table.