On today’s episode, we are gonna talk about where data is stored in the WordPress database, right here on Your Website Engineer podcast, episode No. 494. Hello, and welcome to another episode of Your Website Engineer podcast. My name is Dustin Hartzler. I’m excited to be here with you this week as we get closer to that episode No. 500. It’s hard to believe that 494 episodes have went by and just talking about WordPress and different tools within WordPress. And so, that’s been really, really cool and I know that I’ve recycled some topics over the years, but this is what we’re gonna recycle today and talk about where the data’s stored because I’m diving into the databases a little bit more when it comes to WordPress and I just want to kind of highlight where some of those things are.

But, first I’ve got some announcements and a plugin to share. The first announcement is, WordPress 5.5 Beta 3 is now available. It’s getting close. August 11th is the release date. That’s just about three weeks away and they still need some help trying to figure out all the bugs and issues that they’re running into or that might be happening with WordPress 5.5. If you’re interested in setting it up, I do recommend setting up a test site and then use the plugin, the WordPress Beta Tester plugin and then you choose the Bleeding Edge Nightlies as the option and you will get the latest and greatest updates as they come out. You can test with your plugins or your themes or you can just test with your entire site, just don’t do it on a live site until we are all ready to go and WordPress is ready to launch that next version. So, that is the first announcement, WordPress 5.5 Beta 3.

The next thing that I want to talk about today is from an article on WP Tavern and it was all about Unsplash launch as an official plugin for WordPress. And, this probably should have been the plugin of the week, but it is a plugin for the WordPress. It’s on the WordPress Repository and it is co-developed with the team at XWP. It will seamlessly connect to Unsplash’s 1+ million free high-resolution images within the WordPress editor. This is really cool. I use a lot of Unsplash images to use as show notes, to use that images icon for the notes for the podcast, and I use Unsplash a lot because they are the best, they are free, and they work really well and I just really, really like them.

And, now you can add this plugin and then you can search right inside of the block editor. So, you type /unsplash and then you can type what you’re looking for. Maybe you’re looking for a beach or an ocean or maybe you’re looking for code or whatever you’re looking for. And then, you can automatically add the plugin or automatically add the image and then it will automatically bring attribution to the people that took the picture, which is really cool and it’s awesome. It’s one of those things that wish it has been around for years and years and years. So, that is the second announcement that I want to share with you is the official Unsplash plugin.

Next is WordCampus Online is a 2020 conference that features accessibility in higher education topics. It’s July 29th and 30th, so this upcoming weekend, is an online event, it’s free, it’s the fifth annual event, and it’s a two-day event featuring sessions, lightning talks, sponsored demonstrations, and trivia. And, so if you need more information, head on over to WordCamp.us and that is the URL to get more information and there’s a link in the show notes to get right over there.

And then lastly, in the news this week is Astra becomes the only non-default WordPress theme with 1 million active installs. And so, this is a theme made by Brainstorm Force announced that its theme, Astra, had passed the one million active installs. It’s the only one that is, like I said, not one of the 2012, 2013, 2014, 2015, not one of those. And, it’s a huge representation of how many people are using this theme and how great or, I guess, or how powerful this theme can be. They also show that Astra’s at the top, one million. There’s Ocean WP at 600,000. Generate Press is at 300,000. Elementor is at 300,000. And, Store Front, the free theme within WooCommerce, is 200,000. Again, these are free themes, so that doesn’t count the divvies and all the other ones that are out there that are paid themes. So, those are the free ones on the WordPress Repository. So, that is the wrapping up of the news this week.

Heading on over to the Is There a Plugin For That? section, this week I want to show you and tell you about one. It is called Hidden by Roles the Admin Bar. Not quite the best name that I would have picked, but this is a free plugin that’s found on the WordPress Repository and it will allow you to check the roles of the user and show the admin bar according to the options you’ve chosen. So, if you want subscribers to be able to see the admin bar across the top of the page, you can do that. Or, if you only want admins and contributors or authors, you can check that and make sure that they’re the only ones that see the admin bar across the top of the page. It’s kind of an interesting plugin. It’s something that you might need or you might not need, but I wanted to point it out anyway. You can find it by searching “Hidden by Roles the Admin Bar” on the WordPress Repository or you can find the link in the show notes for episode No. 494.

Okay, let’s go ahead and talk all about what’s going on in the world of where your data is stored in the WordPress database. And, this article, or this podcast topic, comes to you just to kind of give you an understanding and an idea of where some things are stored if you’re looking for them. Every once in a while you get a weird glitch in WordPress and something just doesn’t work right or maybe you can’t update a comment or maybe there’s a post or … This happens a lot of time in WooCommerce, where you want to change the value or maybe you want to change what product is linked to an order or something along those lines and the only way to really do it is to go into the database and manually edit the entry. And, it’s one of those things that doesn’t happen that often, but sometimes you might have to do something like this. And so, I wanted to highlight what’s in the default tables of WooCommerce, or in WordPress itself.

But, first let’s go ahead and talk a little bit about WordPress and how it works. So, if you’re familiar with WordPress, there’s two pieces of WordPress. It is a content management system and there’s two parts. There’s a part that’s the database and that stores all of your settings, all your posts, all your pages, all your menus, all of your data. Everything that you see, all the data that loads in the WordPress dashboard when you look at your WordPress site from the backend, all that stuff is stored in the WordPress database. And then there’s a files section and the files can contain the WordPress core files. So, those are there. And, so those are all the files that are in the WP Admin folder and the WP Includes folder. So, that’s all the software that makes up the WordPress software and makes it work. And, then there’s also files like plugins and themes and uploads.

And, so those files are also hosted on your media server or your webserver and the plugins or folders with code in them that contains the code to make your plugin or make your theme do whatever you’re looking for, like the Yost plugin is going to add code to make your page render out some SEO content. Your theme is in there and that’s gonna be all your visuals. And so, those things are kept completely separate and the thought process behind that is, if you wanted to someday update your theme, you could just change your theme and your entire site changes, but none of the data. Like, everything else stays the same. And so, that’s one of the cool parts about WordPress and one of the great parts of being a content management system.

We’re gonna talk about how to search for in the WordPress database the information that we’re looking for. Maybe sometimes you might have to modify some data that doesn’t have the option to modify inside of WordPress itself. Say, for example, like if somebody messed up an order in your WooCommerce store and they need to go in and change what thing was purchased in the order, like that can’t be done from the WordPress dashboard. It has to be done from the WordPress database. And, so that’s one of the things that, it doesn’t happen often, but regularly, or maybe not regularly, it just really depends. Sometimes you might have the reason to go into the database to make an adjustment to something that you can’t do inside of WordPress itself. And, so there are 11 tables in the WordPress database that has all the data stored. And, with that information, those data pieces, stored, then you can kind of search around and you can find them and then you can make the modifications.

Now, there’s 11 by default and then usually every plugin has it’s own set of tables. Like, it’s going to have tables to do different things. And, so if you have Gravity Forms or Ninja Forms, those are gonna have tables. If you’ve got Yost, it’s gonna have different tables. If you’ve got Pretty Links, it’s gonna have tables. If you have the WP Snippets plugin, that’s gonna have tables. If you’ve got WooCommerce, it’s gonna add a bunch of tables. And so, there’s gonna be a lot more tables than just these 11. But, by default when you install WooCommerce, you’re gonna get 11 of these tables. And, they’re all prefixed at WP_ and then the name of the table. And, those prefixes are kind of what the standard WordPress installation is. You can make them custom if you want. So, you want to make them DH_ instead of WP for your initials, like Dustin Hartzler, you could do that.

In this episode in what we’re gonna talk about today, I’m just gonna reference WP_ just because that’s gonna be so much easier and that way we’re kind of all on the same page. Everybody knows that WP_ is going to be what the table prefix name is. Now, also when it comes to databases, I kind of like to think of them as a Google table or a Google Sheets table or Excel table or whatever that each of these tables are its own sheet in the spreadsheet. So, when I talk about the WP_ comments or WP_ commentmeta, when I talk about those, those are like their own tabs. And so, some of them will have lots of columns, wide and some of them will only have a couple of columns wide, but really, really long. And, it all depends on how much data is in your website how big these tables are.

So, let’s go ahead and we’ll dive in. Let’s talk about the 11 tables that are there. I gave reference. We’ll start them top to bottom when it comes to alphabetical order. There is WP_commentmeta and WP_comments. So, you’ll see this. A couple of places you’ll have a table and then you’ll have a table with the same name, but it’ll have meta attached to it. So, in this case, the WP_comments table has a lot of information about the comments. It’s going to have a comment ID, so every comment will have its own ID number. It’s going to have the comment author. It’s going to have the comment author email. It’s going to have the URL of the comment author. It’s going to have some different things, the date posted, the comment content, if the comment was approved or not. It’s all kinds of stuff that’s in there. So, that’s all stored inside the comment itself.

And, then there is a comment meta table and this basically has some information about, from what I can see in this test site, that it has Akismet, which is the spam-blocking plugin. It’s using Akismet to make sure that it is not a spambot. It’s doing its best to try to figure out whether it is a legit comment or not. And, so that’s what’s held there in the WP_commentmeta table. So, that’s what the first two tables are. The next one is the WP_options table and this has most of your options for your WordPress site. It’s got the URL of your site, it’s got your blog name, your blog description. It has your admin email address. Like, anything that you find in the general setting of WordPress, that’s all gonna be there. The date format, the post numbers per page. You’re gonna be able to see the permalink structure. And, I’m just kind of picking out some of these things. The comment registration, HTML type. We’ve got database version. We’ve got all kinds of stuff that’s in there. The thumbnail sizes, that kind of stuff, is all there stored in the WP_options table.

There’s also things that are stored in there when it comes in to some plugins. Some plugins could have some stuff in there. I guess also in the WP_options table has what plugins are activated, what widgets are there, what sticky posts have been tagged. All of this stuff are just settings in the WP_options table. But, not only are just WordPress options there, there are options when it comes to different plugins. So, I am looking at this site and I’ve got some modifications. Like, if you make any modifications in the customizer, those are saved inside of the WP_options. Or, if you make customizations to the theme. Maybe the theme has some options that are saved. Some of those might be saved in the WP_options. If you’re using a plugin … the Jetpack plugin saves a lot of stuff in there. I’m seeing that if your website has maybe Facebook, maybe the Facebook Pixel is there. Maybe the Gravitars are stored in there. There’s a bunch of stuff that’s stored in there.

And, so normally what happens is, when you do an install of WordPress, all of the main stuff, the site ID, home, blog name, blog description, all that kind of stuff is going to be at the top of the database and then as you install things, it’s gonna be kind of in the same order as what you install things because as you install things it’s going to add records to this table. If the table gets too, too big, that’s sometimes when it causes a problem, but we won’t talk about that today just because this test site, I’ve got 700 rows in there. So, it’s not overly huge, but I’ve seen some that have thousands of rows in the WP_options table. Okay, so that is what the WP_options table is.

Next up on the list is WP_post and WP_postmeta. So, the post has information like, it has the post author and that’s set by an ID. So, there’s an author that’s got an ID, it’s got the post date, it’s got the post content, the post title, the exert, whether it’s in draft mode or publish mode. It has if the comments are open or closed. It’s got the post name, like the URL name. The posts include all of the WordPress posts and it’s got the pages or it’s got the menu items. It has WooCommerce orders and stuff are in the WP_post table. It is all there. There’s a lot of information that is there inside of that post table.

And, then there is the postmeta table, which references all of the posts in the WP_post area. And, so what that does is, for an example, I’m trying to look here. A good example is if you have an order. So, if you have WooCommerce order. Let me see if I can pull one up here real quick. If I look in the WP_post table for an order, I find one and I have an ID of 1362. That’s just an arbitrary number. That’s the next order line item that comes in the WP_post table. And, I can go to the WP_postmeta table and I can look up, I can search for that ID and I can see exactly all the details for that specific ID. And, so in that case it’s gonna show me the payment method, like what payment method did they use. It’s gonna show me their address. It’s gonna show me their billing email address. It’s gonna have all the information that is associated with that post. If it’s a page or a post or an order, all of that’s going to be different and all of that data is stored right there in the WP_postmeta table.

Up next on our list is, we’ve got terms. We’ve got WP_terms, WP_termmeta, WP_term_relationship, and WP_termtaxonomy. It is a whole mouthful to do all of those and say all of those things. But, basically the WP_terms, that has both categories and tags. Those are all stored in that table. That’s where all of those are. And, then when you go to the WP_termmeta, that is going to be just meta data for the terms. There’s not a lot of stuff in there unless you have complex tags and categories set up on your site. Then, there is the WP_termrelationships. And, that’s going to relate different terms to each other. So, that is if the posts are associated with categories and tags from the WP_terms table. Again, it’s not gonna be super, super useful. I haven’t ever really logged in and used that. So, that’s not really, really helpful.

And, then there is the WP_termtaxonomy and this describes the taxonomy. So, it basically tells you about the category link or tag. It tells you whether it’s gonna be if the term is a category, a nav menu, a post tag, and basically breaks those down. Or, some plugins, like this website has a plugin for recipes and it has a taxonomy of ingredients. It’s gonna label what terms are actually considered ingredients and how those work across the site. And so, that is the WP_term_taxonomy. Honestly, in all of my time using the WordPress database, I don’t think I’ve ever went into any of those tables that start with WP_term just because it’s not something that needs to be made adjustments very often, if ever.

And, then the last two tables are the WP_users and WP_usermeta. And, that’s basically kind of what it sounds like. The users table has all of the users. So, it’s got their login. It’s got their password that is in an MD5 encrypted format, so you can’t see what their password is. It has their username, their email address. It says when they were registered. It’s got the username or when the users were registered and the display name. So, it’s got all of that stuff in that table. And, then the WP_usermeta table, that one has a little bit more information. It breaks down the capabilities in the user level, so if they’re an admin it’s got special WordPress capabilities and it’s got a specific user level. It shows if you want to have the welcome panel show or the WP_user settings. If metaboxes are hidden. Some very specific thing to the user. And, so if you wanted to turn something on specifically for a user, that option would probably be in the WP_usermeta table.

So, hopefully this wasn’t too confusing. It just kinda gives you a reference and I’ve got listed in the show notes what the tables are and what kind of data is stored in those tables. And, so if you’re interested you can go over to the show notes. Just head on over to YourWebsiteEngineer.com/494 and you can find out all of the links and you can see exactly what I’m talking about there. So, until next week, take care and we’ll talk to you soon. Bye bye.