When working with WordPress, or any other database-driven CMS, it is good to have a basic understanding of the database that stores all of the site’s data. The WordPress database is well designed and has relatively few tables, which makes it easily to work with and update. Understanding how the WordPress database stores information can give you more control of your WordPress site.
Before accessing any database, we need to have three things; an understanding of the tables, connection information, and a tool to view and update the data (a basic understanding of SQL is helpful as well). Let’s look at these three aspects as they relate to the WordPress database:
Tables
The first thing we need to know if we want to work directly with the data in a WordPress site’s database are the table names and what each table is used for. This allows us to know where to look in the database to find the information we want and update it if needed. At the time of this writing (Fall 2015), there are currently 11 tables in a standard WordPress version 4.0 site’s database.
|
Table Name
|
Description
|
|
wp_commentmeta
|
Stores metadata for WordPress comments.
|
|
wp_comments
|
Stores WordPress comment data.
|
|
wp_links
|
Stores data related to the links entered into the Links feature of WordPress. (The Links feature has been deprecated)
|
|
wp_options
|
Stores setting data for the WordPress site. Options set under the Administration/Settings panel are stored in this table.
|
|
wp_postmeta
|
Stores metadata for posts and pages. (Some plugins may add their own information to this table.)
|
|
wp_posts
|
Stores post and page data. Navigation menu items are stored in this table as well.
|
|
wp_terms
|
Stores categories for both posts and links, as well as the tags for posts.
|
|
wp_term_relationships
|
Stores relationship data for categories and tags from the wp_terms table. The association of links with their respective categories are also kept in this table.
|
|
wp_term_taxonomy
|
Stores descriptions of the taxonomy (category, link, or tag) for the entries in the wp_terms table.
|
|
wp_usermeta
|
Stores metadata on WordPress user accounts.
|
|
wp_users
|
Stores WordPress user accounts data.
|
Connection information
The next thing we need to know is how to connect to our WordPress site’s database. This information is stored in the wp-config.php file found in the root WordPress file directory. There are four key pieces of information in this file that we need:
|
Value
|
Value Name
|
Description
|
|
Database Name
|
DB_NAME
|
Database Name used by WordPress
|
|
Database Username
|
DB_USER
|
Username used to access Database
|
|
Database Password
|
DB_PASSWORD
|
Password used by Username to access Database
|
|
Database Host
|
DB_HOST
|
The hostname of your Database Server
|
Database Interface
The last item we need before connecting to a WordPress database to view and edit the row data is a way to send SQL statements to the MySQL database. There are many ways to do this, but I recommend using a web based database management tool for MySQL like phpMyAdmin. Tools like phpMyAdmin provide a simple, but powerful, interface that allows for quick viewing and updating of the data. phpMyAdmin is installed by default with cPanel, the Linux web hosting control panel we use here at Diagram. I will not go into detail how to use phpMyAdmin here, but a lot of information on using this tool can be found on the web.
Note: Before making any updates or changes to any database, it is strongly recommended that you create a backup of the current database.
Three Common Database Updates
Now that we know a little bit about what data is stored in the tables in the WordPress database, how we can find and connect to the database, and how we want to interface with the data in the database, we can get to work. The three most common changes I help people make in their WordPress database are:
- Getting administrator level access to a site.
- Updating the site URL after a server move or when setting up a test copy of the site.
- Updating links after a URL change.
Getting administrator level access to a site
People will often contact me asking to help them get access to their organization’s site after the person administering the site has left the organization. One of the simplest ways to do so (if you have access to the database) is to update an administrator level account with the email address of the person that needs access. This will allow that person to use the default WordPress password reset feature to reset the account’s password.
You can update a user’s email address by doing the following:
- Access the Wordpress database using phpMyAdmin.
- Go to the wp_users table.
- Find the row for the user we want to edit the email address for. User names are stored in the user_login column. We can do this by browsing the table or by running the SQL statement: SELECT * FROM wp_users WHERE user_login = '[USERNAME]';
- Once we find the row containing the login we want to update, we can change the user_email to the new email address using the Edit function in phpMyAdmin. We can also run the following SQL statement to do this: UPDATE wp_users SET user_email = '[NEW EMAIL ADDRESS]' WHERE user_login = '[USERNAME]';
- After the email address is updated in the database, the user needing access can go to the site’s WordPress admin login page and use the “Lost your password?” link to reset the password for that account.
Updating the site’s URL
Sometimes we need to change the URL of a WordPress site but are unable to access the administration area of the site. This often happens after a server move or when setting up a test copy of the site. This can easily be done in the database by updating the wp_options table.
You can update a site’s URL by doing the following:
- Access the Wordpress database using phpMyAdmin.
- Go to the wp_options table.
- Find the row where the option_name column equals siteurl. We can do this by browsing the table or by running the SQL statement: SELECT * FROM wp_options WHERE option_name = 'siteurl';
- Once we find the row we want to update, we can change the option_value column to the new URL using the Edit function in phpMyAdmin. We can also run the following SQL statement to do this: UPDATE wp_options SET option_value = '[NEW URL]' WHERE option_name = 'siteurl';
- Now we can access the site using the new URL and make any other changes we need, such as updating the home page URL.
Updating links after a URL changes
If the URL of your site or a site that you often linked to changes, then the links in posts and pages will need to be updated from the old URL to the new URL. This can be done in the WordPress editor, but if there are more than a few links to be updated, manually updating each link can require a great deal of work. Modifying a large number of links in posts or pages can be done more easily by updating them in the site’s database.
You can update the links in post and pages by doing the following:
- Access the Wordpress database using phpMyAdmin.
- Click on the SQL tab at the top of the right hand panel. Unlike the other two updates described above, this is an update we want to make to all the posts and pages on the site at the same time. This means we will need to use a find and replace SQL statement to do this.
- Copy the following SQL statement into the phpMyAdmin SQL panel: UPDATE wp_posts SET post_content = replace(post_content, '[OLD URL]’, '[NEW URL]');
- Execute the SQL statement. This will update all the links in your post and pages. You can do this for as many URL as you would like to update.
Understanding how the WordPress database stores your site’s information can give you a great deal of control over your site, providing you with powerful tools for administering its content, users, and settings. These examples are only a few of the many ways you can access and update the database to modify your site’s data. Do you have any questions about other ways you can use the database? Do you need help with your own WordPress site’s database? Please contact us to speak to a WordPress expert, or feel free to leave a comment below.
