Working with WordPress Database: Plugin & Development

You can do a lot of things through WordPress without the need for technical knowledge of development or programming. After using WordPress for a while, it’s a good idea to get acquainted with how the WordPress database works, how to get database backup manually and with plugins, how to do optimization and clean up the WordPress database, and how to integrate with WordPress database with PHP code. In other words, how to develop a plugin to work with the WordPress database and have CRUD with the database.

One of the most important parts of dynamic sites is their database. Because all the information needed for the performance of your site is stored in the database.

WordPress is written using PHP as its scripting language and MySQL as its database management system. WordPress database is a deeper area in the system. While you already use many functions in WordPress to communicate with the database, there is an easy and safe way to do this directly, using the $wpdb class.

In this tutorial, we’re going to talk about what a WordPress database is and how it works. Keep in mind that learning this is not necessary for you, but understanding this can help you a lot in troubleshooting and solving potential problems.

What is a Database?

A database is an organized collection of data. Today, this data is stored in a computer system and can be accessed with specific instructions. What all databases have in common is how to store a set of information in a way that is easy to access.

The data is stored in structures called rows, columns, and tables in a file. Databases are dynamic. As long as you have access to it, you can add, delete and modify information in the database. Database data is usually sensitive, and each has its own security measures to keep unauthorized users away.

Why WordPress Uses a Database?

As mentioned, most dynamic websites require a database to interact with users and store the information entered. WordPress also uses a database to store data as a dynamic content management system.

Your WordPress website is much more complex than it looks. Even if your site is relatively small. Specifically, a WordPress website is made up of different types of data. This database is something that allows you to run your site and save changes that you or your visitors make.

Some of the data stored in your WordPress database are:

• Posts, pages, and other content.
• Categories and tags.
• Information and user comments
• Site-level settings
• Plugin and theme data.

There are several ways to use and access a database. WordPress uses MySQL for its database management system. MySQL is open-source software that manages the components of a WordPress database such as user data, meta users, posts, comments, and more.

When you first install WordPress, you will fill in your database name, hostname, username, and password. This information is stored in the configuration file (wp-config.php file).

During installation, WordPress uses the information in your database that you provided by default to create tables and store installation data. Once installed, WordPress runs and generates HTML pages for your website or blog.

Why is it important to have a fast WordPress database?

A quick database gives you several benefits:

• You can make sure your website is running fast.
• This helps to provide a better user experience.
• Improves your chances of getting higher rankings in search engines.
• A fast database usually means that you only use the necessary data, so your servers are less stressed.

Create and build a WordPress MySQL database

When you install WordPress on your host, you also need to create a MySQL database because WordPress works with databases. To create a WordPress database you can use a graphical user interface such as phpMyAdmin. For this purpose, in this section, we intend to provide you with a tutorial on creating and building a database in WordPress.

Creating a WordPress MySQL database in DirectAdmin hosting

To create a database in DirectAdmin hosting, first, log in to your hosting and then click on “MySQL Management” from the Account section.

In the next step, you must click on the “Create new Database” option.

A page will appear asking you to enter information about your database.

• Database Name: In this field, you must enter a name for your database.
• Database Username: Enter a username.
• Username Password: Enter a password.
• Confirm Password: Re-enter your password.
• Create: Finally, click on the Create Database option.

Build a WordPress database in host CPanel

To create a database for WordPress in host CPanel, first, log in to your host control panel and then click on “MySQL Databases”.

Then in the “Create new database” section, you must enter a name for your MySQL database and then click on the “Create database” button.

After a few moments, you will see a green message indicating the creation of the database. Then click on the “Go Back” option at the bottom of the page.

In the “Creating Database User” field, enter your username and password. You can also click the “Password Generator” button to generate a random password for you. Finally, click the “Create User” button.

Then you need to attach the database to the username of the database you created. To do this, scroll down the page and enter the “Add User To Database” section. In this field, enter a username and database that you have already created, and finally, click on the “Add” button.

You will then be taken to a page called “Manage User Privileges”. In this section, you must check all the options. Just click on “All Privileges” to automatically select all options and then click on the “Make Changes” button.

16 Best WordPress database plugins

There are several ways to access, manage, and back up WordPress database data. For example, you can manage your site database using the “phpMyAdmin” tool, however, there are countless database plugins in the WordPress world that you can use to achieve this goal, in this article Here are the 16 best WordPress database plugins.

Old drafts, bad tables, and spam comments only make your database heavier and slow down your site. That’s why you might need a WordPress database plugin!

These plugins offer different functions in the WordPress database system. You can choose the best one for your site and download it.

1- UpdraftPlus WordPress Database Backup plugin

• Backup and restore data in the database with one click
• Save backups in many cloud options: GDrive, Dropbox, Amazon S3, and…
• Allows you to easily set up automatic backups
• Fast operation and low usage do not slow down your site server.

2- VaultPress plugin

• Recover any backup with one click
• Check for suspicious code and threats
• 30-day backup archive
• Easy site migration and one-click auto-retrieval
• Supported by WordPress experts
• Prevent spam from damaging your site

3- BackWPup plugin

• Database and file backup
• WordPress XML Export
• Database review and repair
• Database optimization: splitting and indexing data, resizing tables
• Backup in tar, tar.gz, and zip formats
• Flexible storage on FTP server, Dropbox, S3 service, GDrive…
• Send reports and backups via email

4- Optimize the Database plugin after Deleting Revisions

This plugin is a powerful tool for optimizing information. Some of these operations are:

• This plugin detects and deletes some stubborn databases such as OEMBED cache, database tables, and expired transients.
• Remove items in the trash and spam
• Remove unused tags
• You can schedule database optimization on an hourly, daily, or weekly basis.
• Create report files from optimization operations
• Optimize all network sites (if you have more than one website)
• One-click feature to simplify all operations
• Takes care of public order, such as posts and pages.
• All database optimizations are listed for later reference.

5- WP-DBManager plugin

This is a powerful free plugin for optimizing information. This plugin automatically creates a “Database” menu in your dashboard and you can also easily do customization.

• This is a simple plugin to optimize your database and ensure that an additional plugin does not burden your site.
• The main purpose is the automatic backup of the database and registration of files.
• Display your database profile in great detail
• Supports scheduling Database backup
• Database backup management (you can view the total number of your backups and download it)
• You can optimize tables and files.
• Database repair is performed with the click of a button.
• Execute SQL Query
• You can check the tables in your database and decide whether to keep them or not.
• Admin database information is displayed in the dashboard to see what is wrong with your database.
• All backups of your database are stored in a list so that you can manage and potentially use them in the event of an attack or server problem.

6- Participants Database plugin

WordPress stores data in tables, however, tables only contain WordPress-related data such as comments, posts, and login credentials. This plugin allows you to create a database.

The main features of the Participants Database are:

• The database is fully customizable for any type of information.
• You can manage a lot of information in the dashboard.
• Customizable forms for collecting and maintaining records
• Use the shortcode to insert a short registration form, customizable on WordPress pages, posts, and…
• You will be given a registration form template to start designing.
• Completing the registration form can direct visitors to a thank you or reward message on another page
• Shortcode to display the search form that takes the user to the search results page
• Notify and confirm email with a secure personal access link
• Importing an external database is done with CSV.
• This plugin lets you set validation rules.
• Users have the ability to edit their records from the frontend section of your site.
• The admin can also edit records in the backend.
• This plugin has a growing list of translations.
• Several paid add-ons are available such as a listing tool, member payment, and location mapping.

7- WP Database Reset plugin

• This plugin is light and secure and allows you to do a relatively complex job with the click of a button.
• You have the option to delete the entire WordPress database or get rid of multiple tables.
• There is an option to reset your database using the command line.
• This plugin provides security measures so that you do not accidentally press the reset button.
• Allows you to recover your WordPress environment and clean your database with one click.
• Everything from posts to pages and media input to users can be removed.
• WP Reset has settings for removing specific items from the delete. For example, you may not want to delete all images.
• Choose to leave all your theme and plugin files intact.
• All basic site settings such as WordPress URL and site title remain the same.
• If there is a problem with the deletion, you can create a snapshot of your site to back up and restore your database.

8- Contact Form 7 Database Addon

Contact Form 7 Database Addon is suitable for those who want to automatically enter all their contact form submission information in a database.

• For easy installation, it integrates directly with the Contact Form 7 plugin.
• You do not need to configure any settings. A database is created automatically, which starts recording all the messages sent through the form.
• You have the ability to display all messages created through the form.
• Export all messages and put them in a different application.
• You can integrate with some other powerful add-ons like drag & drop file upload tools and database switches.
• This is a lightweight plugin and it does not cause any problems, it does not slow down your site.

9- WP Database Backup plugin

• This plugin has a one-click database backup button to save all your data before completing the database cleanup.
• Automatic backup is great for protecting your site from attacks or file problems.
• You can download backup files to your computer.
• Several storage destinations are available, including Dropbox and Google Drive.
• Delete tables from the database backup if you wish.
• This plugin has tools for database search.
• Reports are available in your inbox to make sure what happens in the backend.

10- WP-Optimize plugin

• A free version is available for installation on any number of sites you want. It provides excellent tools for site storage, database cleaning, and image compression.
• This plugin automatically optimizes your database with the help of Calendar.
• View database optimization statistics in the plugin.
• Database backup is maintained for several weeks inside the plugin.
• You have options to support multiple sites.
• This plugin allows you to delete unwanted images and optimize the items you want to save.
• You can optimize separate tables instead of the whole database.
• WP-Optimize provides multilingual integrations.

11- Advanced Database Cleaner plugin

• This plugin saves you time by getting rid of junk in the background.
• This cleans your database regularly, creating a faster site and database.
• There is a long list of cleaning options, like Pingbacks, unsolicited data, old auto drafts, and more.
• Schedule when you want automatic cleaning.
• View database information on your page before making a decision.
• This plugin has tools for the automatic cleaning of tables.
• You can repair damaged tables.
• There is multi-site support.

12- Better Search Replace plugin

• This plugin analyzes all database tables before transferring, allowing you to select the tables you want to modify.
• You can update the fields in your tables before transferring.
• Replacing Better Search includes a feature to complete a “dry run” or test before the actual run of the program.
• Supports multi-site installation.
• Search and replace operations with the click of a button. After that, it only takes a few seconds to finish.
• This plugin supports a handful of languages.
• The premium version has priority support and is not expensive.
• You can back up the database when running search and replace tools.
• A database entry can also be done.
• Allows you to save the search and replace the profiles, only if you want to do exactly the same process in the future.

13- WP-Sweep plugin

• This is a clear, simple plugin with one function. You can set what the plugin deletes. There are no other complicated settings to worry about.
• This plugin deletes comments such as spam, deleted, and unverified comments.
• Get rid of metadata such as comments, terms, and user data.
• This plugin works great for locating duplicate content and data. It then removes duplicates.
• WordPress removal functions are used instead of MySQL queries, which can cause performance problems.

14- WP Clean Up Optimizer

• Clean up Optimizer cleans your database by identifying items you no longer need.
• You can get rid of things like repayment, editing, and spam comments.
• There is unparalleled deletion, with options for clearing stored cache, unused terms, and unsolicited term relationships!
• Allows you to manually clean your database or set up automated tools.
• All versions of this plugin can be used in multiple languages.
• Sends these notifications via email based on how the cleaning process works.
• You can see all your cleaning work in one place.
• Login security is provided, along with the maximum number of login attempts.
• This plugin has the ability to preview tables before cleaning. This way, you do not make any mistakes.

15- Smart Cleanup Tools plugin

• The cleaning plugin integrates with many other plugins that work to optimize your database.
• It collects a significant amount of statistical data each time cleaning is performed.
• You can schedule your cleaning or run it by default.
• Some tools are only available for deleting individual items.
• A quick access menu is displayed in the WordPress dashboard so you don’t forget your cleanup.
• Several reset tools are provided to remove items such as sidebars, rules, and plug-in data.
• There is a complete list of removal tools for everything from unverified comments and drafts to post records.
• You can browse the complete list of database cleanup tools such as unsolicited data removal options, and WooCommerce sessions.

16- WP Cleaner Pro plugin

• Your database will be optimized immediately after installing the plugin.
• This plugin also backs up your database to ensure that you can recover your files in case of any problems.
• You have options to schedule all future cleanups. This plugin is optimized for speed, so your site will not be heavy and all the work done will speed up your site.
• It specifically targets unsupervised data, which is often completely useless for site performance.
• Some immediate cleanups for refunds are duplicate content comments.

Understanding WordPress Database Tables and Schema

Each WordPress installation has 12 default tables in the database. Each database table contains data schema for different sections, features, and functionality of WordPress. By looking at the WordPress database structure and schema of these tables, you can easily understand them.

Note: wp_ before each table name is the database prefix you choose during the installation. It can be different if you changed it at the time of installation.

The following diagram contains the general WordPress database schema, WordPress tables, and how they relate to each other.

• wp_commentmeta: This table contains meta information about comments posted on a WordPress website.
• wp_comments: This table contains your WordPress comments. It contains the comment author’s name, URL, email, comment, etc.
• wp_links: To manage blogrolls create by earlier versions of WordPress or the Link Manager plugin.
• wp_options: This table contains most of your WordPress site-wide settings such as site URL, admin email, default category, posts per page, time format, and much much more.
• wp_postmeta: This table contains meta-information about your WordPress posts, pages, and custom post types.
• wp_posts: The name says posts but actually this table contains all post types or should we say content types.
• wp_termmeta: This table allows developers to store custom metadata for terms under their custom taxonomies.
• wp_terms: WordPress has a powerful taxonomy system that allows you to organize your content. Individual taxonomy items are called terms, and they are stored in this table.
• wp_term_relationships: This table manages the relationship of WordPress post types with terms in the wp_terms table.
• wp_term_taxonomy: This table defines taxonomies for terms defined in the wp_terms table.
• wp_usermeta: Contains meta information about registered users on your website.
• wp_users: Contains user information like username, password, user email, etc.

Working with the WordPress database and managing it with PhpMyAdmin

You will sometimes need to access your database, And this access requires sufficient information in this regard. The structure of the database is such that any changes to your WordPress site will be recorded. So you may need it sometimes. With PhpMyAdmin you can make any changes to your WordPress site.

PhpMyAdmin is open-source software that provides a graphical web-based user interface for managing your MySQL database. Most WordPress hosting providers have PhpMyAdmin in their control panel. PhpMyAdmin allows users to easily access the database.

In fact, with PhpMyAdmin, you can manage multiple databases. You can also make any changes to your database and edit them easily. For example, you can create or delete a database. In addition, there is the possibility of importing and exporting information. This is easy to do and you can do it with just a few clicks.

Most web hosting providers use CPANEL. To access PhpMyAdmin, scroll down the database page and click on PhpMyAdmin. You will see that PhpMyAdmin will open on a new page of the browser. Using PhpMyAdmin is very simple and you can do it easily.

You can see the list of databases you have created by clicking on Databases. Additionally, by clicking on WordPress databases, database tables are displayed.

Be sure to note that you must back up your database before working with the WordPress database; Which is irreversible if the previous version is changed. With WordPress database backup, you can do this safely and in case of any problem, your content is safe.

WordPress Database backup in PHPMyAdmin

Above in this tutorial, we explained how to back up using the plugin. Now click on your WordPress database to create a backup of your WordPress database in PhpMyAdmin. Then in the top menu, click on the Export tab.

The fast method exports your database with the “SQL” extension. The custom method has more methods and more capabilities for compression. We suggest that you use the custom method and select the zip option. Your exported database file can be re-imported via the Import tab.

Import WordPress database backup via phpMyAdmin

We want to import the support version we got from the WordPress database. PhpMyAdmin lets you easily embed your WordPress database. Just launch phpMyAdmin and then select your WordPress database. Next, you need to click on “Import” from the list at the top of the page.

On the next screen, click the Select file button and then select the backup file of your database that you have already downloaded. PhpMyAdmin now processes your backup file and imports it into a WordPress database. When done, you will see a successful message.

WordPress Database Optimization

Like backup in WordPress, it is also possible to optimize the WordPress database through plugins, in this section we will explain the second method, which is through PHPMyAdmin. To do this, you must first go to the PHPMyAdmin section. Then click on your database. You will see a number of WordPress spreadsheets displayed below. Then click on the check all link and select the optimize table option.

Build a WordPress admin user in PhpMyAdmin

To do this, you must first launch phpMyAdmin and then select your WordPress database. This will display the WordPress database tables, then click on the ‘Browse’ option next to the wp_users table.

Now click on the ‘Insert’ option from the list at the top of the page.

Using this form you can add a new user to your WordPress site.

After filling out the form, click the “Go” button to save the form to your WordPress database.

Write down the user ID because you need it in the next step. Now open the wp_usermeta table by clicking the Browse link next to it.

You will now see a form to insert a new row. This is where you tell WordPress that the user you have already created plays the admin role.

• umeta_id: You have to leave it blank because it fills up automatically.
• user_id: Enter the user ID you just copied.
• meta_key: You must enter wp_capabled in this field. However, if you use different WordPress table prefixes, you may need to replace wp_.
• meta_value: You must enter the following serial value:

a:1:{s:13:"administrator";s:1:"1";}

Finally, click the “Go” button to save your changes.

Change the username using PhpMyAdmin in the WordPress database

You may have noticed that although WordPress allows you to change your username, it does not allow you to change your username.

Many users now choose to install their usernames and later decide to change them. Here’s how to do it through phpMyAdmin.

Log in to phpMyAdmin. Then click on your WordPress database.

PhpMyAdmin shows your database tables. You need to click the “Browse” button next to the “wp_users” table. This table shows a complete list of all registered users on your website. Click the “edit” option to change the username of a member of your site.

To change the username, you must replace the previous username with the new username in the “user_login” row.

Click the Go button to save your changes. You can now visit your website and log in with a new username.

Change WordPress password from PhpMyAdmin

In some cases, you will need to reset your site password. Sometimes your password will not be emailed to you and you will have to use another way to reset your WordPress password. The best way to do this after resetting your email password is to change your WordPress password from PhpMyAdmin.

To do this, first, log in to your host’s admin control panel and click on PHPMyAdmin. To enter the database management panel of your site.

You must enter the Wp_Users table and click Browse.

At this stage, all users who are members of the site will be displayed for you. Then you have to click on the Edit button in front of the desired username – for example, admin.

You can change your password in the User_Pass field. This password is hashed for you for more security in WordPress. This is because if someone has access to your database they will not be able to find the password.

You must first hash your password through http://pajhome.org.uk/crypt/md5/ and enter the hash password into the user_pass field.

Finally, after entering the password, click “Go” to save your password. You can now log in to your site with the password you entered.

Install multiple WordPress in one database

WordPress receives a table prefix from you to install. Note that this prefix is “wp_” by default, And you can change this prefix at will. This allows you to infinitely install the website in one database, And use a comprehensive database for all your sites. Of course, this is not recommended, and the reason is that your database is large. But you can do this if you like.

For the second site in this step, the prefix something else, for example, mw_

Your second site is now built on the previous site database with a new database prefix, And you can use both of your sites on one database.

WordPress Database Repair

The WordPress database repair tool is located right in the heart and core of WordPress, which will allow you to repair the database. Therefore, this tool is not active in normal mode, and before doing anything, you must enable the ability to repair the database in WordPress.

Before doing anything, first, log in to your C-panel host or Direct Admin host, and then click on the File Manager option to be directed to the file management page in WordPress.

Now that you have entered the file manager environment, look for the wp-config.php file in WordPress. Right-click on the file and select Edit to open the file editing page.

After the wp-config.php file editing page opens, you need to put the WordPress database repair activation code in this file. So put the following code snippet in the right place in this file just above the line / * That’s all, stop editing! Happy blogging. */.

define('WP_ALLOW_REPAIR', true);

Note that if this code snippet is already in the WordPress config file, you must set its value to true.

Now that you have the code in the file, save the file and add the phrase “wp-admin/maint/repair.php/” to the end of your site URL. For example:

https://honarsystems.com/wp-admin/maint/repair.php

After the WordPress database repair feature is activated, a page will open by referring to the above address, which will allow you to repair the WordPress database and repair and optimize the WordPress database.

• WordPress Database Repair: If you select this option, WordPress will start checking the tables one by one and will repair them. But sometimes it may not be able to fix all the tables.
• WordPress Database Repair and Optimization: If you select this option, the database will start optimizing tables in which no optimization and repair have been done so far.

So if you want to repair a database in WordPress, you must first do this through the first button to see after repairing the database whether there is a table left that WordPress has not been able to optimize in this case or not. Then if you see that some tables are not optimized, you can use the second button, which includes repair and optimization, to repair all tables in WordPress.

What is SQL Query?

SQL stands for Structured Query Language and is a programming language used to manage databases. The SQL request issued to the CRUD data on the database server is called a query. WordPress uses MySQL data to store and retrieve data and generate it on web pages.

A query can be useful, especially if you want to optimize your webpage. For example, to remove spam comments that fill your database and slow down your webpage.

A query can remove those spam comments very quickly, run the following command to do this:

DELETE FROM wp_comments WHERE wp_comments.comment_approved = 'spam';

What Is wpdb?

The $wpdb class is a simple utility for interacting with the database directly. If you’ve been around WordPress code you’ve probably seen the get_posts() function or the WP_Query class. These let you retrieve posts in a flexible fashion, but sometimes you just need more.

$wpdb enables you to address queries to any table in your database, and it also helps you handle the returned data. The $wpdb class lets you add/modify/delete any data in WordPress modularly, making it a very powerful tool.

Because this functionality is built into WordPress, there is no need to open a separate database connection.

How to Interact With the WordPress Database using $wpdb

Always try and use WordPress-native functionality first. If this is not possible or would be too inconvenient, go for it.

WordPress provides a set of functions to interact with databases using the $wpdb object. Since it’s a global object you can call this function anywhere using global declaration.

global $wpdb;

Before taking a detailed look at all the methods available, let’s take a look at a simple example, retrieving a list of post ids.

global $wpdb;
$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");

• get_results() is a method that will not only fetch your results but put them in a convenient object.
• the query itself is next which is standard SQL. You can write any query here which will be executed.
• $wpdb->posts is a variable that points to the posts table.

The $results object now contains 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
    )
)

Retrieving Results From The WordPress Database

When reading data from your WordPress tables you’ll want to:

• get a single piece of data
• a single row
• a single column
• multiple rows and columns

1. GET_VAR

You can use get_var to retrieve only one value from the database as a simple value. The value’s data type will be the same as its type in the database.

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

This example displays the email of the user with “honarsystems” user_login.

2. GET_ROW

When you need to find only one particular row in the database. It pulls the data into a one-dimensional object.

$posts = $wpdb->get_row("SELECT ID, post_title FROM wp_posts WHERE post_status = 'publish'");
echo $posts->post_title;

Gets the latest published post and echo its title.

3. GET_COL

This method gets a single column. This is helpful if you would like to retrieve the IDs of only the top 10 most commented posts.

$posts = $wpdb->get_col("SELECT ID FROM wp_posts WHERE post_status = 'publish'");
echo $posts[3]->ID;

This gets all published posts and stores them in a single dimension array and echo third posts id.

4. GET_RESULTS

It is best for when you need two-dimensional data. It converts the data into an array that contains separate objects for each row.

$posts = $wpdb->get_results("SELECT ID, post_title FROM wp_posts WHERE post_status = 'publish'");
echo $posts[0]->post_title;

This gets all published posts and displays the first-row titles.

Insert Into the WordPress Database

The $wpdb class is prepared to add things to the database for you with the insert() method. The general use of this method is as follows:

$wpdb->insert( $table, $data, $format);

This method takes three arguments. You’ll need to know what table you’re adding to, you’ll need to prepare an array of the data you’re adding and an array of the formats used.

Specifying the format is optional; all values are treated as strings by default, but including this in the method is a good practice. The three values you can use are %s for strings, %d for decimal numbers and %f for floats.

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

Updating Data in Database

If you want to modify your database data, you should use the update function of $wpdb.

$wpdb->update( $table, $data, $where, $format = null, $where_format = null );

Its use resembles what we saw above; but to handle the where clause of our update, it needs two extra parameters.

Using the $where parameter, we can specify the conditions of the update. It should be an array in the form of column-value pairs. If you specify multiple parameters, then they will be joined with AND logic.

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

Deleting Data From WordPress Database

To delete data from the database you can use the delete function.

$wpdb->delete( $table, $where, $format = null );

The parameters are like the update function. It will remove all data that matches the $where condition.

$wpdb->delete( $wpdb->posts, array("ID" => 5), array("%d") );

Running Any Query

Sometimes you need to execute a query that is not executable by the above functions. For example, create a custom table or drop it. In this case, you can use the query function.

$wpdb->query("DELETE FROM wp_usermeta WHERE meta_key = 'first_login' OR meta_key = 'security_key' ");

Query function accepts SQL standard code and executes it.

WordPress Database Error Handling

Finding out why something isn’t working can be a pain when databases get involved. Are you passing the right data, did you mistype a column name, is the table name correct, and so on? There are a couple of ways to help yourself.

By calling the show_errors() or hide_errors() methods, you can turn error-reporting on or off (it’s off by default) to get some more info about what’s going on. Either way, you can also use the print_error() method to print the errors for the latest query.

$wpdb->print_error();

Protection And Validation

You have code like following

$id = $_GET['id'];
$results = $wpdb->get_results( "SELECT * FROM $wpdb->posts WHERE ID =  $id" );

This code has an SQL Injection vulnerability because this query lets visitors interact with databases directly without sanitization, if the $_GET[‘id’] is an absolute number then it’s fine, but a visitor could easily add evil code here.

You can sanitize your code like this

$id = (int) $_GET['id'];
$results = $wpdb->get_results( "SELECT * FROM $wpdb->posts WHERE ID =  $id" );

The above query is relatively safe but still not recommended. You don’t have to sanitize the input by your code because WordPress provides better sanitizing using prepare() function.

$id = $_GET['id'];
$results = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM $wpdb->posts WHERE ID = %d", $id ) );

If your input data is the string you can use %s.

Use $wpdb or NOT to Interact With WordPress Database

It is extremely important to be able to decide when you need to use $wpdb. It is almost always redundant to use it for selecting posts as the WP_Query class is so powerful in this regard that it is almost always a better option.

Don’t forget that when using the WP_Query class, the get_posts() function, or any other function, multiple actions, and filters are run. This makes your code much more modular which is what you’ll want if you’re a plugin or a theme developer.

With $wpdb, you are interacting directly (or close enough) with the database so your code will become much more difficult to interact with.

Extra Functions of $wpdb

There are more functions that $wpdb offers and we talked about some in the above. In the following, you can learn some other extra functions.

1. INSERT_ID

Whenever you insert something into a table, you will very likely have an auto-incrementing ID in there.

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

Inserts data into postmeta table and displays inserted data id.

2. NUM_ROWS

If you’ve performed a query in your script, then this variable will return the number of results of your last query. This is great for post counts, comment counts, and so on.

$wpdb->get_results("SELECT ID, post_title FROM wp_posts WHERE post_status = 'publish'");
echo $wpdb->num_rows;

3. GET_CHARSET_COLLATE

Returns the charset of the database.

$wpdb->get_charset_collate();

4. PREFIX

Returns prefix of the tables of the database. For default is wp_.

$wpdb->prefix

Create a Custom Table

Sometimes you need to create a custom table in the WordPress database for your plugin. You can create the table with a query function but there is a fancy way to do this.

In this example, we are going to create my_table to store the viewer’s IP and view date.

global $wpdb;
    $charset_collate = $wpdb->get_charset_collate();
    $table_name = $wpdb->prefix . 'my_table';

    $sql = "CREATE TABLE IF NOT EXISTS $table_name (`ip` text NOT NULL,`date` text NOT NULL) $charset_collate;";

    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    dbDelta($sql);

As you see there is a dbDelta function. To call this function you have to include upgrade.php into our code.