WordPress Essentials: Interacting With The WordPress Database
来源:互联网 发布:淘宝桃子老师全球购 编辑:程序博客网 时间:2024/05/02 22:27
In this article, I will show you how to get started with the $wpdb
class, how to retrieve data from your WordPress database and how to run more advanced queries that update or delete something in the database. The techniques here will remove some of the constraints that you run into with functions such as get_posts()
andwp_list_categories()
, allowing you to tailor queries to your particular needs. This method can also make your website more efficient by getting only the data that you need — nothing more, nothing less.
Getting Started
If you know how MySQL or similar languages work, then you will be right at home with this class, and you will need to keep only a small number of function names in mind. The basic usage of this class can be best understood through an example, so let’s query our database for the IDs and titles of the four most recent posts, ordered by comment count (in descending order).
1
<?php
2
$posts
=
$wpdb
->get_results("SELECT ID, post_title FROM
$wpdb
->posts WHERE post_status =
'publish'
3
AND post_type=
'post'
ORDER BY comment_count DESC LIMIT 0,4")
4
?>
As you can see, this is a basic SQL query, with some PHP wrapped around it. The$wpdb
class contains a method (a method is a special name for functions that are inside classes), namedget_results()
, which will not only fetch your results but put them in a convenient object. You might have noticed that, instead of usingwp_posts
for the table’s name, I have used $wpdb->posts
, which is a helper to access your core WordPress tables. More on why to use these later.
The $results
object now contains your data in the following format:
01
Array
02
(
03
[0] => stdClass Object
04
(
05
[ID] => 6
06
[post_title] => The Male Angler Fish Gets Completely Screwed
07
)
08
09
[1] => stdClass Object
10
(
11
[ID] => 25
12
[post_title] => 10 Truly Amazing Icon Sets From Germany
13
)
14
15
[2] => stdClass Object
16
(
17
[ID] => 37
18
[post_title] => Elderberry Is Awesome
19
)
20
21
[3] => stdClass Object
22
(
23
[ID] => 60
24
[post_title] => Gathering Resources
and
Inspiration With Evernote
25
)
26
27
)
Retrieving Results From The Database
If you want to retrieve some information from the database, you can use one of four helper functions to structure the data.
get_results()
This is the function that we looked at earlier. It is best for when you need two-dimensional data (multiple rows and columns). It converts the data into an array that contains separate objects for each row.
1
<?php
2
$posts
=
$wpdb
->get_results("SELECT ID, post_title FROM wp_posts WHERE post_status =
'future'
3
AND post_type=
'post'
ORDER BY post_date ASC LIMIT 0,4")
4
5
// Echo the title of the first scheduled post
6
echo
$posts
[0]->post_title;
7
?>
get_row
When you need to find only one particular row in the database (for example, the post with the most comments), you can useget_row()
. It pulls the data into a one-dimensional object.
1
<?php
2
$posts
=
$wpdb
->get_row("SELECT ID, post_title FROM wp_posts WHERE post_status =
'publish'
3
AND post_type=
'post'
ORDER BY comment_count DESC LIMIT 0,1")
4
5
// Echo the title of the most commented post
6
echo
$posts
->post_title;
7
?>
get_col
This method is much the same as get_row()
, but instead of grabbing a single row of results, it gets a single column. This is helpful if you would like to retrieve the IDs of only the top 10 most commented posts. Likeget_row()
, it stores your results in a one-dimensional object.
1
<?php
2
$posts
=
$wpdb
->get_col("SELECT ID FROM wp_posts WHERE post_status =
'publish'
3
AND post_type=
'post'
ORDER BY comment_count DESC LIMIT 0,10")
4
5
// Echo the ID of the 4th most commented post
6
echo
$posts
[3]->ID;
7
?>
get_var
In many cases, you will need only one value from the database; for example, the email address of one of your users. In this case, you can useget_var
to retrieve it as a simple value. The value’s data type will be the same as its type in the database (i.e. integers will be integers, strings will be strings).
1
<?php
2
$email
=
$wpdb
->get_var(
"SELECT user_email FROM wp_users WHERE user_login = 'danielpataki' "
)
3
4
// Echo the user's email address
5
echo
$email
;
6
?>
Inserting Into The Database
To perform an insert, we can use the insert method:
1
$wpdb
->insert(
$table
,
$data
,
$format
);
This method takes three arguments. The first specifies the name of the table into which you are inserting the data. The second argument is an array that contains the columns and their respective values, as key-value pairs. The third parameter specifies the data type of your values, in the order you have given them. Here’s an example:
1
<?php
2
$wpdb
->insert(
$wpdb
->usermeta,
array
(
"user_id"
=> 1,
"meta_key"
=>
"awesome_factor"
,
"meta_value"
=> 10),
array
(
"%d"
, %s
", "
%d"));
3
4
// Equivalent to:
5
// INSERT INTO wp_usermeta (user_id, meta_key, meta_value) VALUES (1, "awesome_factor", 10);
6
?>
If you’re used to writing out your inserts, this may seem unwieldy at first, but it actually gives you a lot of flexibility because it uses arrays as inputs.
Specifying the format is optional; all values are treated as strings by default, but including this in the method is a good practice. The three values you can use are%s
for strings, %d
for decimal numbers and %f
for floats.
Updating Your Data
By now, you won’t be surprised to hear that we also have a helper method to update our data — shockingly, calledupdate()
. Its use resembles what we saw above; but to handle the where
clause of our update, it needs two extra parameters.
1
$wpdb
->update(
$table
,
$data
,
$where
,
$format
= null,
$where_format
= null );
The $table
, $data
and $format
parameters should be familiar to you; they are the same as before. Using the$where
parameter, we can specify the conditions of the update. It should be an array in the form of column-value pairs. If you specify multiple parameters, then they will be joined withAND
logic. The $where_format
is just like $format
: it specifies the format of the values in the$where
parameter.
1
$wpdb
->update(
$wpdb
->posts,
array
(
"post_title"
=>
"Modified Post Title"
),
array
(
"ID"
=> 5),
array
(
"%s"
),
array
(
"%d"
) );
Other Queries
While the helpers above are great, sometimes performing different or more complex queries than the helpers allow is necessary. If you need to perform an update with a complexwhere
clause containing multiple AND
/OR
logic, then you won’t be able to use theupdate()
method. If you wanted to do something like delete a row or set a connection character set, then you would need to use the “general”query()
method, which let’s you perform any sort of query.
1
$wpdb
->query(
"DELETE FROM wp_usermeta WHERE meta_key = 'first_login' OR meta_key = 'security_key' "
);
Protection And Validation
I hope I don’t have to tell you how important it is to make sure that your data is safe and that your database can’t be tampered with! Data validation is a bit beyond the scope of this article, but do take a look at what the WordPress Codex has to say about “Data Validation” at some point.
In addition to validating, you will need to escape all queries. Even if you are not familiar withSQL injection attacks, still use this method and then read up on it later, because it is critical.
The good news is that if you use any of the helper functions, then you don’t need to do anything: the query is escaped for you. If you use thequery()
method, however, you will need to escape manually, using theprepare()
method.
1
$sql
=
$wpdb
->prepare(
'query'
[, value_parameter, value_parameter ... ] );
To make this a bit more digestible, let’s rewrite this basic format a bit.
1
$sql
=
$wpdb
->prepare(
"INSERT INTO $wpdb->postmeta (post_id, meta_key, meta_value ) VALUES ( %d, %s, %d )"
, 3342,
'post_views'
, 2290 )
2
$wpdb
->query(
$sql
);
As you can see, this is not that scary. Instead of adding the actual values where you usually would, you enter the type of data, and then you add the actual data as subsequent parameters.
Class Variables And Other Methods
Apart from these excellent methods, there are quite a few other functions and variables to make your life easier. I’ll show you some of the most common ones, but please do look at the WordPress Codex page linked to above for a full list of everything that$wpdb
has to offer.
insert_id()
Whenever you insert something into a table, you will very likely have an auto-incrementing ID in there. To find the value of the most recent insert performed by your script, you can use$wpdb->insert_id
.
1
$sql
=
$wpdb
->prepare(
"INSERT INTO $wpdb->postmeta (post_id, meta_key, meta_value ) VALUES ( %d, %s, %d )"
, 3342,
'post_views'
, 2290 )
2
$wpdb
->query(
$sql
);
3
4
$meta_id
=
$wpdb
->insert_id;
num_rows()
If you’ve performed a query in your script, then this variable will return the number of results of your last query. This is great for post counts, comment counts and so on.
Table Names
All the core table names are stored in variables whose names are exactly the same as their core table equivalent. The name of your posts table (probablywp_posts
) would be stored in the $posts
variable, so you could output it by using$wpdb->posts
.
We use this because we are allowed to choose a prefix for our WordPress tables. While most people use the defaultwp
, some users want or need a custom one. For the sake of flexibility, this prefix is not hardcoded, so if you are writing a plug-in and usewp_postmeta
in a query instead of $wpdb->postmeta
, your code will not work on some websites.
If you want to get data from a non-core WordPress table, no special variable will be available for it. In this case, you can just write the table’s name as usual.
Error Handling
By calling the show_errors()
or hide_errors()
methods, you can turn error-reporting on or off (it’s off by default) to get some more info about what’s going on. Either way, you can also use theprint_error()
method to print the errors for the latest query.
1
$wpdb
->show_errors();
2
$wpdb
->query(
"DELETE FROM wp_posts WHERE post_id = 554 "
);
3
4
// When run, because show_errors() is set, the error message will tell you that the "post_id" field is an unknown
5
// field in this table (since the correct field is ID)
Building Some Basic Tracking With Our New $wpdb Knowledge
If you’re new to all of this, you probably get what I’m going on about but may be finding it hard to implement. So, let’s take the example of a simple WordPress tracking plug-in that I made for a website.
For simplicity’s sake, I won’t describe every detail of the plug-in. I’ll just show the database’s structure and some queries.
Our Table’s Structure
To keep track of ad clicks and impressions, I created a table; let’s call it “tracking.” This table records user actions in real time. Each impression and click is recorded in its own row in the following structure:
ID
The auto-incremented ID.time
The date and time that the action occurred.deal_id
The ID of the deal that is connected to the action (i.e. the ad that was clicked or viewed).action
The type of action (i.e. click or impression).action_url
The page on which the action was initiated.user_id
If the user is logged in, their ID.user_ip
The IP of the user, used to weed out any naughty business.
This table will get pretty big pretty fast, so it is aggregated into daily statistics and flushed periodically. But let’s just work with this one table for now.
Inserting Data Into Our Tables
When a user clicks an ad, it is detected, and the information that we need is sent to our script in the form of a$_POST
array, with the following data:
1
Array
2
(
3
[deal_id] => 643
4
[action] => click
5
[action_url] => http:
//thisiswhereitwasclicked.com/about/
6
[user_id] => 223
7
[user_ip] = 123.234.223.12
8
)
We can then insert this data into the database using our helper method, like so:
1
$wpdb
->insert(
'tracking'
,
array
(
"deal_id"
=> 643,
"action"
=>
"click"
,
"action_url"
=>
"http://thisiswhereitwasclicked.com/about/"
,
2
"user_id"
=> 223,
"user_ip"
=>
"123.234.223.12"
),
array
(
"%d"
, %s
", "
%s
", "
%d
", "
%s"));
At the risk of going on a tangent, I’ll address some questions you might have about this particular example. You may be thinking, what about data validation? The click could have come from a website administrator, or a user could have clicked twice by mistake, or a bunch of other things might have happened.
We decided that because we don’t need real-time stats (daily stats is enough), there is no point to check the data at every insert. Data is aggregated into a new table every day around midnight, a low traffic time. Before aggregating the data, we take care to clean it up, taking out duplicates and so on. The data is, of course, escaped before being inserted into the table, because we are using a helper function; so, we are safe there.
Just deleting in bulk all at once the ones that are made by administrators is easier than checking at every insert. This takes a considerable amount of processing off our server’s shoulders.
Deleting Actions From a Blacklisted IP
If we find that the IP address 168.211.23.43
is being naughty-naughty, we could blacklist it. In this case, when we aggregate the daily data, we would need to delete all of the entries by this IP.
1
$sql
=
$wpdb
->prepare(
"DELETE FROM tracking WHERE user_ip = %s "
,
'168.211.23.43'
);
2
$wpdb
->query(
$sql
);
You have probably noticed that I am still escaping the data, even though the IP was received from a secure source. I would suggest escaping your data no matter what. First of all, proper hackers are good at what they do, because they are excellent programmers and can outsmart you in ways that you wouldn’t think of. Also, I personally have done more to hurt my own websites than hackers have, so I do these things as a safety precaution against myself as well.
Updating Totals
We store our ads as custom post types; and to make statistical reporting easier, we store the total amount of clicks that an ad receives separately as well. We could just add up all of the clicks in our tracking database for the given deal as well, so let’s look at that first.
1
$total
=
$wpdb
->get_var(
"SELECT COUNT(ID) WHERE deal_id = 125 "
);
Because getting a single variable is easier than always burdening ourselves with a more complex query, whenever we aggregate our data, we would store the current total separately. Our ads are stored as posts with a custom post type, so a logical place to store this total is in the postmeta
table. Let’s use the total_clicks
meta key to store this data.
1
$wpdb
->update(
$wpdb
->postmeta,
array
(
"meta_value"
=>
$total
),
array
(
"ID"
=> 125),
array
(
"%d"
),
array
(
"%d"
) );
2
3
// note that this should be done with update_post_meta(), I just did it the way I did for example's sake
- WordPress Essentials: Interacting With The WordPress Database
- Interacting with the Runtime
- MonkeyRunner_monkeyrunner: interacting with the Views
- MonkeyRunner_monkeyrunner: interacting with the Views
- Access another wordpress database
- Interacting with the User in a Service
- WordPress
- WordPress
- wordpress
- wordpress
- wordpress
- WordPress
- WordPress
- Wordpress
- wordpress
- wordpress
- Wordpress
- wordpress
- iOS开发之NSobject--------sharedInstance
- Android WIFI模块分析
- Discuz! X2 全新安装图文教程
- 浅谈SEO发展的4个方向
- linux的Framebuffer(转)
- WordPress Essentials: Interacting With The WordPress Database
- 成为Java高手的重要过程
- 删除字符串中首次出现的指定字符
- 位和字节
- Google Open API 授权认证体系
- DEDE获取列表循环数字次数
- Delphi语言中的Helper 关键字 和 Objective-C中的类别
- Android开发者必须深入学习的10个应用开源项目
- jni数组使用(一)