0%

使用 WordPress 的wpdb类操作数据库

数据库接口介绍

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。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
Run Any Query on the Database

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

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

query
(string) The SQL query you wish to execute.
The function returns an integer corresponding to the number of rows affected/selected. 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 ===).

Note: 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.

Examples

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

$wpdb->query("
DELETE FROM $wpdb->postmeta WHERE post_id = '13'
AND meta_key = 'gargle'");
Performed in WordPress by delete_post_meta().


Set the parent of Page 15 to Page 7.

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

SELECT a Variable

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.

Examples

Retrieve and display the number of users.

<?php
$user_count = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM $wpdb->users;"));
echo '<p>User count is ' . $user_count . '</p>';
?>
Retrieve and display the sum of a Custom Field value.

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

SELECT a 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 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.

Examples

Get all the information about Link 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"

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 numerically indexed array:

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

SELECT a Column
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.

Examples

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
}
}
?>

SELECT Generic 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.

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

Examples

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

$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;
}
Get all information on the Drafts by User 5.

<?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="Permanent Link to <?php the_title(); ?>"><?php the_title(); ?></a></h2>
<?php
endforeach;
else :
?>
<h2> Not Found</h2>
<?php endif; ?>

INSERT rows
Insert a row into a table.

<?php $wpdb->insert( $table, $data, $format ); ?>

table
(string) The name of the table to insert data into.
data
(array) Data to insert (in column => value pairs). Both $data columns and $data values should be "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
This function returns false if the row could not be inserted.

Examples

Insert two columns in a row, the first value being a string and the second a number:

$wpdb->insert( 'table', array( 'column1' => 'value1', 'column2' => 123 ), array( '%s', '%d' ) )
UPDATE rows 更新记录
更新数据库的记录。

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

范例

Update a row, where the ID is 1, the value in the first column is a string and the value in the second column is a number:

$wpdb->update( 'table', array( 'column1' => 'value1', 'column2' => 'value2' ), array( 'ID' => 1 ), array( '%s', '%d' ), array( '%d' ) )
Protect Queries Against SQL Injection Attacks
For a more complete overview of SQL escaping in WordPress, see database Data Validation. That Data Validation article 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 a sprintf()-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.
Examples

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.

Show and Hide SQL Errors
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(); ?>

Getting Column Information
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 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 flush.

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

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

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 by 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_result
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.
$num_rows
The number of rows returned by the last query.
$prefix
The assigned WordPress table prefix for the site.
Multi-Site Variables

If you are using Multi-Site, you also have access to the following:

$blogid
The id of the current site (blog).
Tables
The WordPress database tables are easily referenced in the wpdb class.

$posts
The table of Posts.
$postmeta
The Meta Content (a.k.a. Custom Fields) table.
$comments
The Comments table.
$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
The table of Users.
$usermeta
The usermeta table contains additional user information, such as nicknames, descriptions and permissions.
$links
The table of Links.
$options
The Options table.

通过上面我们获得的ID,我们可以进行各种数据库的读取

如获得当前post的category 可以使用

1
$category = get_the_category($suID);

$suID为获得的ID。

获得post的链接 可以使用

1
get_post($suID)->guid;

获得上一层category的链接可以使用

1
$catelink = get_category_link($category[0]->cat_ID);

自己制作了新表的情况下直接使用 $wpdb->新表名称,是查找不到的。

需要使用

1
$wpdb->prefix . '新表名称'

才能正确的进行数据库操作。

为WordPress 插件添加数据表

当你写插件的时候,你可能需要写东西到数据库中去,一般来说,有两种数据要存储,第一种是安装数据,一般存储在 options 表中,使用 WordPress 的 options 机制来实现。第二种是数据,可以存储到 postmeta 表或者 usermeta 表中。如果数据特别多,就需要自己创建数据库了。这篇文章就讲讲如何在写插件时候创建数据库。一般在写插件时候创建数据库有如下三个步骤:

创建一个添加新数据表的 PHP 函数。
保证插件启动的时候调用这个插件。
如果新版本有不同的数据表结构,创建一个升级函数。
今天我就结合我以前写的 《WordPress 插件:Google PageRank》 讲解下这个过程,其中的第三步,由于没有数据库的升级,不涉及,如果你要了解这个东东,请参考 Codex 文档:Creating Tables with Plugins,这篇也是很多参考这里的。

创建一个添加新数据表的 PHP 函数
我们这个插件的安装函数叫做:google_pagerank_install。

  1. 数据库表前缀

默认 WordPress 数据库标签前缀都是 wp_,但是有人如果在一个数据库安装两个及以上 WordPress,就需要在 wp-config.php 设置 WordPress 数据库表前缀来区别,所以我们向 WordPress 数据库添加新表的时候,要先找找到数据库表前缀。我们可以在变量 $wpdb->prefix 找到。代码如下:

1
2
3
function google_pagerank_install () {
global $wpdb;
$table_name = $wpdb->prefix . "googlepr";
  1. 判断表是否存在

可以通过一条 SHOW TABLES SQL 查询来判断。

1
if($wpdb->get_var("SHOW TABLES LIKE "$table_name"") != $table_name) {
  1. 创建数据表

这步就是实际来创建一个数据表了。我们这个表有三个字段,post_id 日志的 ID,外键。pr 该日志 ID 对应页面的 PR,time 更新时间。由于 PR 更新不是每天都发生的。所以我们可以设置上次查询 PR 的时间,然后在一段时间间隔内都不去查询,直接在数据库中调用。

这里我不用直接的 SQL 查询来创建,而是使用在 wp-admin/upgrade-functions.php(我们需要导入该文件,默认是不会导入的)中的 dbDelta。dbDelta 函数会检查当前的表结构,和需要的表结构进行比较,所以无论添加或者修改表都会非常方便。关于具体怎样使用 dbDelta,详细可以看 wp-admin/upgrade-schema.php 中的例子。

1
2
3
4
5
6
7
8
9
10
$sql = "CREATE TABLE " . $table_name . " (
post_id bigint(20) NOT NULL,
pr int(2) NOT NULL default "0",
time datetime NOT NULL default "0000-00-00 00:00:00",
UNIQUE KEY post_id (post_id)
);";

require_once(ABSPATH . "wp-admin/includes/upgrade.php");

dbDelta($sql);

最后这个 google_pagerank_install 函数为:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
function google_pagerank_install () {   
global $wpdb;

$table_name = $wpdb->prefix . "googlepr";
if($wpdb->get_var("show tables like "$table_name"") != $table_name) {
$sql = "CREATE TABLE " . $table_name . " (
post_id bigint(20) NOT NULL,
pr int(2) NOT NULL default "0",
time datetime NOT NULL default "0000-00-00 00:00:00",
UNIQUE KEY post_id (post_id)
);";

require_once(ABSPATH . "wp-admin/includes/upgrade.php");

dbDelta($sql);
}
}

保证插件启动的时候调用这个插件
我们使用 register_activation_hook 调用上面创建数据表的函数。

register_activation_hook( FILE,‘google_pagerank_install‘);

参考:Creating Tables with Plugins

最后欢迎大家使用我的 Google PageRank for WordPress,它能够获取每篇日志的 PR 值。并在文章结尾显示。