wordpress 数据库的操作方法-致用二开的小伙伴们

WordPress包含一个操作数据库的类——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查询。当然了，最好能利用如下的特定函数，

query(‘query’); ?>

query
(string) 你需要执行的SQL查询

此函数返回操作/查询的行或列的整数。如果出现了MySQL错误，此函数将返回 FALSE(注意: 因为 0 和 FALSE 都可能被返回, 确保你使用了正确的比较运算符：等于 == vs. 一致 ===)。

注意：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.
示例

删除属于id为13的文章的‘gargle’meta 键和值。

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

在WordPress中由 delete_post_meta()执行.

设置页面 Page 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.

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.

示例

获取并显示用户数量

get_var($wpdb->prepare(“SELECT COUNT(*) FROM $wpdb->users;”));
echo ‘

User count is ‘ . $user_count . ‘

‘;
?>

获取并显示 自定义字段值 的总和.

get_var($wpdb->prepare(“SELECT sum(meta_value) FROM $wpdb->postmeta WHERE meta_key = %s”, $meta_key));
echo ‘

Total miles is ‘.$allmiles . ‘

‘;
?>

选择一行

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.

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.

示例

获取ID为10的链接的全部信息

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

$mylink对象的属性是SQL查询结果的列名（此例中是所有 $wpdb->links表中的列名）。

echo $mylink->link_id; // prints “10”

作为对比, 使用

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

将返回关联数组：

echo $mylink[‘link_id’]; // prints “10”

然后

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

将返回索引数组：

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.

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.

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

” rel=”bookmark” title=”Permanent Link to “>

选择通用结果

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(‘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.
示例

获取用户 5 发布的草稿的id和标题，并显示标题。

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

获取用户 5 的所有草稿信息

get_results(“SELECT * FROM $wpdb->posts
WHERE post_status = ‘draft’ AND post_author = 5”);
if ($fivesdrafts) :
foreach ($fivesdrafts as $post) :
setup_postdata($post);
?>