WordPress Common Function-wpdb class

establishing interfaces with databases

WordPress provides users with a series of function class--wpdb for database operations. The Wpdb class is based on the Ezsql class that Justin Vincent writes and maintains.

Notice of Use

Methods in the Wpdb class should not be called directly, and global variable $wpdb should be used. $WPDB is a global variable provided by WordPress, which is the instantiation of the class that is responsible for communicating with the WordPress database. (Remember to globally $wpdb before using $WPDB in custom functions.) ）

You can use the $wpdb object to read data from any WordPress database table, not just a standard database table. For example, if you need to select some information from a custom table named "MyTable," you can use the following code:

$myrows = $wpdb->get_results ("Select ID, name from mytable");  

$wpdb objects can communicate with any number of database tables, but these database tables can only come from a single database--wordpress database. If you want to connect to a different database (in rare cases), you need to instantiate your object from the Wpdb class with the appropriate connection details. If you need to set up multiple databases, consider using HYPERDB.

Execute database Query

You can use the query function to execute any SQL query in the WordPress database. However, we recommend using more specific functions for select queries.

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

Query

(string) The SQL statement that you want to execute.

The function returns an integer corresponding to the number of rows in the selection. If a MySQL error occurs, the function returns FALSE. (Note: Both 0 and false are likely to be returned, ensuring that the correct comparison operator is used: equality = = vs. Identicality = = =).

Note: When you use all of the functions that execute SQL queries in the Wpdb class, you need to/inputs all of the input into character escapes (such as Wpdb->escape ($user _entered_data_string). See below.

Example

Delete the "gargle" meta keyword and value for the article with ID 13.

$wpdb->query ("
DELETE from $wpdb->postmeta WHERE post_id = ' 13 '
and Meta_key = ' gargle ' ");

Executed by Delete_post_meta.

Set Page 7 as the parent of page 15.

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

Select a variable

The Get_var function returns a variable from the database. Although only one variable is returned, the query results are cached as a whole for later use. If there is no query result, NULL is returned.

<?php $wpdb->get_var (' query ', column_offset,row_offset);?>

Query

(string) The query that you want to execute. Setting this parameter to null causes the function to return a specific variable in the result of the previous query cache.

Column_offset

(integer) The number of columns of the expected database table (0 is the first column in the table). The default value is 0.

Row_offset

(integer) The estimated number of rows in the database table (0 is the first row in the table). The default value is 0.

Example

Retrieves and returns 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> ';
?>

Retrieves and returns the total number of custom field values.

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

To retrieve an entire row of content from a query, use the Get_row function. This function returns the row as an object, an associative array, or an array of numeric indices. If the query returns more than one row, the function returns only the specified row, but all returned rows are cached for later use.

<?php $wpdb->get_row (' query ', Output_type, row_offset);?>

Query

(string) The query statement that you want to execute.

Output_type

One of the three predefined constants. The default value is object.

• object--returned results are output as objects
• The results returned by the array_a--are output in an associative array form
• array_n--The returned result is output as a numeric index array

Row_offset

(integer) The estimated number of rows in the database table (0 is the first row in the table). The default value is 0.

Example

Get all the information for the link with ID 10.

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

The property of the $mylink object is the row name of the SQL query result (in this case, all the rows in the->links table are $wpdb).

Echo $mylink->link_id; Prints "10"  

Use

$mylink = $wpdb->get_row ("select * from $wpdb->links WHERE link_id = ten", array_a);  

An associative array is generated:

echo $mylink [' link_id ']; Prints "10"  

and

$mylink = $wpdb->get_row ("select * from $wpdb->links WHERE link_id = ten", array_n);  

A numeric index array is generated:

echo $mylink [1]; Prints "10"  

Select table Columns

To select a column of content in a database table, you can use the Get_col function. The function outputs an array of spaces, and if the query returns more than one column, the function returns only the specified columns, but all the returned columns are cached for later use.

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

Query

(string) The query that you want to execute. Setting this parameter to null causes the function to return the Execution table column in the cached result of the previous query.

Column_offset

(integer) The number of columns of the expected database table (0 is the first column in the table). The default value is 0.

Example

In this example, we assume that there is a blog focused on publishing car information, and each article in the blog introduces a special car (such as 1969 Ford Mustang). In addition, we assign three custom fields--manufacture, model, and year to each article. Our aim here is to show the title of all articles by manufacturer (manufacturer) for Ford, each of which is arranged by model and year.

You can use the Get_col form of the Wpdb class to return an array of article IDs that meet the requirements and are arranged in the correct order. The article ID array is then repeated through the foreach constructor, showing the title of each article. The SQL query for this example is written 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
}
}
?>

Select the result of the build

Get_results can extract multiple rows of results from a function in a database. The WPDB function returns the entire query result in the form of an array. Each element in the array corresponds to a row in the query result, such as get_row can be either an object or an associative array or a numeric array.

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

Query

(string) The query statement that you want to execute. Setting this parameter to null causes the function to return information from the cached results of the previous query.

Output_type

One of the three predefined constants. The default value is object. For more information, see "Selecting a table row " above.

• object--output returned as an object
• array_a--output of returned results as an associative array
• array_n--output returned as a numeric index array

Example

Gets the ID and title of all article drafts for the user with ID 5 and responds to the title.

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

Gets all the article draft information for the user with ID 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);
?>
Title= "Permanent Link to <?php the_title ();?>" ><?php the_title ();?></a><?php
Endforeach;
else:
?>
<?php endif;?>

Insert table row/Column

Inserts a row/column content into a database table.

Example

Insert two columns of content in a row, the value of the first column is a string, and the value of the second column is a number:

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

Possible values:-%s is a string,-%d is a decimal number,-%f is floating point.

Update table Rows/Columns

Updates the contents of a row/column in a database table.

Example

To update a table row with an ID of 1, the value in the first column of the row 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 ')  

Prevent database queries from being attacked by SQL injection

For more information on avoiding SQL attacks in WordPress, see Data validation. This is a must-read article for WordPress Code writers and plugin developers.

Simply put, all data in an SQL query needs to be escaped by SQL characters before executing SQL queries to prevent SQL injection attacks. You can quickly complete character escapes by using the Prepare method, which uses the sprintf ()-like statement.

<?php $sql = $wpdb->prepare (' query ' [, Value_parameter, Value_parameter ...]); ?>

Query

(string) The SQL query that you want to execute, with%s and%d as placeholders.

Value_parameter

(string) The value that is submitted to the placeholder. This time must not be escaped by SQL.

Example

Add meta key = value pair "Harriet ' s adages" + "WordPress ' Database interface is like Sunday morning:easy." Add to the article with ID 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) ",
, $metakey, $metavalue));

Executed by Add_meta ().

Note that you do not need to worry about referencing strings at this point. Do not pass the variable directly to the SQL query, you can use%s as a placeholder for the string,%d as a placeholder for the integer. You can pass any value, each one as a new parameter to the prepare () method.

Show/Hide SQL errors

You can use show_errors to display SQL errors, or you can use hide_errors to hide SQL errors.

<?php $wpdb->show_errors ();?>
<?php $wpdb->hide_errors ();?>

You can also use Print_error to output errors generated by recent queries.

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

Get a column of information in a database table

You can use Get_col_info to retrieve the table column information for a recent query. When you do not understand the properties of an object returned by a function, you can obtain information through the Get_col_info function. The function outputs the required information from the specified column, and if no column is specified, the function outputs an array that contains information for all the columns in the query results.

<?php $wpdb->get_col_info (' type ', offset);?>

Type

(string) The information you want to retrieve. May be one of the following values (excerpted from the Ezsql documentation). The default value is name.

• The name of the name--table column. Default value
• Table name of the table to which the table--column belongs
• max_length--the maximum length of a table column
• not_null--a value of 1 if the table column is not NULL
• primary_key--If the table column is a primary key, the value is 1
• unique_key--If the table column is a unique key, the value is 1
• multiple_key--If the table column is a non-unique key, the value is 1
• numeric--If the table column is numeric, the value is 1
• blob--If the table column is a blob, the value is 1
• type--Types of table columns
• unsigned--If the table column is unsigned, the value is 1
• zerofill--If the table column is zero-filled, the value is 1

Offset

(integer) Specifies a table column from a table that retrieves information (0 is the first column in the table). The default value is-1.

• -1--retrieves information from all table columns. Outputs an array. The default value.
• nonnegative integer-retrieves information from the specified table column (0 is the first column in the table).

Clear Cache

Clears the SQL results cache with flush.

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

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

class variables

$show _errors

Whether to display the SQL error message. The default is true.

$num _queries

Number of queries that have been executed

$last _query

The last query that has been executed

$queries

Set the Savequeries constant to True (the default value is False) to save all database queries and their stop times. If the Savequeries value is true, your query will be saved as an array in the $queries variable.

$last _result

The results of the most recent query.

$col _info

The table column information for the most recent query result. See Getting table column information.

$insert _id

The ID of the last insert query that was generated for the Auto_increment column.

$num _rows

The number of rows returned by the most recent query.

Table

You can refer to the WordPress database table in the Wpdb class.

$posts

About database tables for articles

$users

About the user's database tables

$comments

Comment Form

$links

Link table

$options

Options table

$postmeta

Meta data (custom fields) table

$usermate

The Usermeta table contains additional information about the user, such as nicknames, personal descriptions, and permissions.

$terms

Terms table includes "description" of categories, link classifications, labels (description)

$term _taxonomy

Term_taxonomy table describes the different categories of WordPress (taxonomy). Categories, link categories, and labels are all categories.

$term _relationships

The Term_relationships table contains a link between the term and the object that uses the term, that is, the table can point to the category directory to which the log belongs.

WordPress Common Function-wpdb class