Introduction to using SQL query in wordpress

First, I want to use some methods under this class and cannot directly call them. WordPress provides the global variable $ wpdb for this class, which is used to associate with the WordPress Database. Therefore, we need to define this global variable before using it.

The code is as follows:	Copy code
<? Php global $ wpdb;?>

Execute database query
Use the query function to execute any SQL query in the WordPress database. SELECT query is recommended.

The code is as follows:	Copy code
<? Php $ wpdb-> query ('query');?>

 

Delete an article with the ID of 13

The code is as follows:	Copy code
$ Wpdb-> query ("delete from $ wpdb-> posts WHERE post_id = '13 '");

Select a variable

The get_var function returns a variable from the database. Although only one variable is returned, if no query result is returned, NULL is returned.

The code is as follows:	Copy code
<? Php $ wpdb-> get_var ('query', column_offset, row_offset);?>

Query

(String) the query you want to execute. If this parameter is set to null, the function returns the specific variable in the previous query cache result.

Column_offset

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

Row_offset

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

Example

Number of retrieved and returned users

 

The code is as follows:	Copy code
<? Php $ User_count = $ wpdb-> get_var ($ wpdb-> prepare ("select count (*) FROM $ wpdb-> users ;")); Echo '<p> A total of'. $ user_count. 'users </p> '; ?>

Select table row

You can use the get_row function. This function can return rows as objects, associated arrays, or numerical index arrays. If the query returns multiple rows, the function returns only the first row.

 

The code is as follows:	Copy code
<? Php $ wpdb-> get_row ('query', output_type, row_offset);?>

Query

(String) the query statement you want to execute.

Output_type

One of the three predefined constants. The default value is OBJECT. OBJECT -- returned results are output in the form of objects, ARRAY_A -- returned results are output in the form of an associated array, and ARRAY_N -- returned results are output in the form of a numerical index array.

Row_offset

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

Example

Obtain all the information of the link with ID 10.

 

The code is as follows:

Copy code

<? Php $ Mylink = $ wpdb-> get_row ("SELECT * FROM $ wpdb-> links WHERE link_id = 10 "); // $ Attributes of the mylink object are the row names of the SQL query results (that is, all rows in the $ wpdb-> links table ). Echo $ mylink-> link_id; // prints "10" // Use ARRAY_A $ Mylink = $ wpdb-> get_row ("SELECT * FROM $ wpdb-> links WHERE link_id = 10", ARRAY_A ); // An associated array is generated: Echo $ mylink ['link _ id']; // prints "10" // Use ARRAY_N $ Mylink = $ wpdb-> get_row ("SELECT * FROM $ wpdb-> links WHERE link_id = 10", ARRAY_N ); // A numeric index array is generated: Echo $ mylink [1]; // prints "10" ?>

Select table columns

To select a column in the database table, use the get_col function. This function outputs a space array. If the query returns multiple columns.

The code is as follows:	Copy code
<? Php $ wpdb-> get_col ('query', column_offset);?>

Query

(String) the query you want to execute. If this parameter is set to null, the function returns the execution table column in the cache result of the previous query.

Column_offset

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

Example

Returns the ID of the specified document type.

The code is as follows:	Copy code
<? Php $ Resaults = $ wpdb-> get_col ("SELECT * FROM $ wpdb-> posts WHERE post_type = 'question '"); Print_r ($ resaults ); ?>

Select the generated result

Get_results can extract multiple row results generated by functions from the database. The wpdb function returns the entire query result in an array.

The code is as follows:	Copy code
<? Php $ wpdb-> get_results ('query', output_type);?>

Query

(String) the query statement you want to execute. If this parameter is set to null, the function returns the information in the cache result of the previous query.

Output_type

One of the three predefined constants. The default value is OBJECT. For more information, see "select table rows" in the preceding section ". OBJECT -- output the returned results in the form of an OBJECT, ARRAY_A -- output the returned results in the form of an associated array, and ARRAY_N -- output the returned results in the form of a numerical index array

Example

Returns the content of all specified articles.

The code is as follows:	Copy code
<? Php $ Resaults = $ wpdb-> get_results ("SELECT * FROM $ wpdb-> posts WHERE post_type = 'question '"); Print_r ($ resaults ); ?>

The above section is the query part of the WordPress database interface. Through the method provided above, we can use SQL statements to query and obtain the database content, but you need to be familiar with SQL.

Commonly used SQL statements in wordpress

Enable all comments

The code is as follows:	Copy code
UPDATE wp_posts SET comment_status = 'open' WHERE post_status = 'Publish ';

Disable messages from old articles

The code is as follows:	Copy code
UPDATE wp_posts SET comment_status = 'closed' WHERE post_date <'2014-01-01 'AND post_status = 'Publish'

Delete all spam comments

The code is as follows:	Copy code
Delete from wp_comments WHERE comment_approved = 'spam'

Delete all article Revisions (Revisions) and their Meta data

The code is as follows:	Copy code
DELETE a, B, c FROM wp_posts Left join wp_term_relationships B ON (a. ID = B. object_id) Left join wp_postmeta c ON (a. ID = c. post_id) WHERE a. post_type = 'Revision'

Clear the wp_postmeta table

The code is as follows:	Copy code
Delete from wp_postmeta WHERE meta_key = '_ wp_old_slug '; Delete from wp_postmeta WHERE meta_key = '_ revision-control '; Delete from wp_postmeta WHERE meta_value = '{unknown }}';

Write a shell script and add table optimization. You can use crontab to call and process it regularly.