WordPress Database Series: How to interact with WordPress databases

This is the 9th day of my participation in Gwen Challenge

How To Interact With The WordPress Database How To Interact With The WordPress Database

WPDB — WordPress Database Access Abstraction class

WPDB is a wordpress database access abstract class, through which you can directly interact with MySQL data, query any table and process returned data, and achieve more flexible data operations.

It is built into WordPress and does not require opening a separate database connection or modifying a dataset after a query.

$WPDB is built on top of the ezSQL class

When you use $WPDB in function, you need to declare it through global. Can be used directly in themes/files

Begin to use

The basic use of WPDB can be better understood by looking directly at examples. Query the ID and title of posts with a maximum of 4 comments in the database, and sort the comments in descending order.

      
   $posts = $wpdb->get_results("SELECT ID, post_title FROM $wpdb->posts WHERE post_status = 'publish'
   AND post_type='post' ORDER BY comment_count DESC LIMIT 0,4")
? >
Copy the code

This is a basic SQL query wrapped in PHP. The $WPDB class contains a method called get_Results () (method is the specific name of the functions inside the class) that not only takes the result, but also puts it into an appropriate object.

Note that $WPDB ->posts is used instead of wp_posts. It is a helper that accesses your core WordPress tables.

The $Results object will contain your data in the following format

Array([0] = >stdClass Object
      (
         [ID] => 6
         [post_title] => The Male Angler Fish Gets Completely Screwed
      )

   [1] = >stdClass Object
      (
         [ID] => 25
         [post_title] => 10 Truly Amazing Icon Sets From Germany
      )

   [2] = >stdClass Object
      (
         [ID] => 37
         [post_title] => Elderberry Is Awesome
      )

   [3] = >stdClass Object
      (
         [ID] => 60
         [post_title] => Gathering Resources and Inspiration With Evernote
      )

)
Copy the code

Retrieve results from the database

If you want to retrieve some information from a database, you can use the following four helper functions to structure data,

GET_RESULTS()

The best use of GET_RESULTS() is to take two-dimensional data (multiple rows and columns), which translates the data into an array of independent objects for each row,

      
   $posts = $wpdb->get_results("SELECT ID, post_title FROM wp_posts WHERE post_status =' future' AND post_type='post' ORDER BY post_date ASC LIMIT 0,4")

   // Echo the title of the first scheduled post
   echo $posts[0]->post_title;
? >
Copy the code

Get_results is used to SELECT a generic result.

GET_ROW() — Query a row

Get_row () can be used when you need to find an individual row in the database, such as for the post with the most comments. It populates data into a one-dimensional object

      
   $posts = $wpdb->get_row("SELECT ID, post_title FROM wp_posts WHERE post_status = 'publish'
   AND post_type='post' ORDER BY comment_count DESC LIMIT 0,1")

   // Echo the title of the most commented post
   echo $posts->post_title;
? >
Copy the code

GET_COL()

Get_col () is similar to get_row(), except that it takes a single column. Get_row () is great if you want to just get the ids of the top 10 posts with the most comments. Similarly, store data to one-dimensional objects

      
   $posts = $wpdb->get_col("SELECT ID FROM wp_posts WHERE post_status = 'publish'
   AND post_type='post' ORDER BY comment_count DESC LIMIT 0,10")

   // Echo the ID of the 4th most commented post
   echo $posts[3]->ID;
? >
Copy the code

GET_VAR()

In many cases, you just need to fetch a value from the database. For example, the email address of a user, you can use get_var to retrieve a single value. The data type of the value will be the same as its type in the database (that is, integer is integer and string is string)

      
   $email = $wpdb->get_var("SELECT user_email FROM wp_users WHERE user_login = 'danielpataki' ")

   // Echo the user's email address
   echo $email;
? >
Copy the code

INSERT- Inserts a single row

You can perform an insert with insert

$wpdb->insert( $table.$data.$format);
Copy the code

The insert method takes three parameters. The first parameter is the name of the table to which the data is to be inserted. The second is an array of columns and corresponding values (key-value pairs); The third parameter is the data type corresponding to the order of the given values.

Return false on failure; Returns the number of affected rows successfully

      
   $wpdb->insert($wpdb->usermeta, array("user_id"= >1."meta_key"= >"awesome_factor"."meta_value"= >10), array("%d"."%s"."%d"));

   // Equivalent to:
   // INSERT INTO wp_usermeta (user_id, meta_key, meta_value) VALUES (1, "awesome_factor", 10);
? >
Copy the code

If used to perform inserts, it may look awkward at first; But actually INSERT () gives you a lot of flexibility because it uses arrays as input.

Specifying the format is optional: by default, all values are treated as strings. It is a good practice to include this parameter in a method. Three values you can use: %s for string, %d for integer decimal number, and %f for float

The ID generated by the autoincrement column can be obtained using $WPDB ->insert_id after the insert method

UPDATE- Updates data

The auxiliary method for updating data is update(), which is similar to the above usage. But in order to process the updated WHERE clause, it requires two additional parameters.

$wpdb->update( $table.$data.$where.$format = null.$where_format = null );
Copy the code

The $table, $data, and $format parameters are the same as insert. The $WHERE parameter is an array of column-values. Specify multiple parameters that will be logically connected by AND. $where_format is similar to $format: Specifies the format of the value of the $WHERE parameter.

Return false on failure; Returns the number of affected rows successfully.

$wpdb->update( $wpdb->posts, array("post_title"= >"Modified Post Title"), array("ID"= >5), array("%s"), array("%d"));Copy the code

DELETE- Deletes data

Delete () is used to delete rows from a table. Successfully returns the number of affected rows; Failure (error) returns false

delete( $table.$where.$where_format = null );
Copy the code

The parameters are similar to update

// Default usage.
$wpdb->delete( 'table'.array( 'ID'= >1));// Using where formatting.
$wpdb->delete( 'table'.array( 'ID'= >1 ), array( '%d'));Copy the code

Other Enquiries (General QueriesGeneric query)

The helper above is powerful, but sometimes you need to perform different or more complex queries. For example, an update to a complex WHERE clause with multiple AND/OR logic cannot be performed using the update() method.

You can use the “general” query() method, which can perform any form of query.

query('query');
Copy the code

For SELECT, INSERT, DELETE, UPDATE, and other queries, query() returns an integer value indicating the number of rows affected; Query () returns TRUE on success for SQL statements that affect the table, such as CREATE, ALTER, TRUNCATE, and DROP. Return FALSE in error.

Such as deleting rows with multiple conditions

$wpdb->query($wpdb->prepare("DELETE FROM wp_usermeta WHERE meta_key = 'first_login' OR meta_key = 'security_key' "));
$wpdb->query( 
    $wpdb->prepare( 
        "DELETE FROM $wpdb->postmeta
            WHERE post_id = %d
            AND meta_key = %s
        ".13.'gargle'));Copy the code

Protection and verification

You need to first understand the importance of data security and database tampering. For Data Validation, see Data Validation in WordPress Codex

For extra validation, you need to escape all queries.

All methods that write raw SQL, such as query(), get_var(), get_results() and other unaided methods, need to be escaped manually using the prepare() method.

$sql = $wpdb->prepare( 'query' [, value_parameter, value_parameter ... ] );
Copy the code

The following example is given in the basic format for better understanding

$sql = $wpdb->prepare( "INSERT INTO $wpdb->postmeta (post_id, meta_key, meta_value ) VALUES ( %d, %s, %d )".3342.'post_views'.2290 )
$wpdb->query($sql);
Copy the code

Enter the data type and then add the actual data as a subsequent parameter, rather than adding the actual value directly as usual.

Class variables and other methods

Aside from the good ones, there are a few other functions and variables that can make things easier for you. The full list of function variables can be found in the official documentation.

INSERT_ID

Whenever something is inserted into a table, it is likely to have an incremented ID in it. To find the most recent value for an insert, use $WPDB -> insert_id.

$sql = $wpdb->prepare( "INSERT INTO $wpdb->postmeta (post_id, meta_key, meta_value ) VALUES ( %d, %s, %d )".3342.'post_views'.2290 )
$wpdb->query($sql);

$meta_id = $wpdb->insert_id;
Copy the code

NUM_ROWS

Num_rows The number of rows returned by the most recent query. The total number of rows returned by the SELECT query

The name of the table

The table names of all core tables are stored in variables with exactly the same name as their corresponding core tables. The name of the posts table (possibly wP_posts) is stored in the $posts variable, so you can output it using $WPDB ->posts.

It is used because WordPress tables allow you to select a prefix. Most people use the default WP, some modify it. Prefixes cannot be hard-coded for flexibility. Therefore, if you write a plug-in that uses wp_postmeta in queries instead of $WPDB ->postmeta, your code may not work on some sites.

If you want to get data from a WordPress non-core table, you can use the table name directly as usual

Error handling

By calling the show_errors() or hide_errors() methods, you can turn on (on) or off (off) error reporting, which defaults to off, for more information.

Either way, you can use the print_error() method to print the error for the latest query.

$wpdb->show_errors();
$wpdb->query("DELETE FROM wp_posts WHERE post_id = 554 ");

// When run, because show_errors() is set, the error message will tell you that the "post_id" field is an unknown
// field in this table (since the correct field is ID)
Copy the code

Show_errors () is used only to report errors. $result===false and print_error()

Clear the cache

Flush Clears the SQL result cache.

       
$WPDB ->last_result, $WPDB ->last_query, and $WPDB ->col_info
$wpdb->flush();
? >
Copy the code

Get column information

Get_col_info (‘type’, offset) obtains the column information of the latest query result. Useful when a function returns an object with unknown attributes

use$WpdbNew knowledge builds some basic trace

If you’re new to this, you probably understand the content, but find it difficult to implement. So, let’s take a simple WordPress tracking plugin that Daniel Pataki created for a website as an example.

For simplicity, the details of the plug-in are not described below, but the structure of the database and some queries are shown.

Table’s structure

To track the number of clicks and displays on an AD, the authors created a table tracking. This table records user operations in real time. Each display and click is recorded in its own line with the following structure:

• ID the ID.
• Time The date and time of the action.
• Deal_id The deal ID associated with the action (that is, the AD being clicked or viewed).
• Action Type of action (click or show).
• Action_url Opens the action page.
• User_id If ID of the login user.
• User_ip IP of the user, used to clear any naughty behavior.

This table can quickly become large, so it is summarized into daily statistics and refreshed regularly. But, for now, let’s use this table.

Insert data into a table

When the user clicks on the AD, it detects it and sends the information we need to the script in the form’s $_POST array, which contains the following data:

Array
(
   [deal_id] => 643
   [action] => click
   [action_url] => http://thisiswhereitwasclicked.com/about/
   [user_id] => 223
   [user_ip] = 123.234.223.12
)
Copy the code

We can use our helper methods to insert this data into the database as follows:

$wpdb->insert('tracking'.array("deal_id"= >643."action"= >"click"."action_url"= >"http://thisiswhereitwasclicked.com/about/"."user_id"= >223."user_ip"= >"123.234.223.12"), array("%d", %s","%s","%d","%s"));
Copy the code

As a digression, I’ll address some of the problems you might have with this particular example. What about data validation, you might be wondering? The click may have come from the webmaster, or the user may have clicked twice by mistake, or many other things may have happened.

We decided that since we didn’t need real-time statistics (daily statistics would suffice), there was no need to check the data every time we inserted. The data is aggregated into a new table around midnight (low traffic time) each day. Before summarizing the data, we carefully clean up the data, remove duplicate data, and so on. Of course, since we are using helper functions, the data is escaped before being inserted into the table, so it is safe here.

It is easier to batch delete everything the administrator has done at one time than to check every time you insert. This relieves a lot of processing from the server.

Delete an ACTION from a blacklist IP

If we find that IP address 168.211.23.43 is very, very naughty, we can block it. At this point, when we aggregate daily data, we need to delete all entries for that IP

$sql = $wpdb->prepare("DELETE FROM tracking WHERE user_ip = %s ".'168.211.23.43');
$wpdb->query($sql);
Copy the code

You may have noticed that EVEN though THE IP was received from a secure source, I was still escaping the data. I recommend escaping the data anyway. First, proper hackers are good at what they do because they are good programmers and can outdo you in ways you wouldn’t expect. In addition, I personally do more to harm my own site than hackers do, so TAKE these measures as a safety precaution for myself.

Update total data

We store ads as custom POST types; To simplify our statistical reporting, we also store the total number of clicks received by our ads separately. We can add up the sum of all clicks for a given deal in the Tracking database, so let’s take a look first.

$total = $wpdb->get_var("SELECT COUNT(ID) WHERE deal_id = 125 ");
Copy the code

Because it is much easier to get a single variable than to always burden ourselves with a more complex query, we will store the current total separately whenever we aggregate the data. Our ads are stored as posts with a custom post type, so it’s a logical place to store this total in the PostMeta table. Let’s use the Total_CLICKS key to store this data.

$wpdb->update( $wpdb->postmeta, array("meta_value"= >$total), array("ID"= >125), array("%d"), array("%d"));// note that this should be done with update_post_meta(), I just did it the way I did for example's sake
Copy the code

Final thoughts and hints

Hopefully, you have a better understanding of WordPress’s $WPDB class and can use it to make your project better. To sum up, here are some final tips and tricks for using this class effectively:

• Try to be cautious: great power comes with great responsibility. Be sure to escape data and validate, as misuse of this class can be a major cause of a site being hacked!
• Look up only the data you need. If only the article title is displayed, there is no need to retrieve all the data from every row. In this case, just ask for the title and ID:

SELECT title, ID FROM wp_posts ORDER BY post_date DESC LIMIT 0.5;
Copy the code

• Although it can be used for any queryquery()Method, but if possible, it is best to use auxiliary methods (helper methods:insert,update,get_row, etc.). They are more modular and secure because they automatically escape your data.
• Be careful when deleting records from a WordPress(or any other) database. When WordPress deletecommentThe comment count in the WP_posts table needs to be reduced by one, all data in the COMMENT_meta table needs to be deleted, and so on. Make sure you clean up properly after yourself, especially when deleting.
• View all class variables and other information in the official documentation. These will help you take full advantage of the class’s potential. I also recommend looking at ezSQL classes in non-wordpress projects for general use; I use it for almost everything.