Working with Databases in WordPress

WordPress Database Interaction Guide: Master wpdbClass and Database Operation Skills

WordPress comes with a large number of database interaction functions. WP_Query classes and wp_insert_post, update_post_meta, get_posts and other functions are usually enough to cope with most situations. However, especially when dealing with custom tables, we sometimes need to do things that cannot be achieved by WordPress native features.

This tutorial will explore in-depth the most important class in WordPress database interaction - wpdb and share some development tips. We will cover the dbDelta functions used to create custom tables, but will not cover the basics of creating an initial WordPress database.

Key points:

• WordPress provides a variety of database interaction functions, including WP_Query classes and related functions. But for more complex needs, developers can use wpdb classes or create custom tables.
The
• wpdb class is a key tool for directly handling WordPress databases. To avoid hard-code table names in SQL queries, use the prefix attribute provided by WordPress to improve code portability.
The
• wpdb class also provides auxiliary methods for database operations, such as insert, update and get_row, which are safer and more case-specific than the general query method.
• To prevent SQL injection, the wpdb class provides a prepare method that receives SQL statement strings and data that need to be escaped. This is especially important when dealing with methods such as query or get_results.
• Creating custom tables is useful when more granular control of data is required. It is recommended to use the dbDelta function to create custom database tables and process table schema updates. Meanwhile, store the database version in the options table for reference during future updates.

Using wpdbCategory

The

wpdb class is the most commonly used class when processing databases directly. It is based on the ezSQL class written by Justin Vincent and has been adjusted for WordPress. The WordPress manual has explained the basic methods and properties of the wpdb class in detail, and will not be repeated here. We will focus on some of the common mistakes that WordPress developers make, how to correct them, and best practices when using wpdb classes.

Avoid hard-code table names in SQL queries

Some developers assume that the table prefix will not change and use the default value wp_. The following code snippet shows the wrong approach:

global $wpdb;
$result = $wpdb->get_results('SELECT * FROM wp_posts LIMIT 10');

If the user changes the table prefix to another value, this code will error. This problem can be easily solved by using the prefix attribute:

global $wpdb;
$result = $wpdb->get_results('SELECT * FROM wp_posts LIMIT 10');

Better yet, if you are dealing with WordPress default tables, you can access them directly using the properties in wpdb. Each WordPress default table is represented by a property of the same name in the wpdb class (excluding prefixes). For example, suppose the table prefix is ​​wp_:

• $wpdb->postscorresponding to wp_posts table
• $wpdb->postmetacorresponding to wp_postmeta table
• $wpdb->userscorresponding to wp_users table

Therefore, the above code can be further improved:

global $wpdb;
$result = $wpdb->get_results('SELECT * FROM ' . $wpdb->prefix . 'posts LIMIT 10');

Use specific auxiliary methods for database operations

While the query method can handle any SQL query, it is best to use more suitable auxiliary methods, such as insert, update, get_row, etc. These methods are more specific and safer, as escapes and other underlying work are already handled by them.

Correctly debug database query

By default, error reports are turned off. wpdb provides two ways to switch the status of error reports: $wpdb->show_errors(); (on) and $wpdb->hide_errors(); (off). If both WP_DEBUG and WP_DEBUG_DISPLAY are set to true, the show_errors method is automatically called. The $wpdb->print_error(); method will display the errors of the last query regardless of the status of the error report. Enable wp-config.php in SAVEQUERIES to store all database queries, execution time, and call locations into the wpdb attribute of the queries class. This data can be retrieved using print_r( $wpdb->queries );. Note that this affects website performance and is therefore only used if necessary.

Protect queries from potential attacks

To fully protect the code from SQL injection attacks, wpdb also provides a prepare method that receives SQL statement strings and data that need to be escaped. This is very important when dealing with methods such as query or get_results.

global $wpdb;
$result = $wpdb->get_results('SELECT * FROM ' . $wpdb->posts . ' LIMIT 10');

The

prepare method supports two syntaxes: sprintf and vsprintf.

Connect to a separate database

By default, the $wpdb variable is an instance of the wpdb class, connected to the WordPress database defined in wp-config.php. If you want to interact with another database, you can instantiate another wpdb class instance. The constructor of the wpdb class accepts four parameters: username, password, database name, and database host.

$wpdb->prepare( $sql, $format... );

If the username, password and database host are the same, just change the selected database and use the $wpdb method on the select variable.

$mydb = new wpdb( 'username', 'password', 'my_database', 'localhost' );

Use custom database tables

WordPress default tables are usually enough to handle most complex operations. With custom article types, article metadata, custom taxonomy, and term metadata, you can do almost anything without using custom tables. However, custom tables can be useful when more granular control over the data processed by the plugin. The benefits of custom tables include: complete control over data structures, separation of concerns, and efficiency.

UsingdbDelta

When creating a custom database table, it is recommended to use

to handle all initial table creation and table schema updates. The WordPress core also uses this function to handle database schema updates between versions. dbDelta

When updating table schema, you can use the

global $wpdb;
$result = $wpdb->get_results('SELECT * FROM wp_posts LIMIT 10');

action to check the database version and update the table schema if necessary.

plugin_loaded

Conclusion

WordPress is not limited to creating simple websites, it is rapidly evolving into a fully functional application framework. Extending WordPress should prioritize custom post types and custom taxonomy. However, when more granular control of data is needed, functions and classes such as

are very important.

wpdb

(The FAQs part is omitted because it is too long and does not match the pseudo-original goal. The FAQs part can be regenerated as needed.)

The above is the detailed content of Working with Databases in WordPress. For more information, please follow other related articles on the PHP Chinese website!