使用Wordpress中的wpdb类操作数据库

本文涉及的产品
可视分析地图(DataV-Atlas),3 个项目,100M 存储空间
数据可视化DataV,5个大屏 1个月
简介:

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查询。当然了,最好能利用如下的特定函数,

 <?php $wpdb->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.

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

示例

获取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.

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

示例

获取用户 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 的所有草稿信息

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

如果不能插入行,此函数返回false

示例

在一行中插入两列,第一个值为字符串,第二个为数字:

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

示例

更新ID为1的行,第一列的值为字符串,第二列的值为数组:

$wpdb->update( 'table', array( 'column1' => 'value1', 'column2' => 'value2' ), array( 'ID' => 1 ), array( '%s', '%d' ), array( '%d' ) )

防止SQL查询注入攻击

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

清除缓存

使用 flush 清除SQL查询结果缓存

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

可以清除 $wpdb->last_result$wpdb->last_query, 和 $wpdb->col_info的缓存。

类变量

$show_errors 
是否打开  Error echoing. 默认为 TRUE.
$num_queries 
已执行的查询的数量
$last_query 
已执行的最后一条查询
$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 
最近的查询结果
$col_info 
最新查询结果的列信息. 查阅  获取列信息章节.
$insert_id 
ID自动增长列生成的最近一条插入语句的ID
$num_rows 
最近一个查询返回的行数
$prefix 
       表前缀
$last_error
      错误信息

多站点参数

如果你正在使用多站点, 你也可以访问:

$blogid 
博客ID(多blog环境)

数据表

The WordPress database tables are easily referenced in the wpdb class.

$posts 
文章表
$postmeta 
The  Meta Content (a.k.a.  Custom Fields) table.
$comments 
评论表
$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.



本文转自黄聪博客园博客,原文链接:http://www.cnblogs.com/huangcong/archive/2011/07/12/2104398.html如需转载请自行联系原作者
相关实践学习
Github实时数据分析与可视化
基于Github Archive公开数据集,将项目、行为等20+种事件类型数据实时采集至Hologres进行分析,并搭建可视化大屏。
阿里云实时数仓实战 - 项目介绍及架构设计
课程简介 1)学习搭建一个数据仓库的过程,理解数据在整个数仓架构的从采集、存储、计算、输出、展示的整个业务流程。 2)整个数仓体系完全搭建在阿里云架构上,理解并学会运用各个服务组件,了解各个组件之间如何配合联动。 3&nbsp;)前置知识要求 &nbsp; 课程大纲 第一章&nbsp;了解数据仓库概念 初步了解数据仓库是干什么的 第二章&nbsp;按照企业开发的标准去搭建一个数据仓库 数据仓库的需求是什么 架构 怎么选型怎么购买服务器 第三章&nbsp;数据生成模块 用户形成数据的一个准备 按照企业的标准,准备了十一张用户行为表 方便使用 第四章&nbsp;采集模块的搭建 购买阿里云服务器 安装 JDK 安装 Flume 第五章&nbsp;用户行为数据仓库 严格按照企业的标准开发 第六章&nbsp;搭建业务数仓理论基础和对表的分类同步 第七章&nbsp;业务数仓的搭建&nbsp; 业务行为数仓效果图&nbsp;&nbsp;
相关文章
|
2月前
|
C# 数据库
C# 使用 DbDataReader 来访问数据库
C# 使用 DbDataReader 来访问数据库
63 2
|
4月前
|
关系型数据库 MySQL 数据库
如何快速创建一个数据库
如何快速创建一个数据库
如何快速创建一个数据库
|
10月前
|
SQL 小程序 PHP
laravel5.8(三)数据库相关操作及配置
数据库相关的操作,大概看下laravel封装的增删改查的数据库操作方法以及laravel对原生sql的支持。 一:执行原生sql 1:查询select php 复制代码 $results = DB::select('select * from users where id = ‘{$id}’'); 2:写入 php 复制代码 $result = DB::insert('insert into users (id, name) values (‘{$id}’, ‘{$name}’)'); 3:修改,该方法返回受更新语句影响的行数:
101 1
|
SQL 数据库
数据库的创建
数据库的创建
|
存储 关系型数据库 MySQL
如何访问我的 WordPress 数据库?
您的 WordPress 数据库大部分是自给自足的,普通用户可能永远不需要访问它。例如,您不必经常访问数据库来运行您的网站或创建/修改网站内容。但是,在某些情况下,了解如何访问和理解数据库中的内容非常重要。
如何访问我的 WordPress 数据库?
|
SQL Java 数据库
javaweb操作数据库
javaweb操作数据库
147 0
javaweb操作数据库
|
存储 SQL 关系型数据库
WordPress的数据库介绍
WordPresss数据库是存储所有网站数据的地方。不仅仅是用户名和密码等基本信息,还包括帖子,页面和评论,甚至是网站主题和WordPress配置等设置。
396 0
|
SQL 数据库 数据库管理
数据库——数据库创建(模板与导入)
数据库——数据库创建(模板与导入)
数据库——数据库创建(模板与导入)
|
关系型数据库 MySQL 数据库
mysql数据库(4): 创建并选择数据库
mysql数据库(4): 创建并选择数据库
143 0
mysql数据库(4): 创建并选择数据库
|
关系型数据库 MySQL 数据库
mysql数据库(3):创建并使用数据库
mysql数据库(3):创建并使用数据库
121 0
mysql数据库(3):创建并使用数据库