Hello everybody, welcome back to another episode of Your Website Engineer Podcast. I’m excited to be here with you today because we will be talking about the WordPress database, and this came from a question from a listener who was interested in learning just a little bit more about how things are structured and things along those lines in the WordPress database. But first, I’ve got a couple announcements that I want to share with you. First off, I want to say that last week on Friday – let’s see, it was March 6, I had a webinar all about the ins and outs and why to use Jetpack. You can now find that in the webinar replay area of YourWebsiteEngineer.com.
It was a lot of fun, it was a lot of great information just kind of showing the different pieces of what Jetpack does and how it can impact and benefit your site. There was a lot of positive comments and positive feedback that came out of this, and so if you’re interested and wondering what maybe you could use Jetpack for, this definitely gives you a lot of indications or a lot of different areas that you can use it. It kind of highlights some of the different modules and stuff that’s in there. I want to highlight and talk about the next webinar that’s coming. That will be happening on April 3 at 10:00 a.m. EST and this is probably one of the most complex webinars that I’ve ever done.
I’m going to try to share in a one-hour time period how to create a password protected membership site, and so there’s lots of different plugins to do this. I’m not exactly sure how I’m going to do all of it and how I’m going to set everything up, but be assured that I will spend some time in the next month crafting this webinar and showing you exactly step by step from taking a regular website that you already have and how you can add a membership site on top of that using WordPress. So, again, that is on April 3 at 10:00 a.m. EST, and you can register over at YourWebsiteEngineer.com/webinar.
So, I look forward to seeing you there and I’m really excited to, kind of, dive into some of these membership plugins that are out there because I haven’t done that in quite some time. The other piece of news that came out of the WordPress space for this week is the WordPress.org plugins area, the repository, if you will, got a refresh and it’s no longer as overwhelming as it used to be. When you log in for the very first time over at WordPress.org/plugins, you will see it looks exactly like your WordPress dashboard does when you’re trying to install a new plugin. It’s got a featured area, so it shows the top six featured plugins. It shows popular ones. If you’re signed into the page, so if you have a WordPress.org username and password, if you’re signed in you can actually see your favorites right from there, which is really helpful.
Instead, before in the past, you could only see the favorites from logged in and it was kind of a hassle or you had to go actually move into your – like, you had to look at your profile page and stuff. Now you can this right from WordPress.org/plugins and it also has what’s really cool is a beta testing section. So, you can see all the plugins that are being tested that will eventually possibly go into WordPress core in the future, so you can go ahead and check that out. You can see that right there it kind of highlights it. There are 36,535 plugins with over, let’s see, 866 million total downloads. That’s what’s been happening right here with the plugins.
Again, these cards are kind of like the cards you see inside of your WordPress installation and it shows how many active installs, so some of the updates that we talked about last week. It no longer just shows downloads. It shows the number of active installs, which I think is really nice and really helpful. Instead of seeing Jetpack has, you know, millions and millions of downloads, you see it had one-plus million active installs and bbPress has 200,000 active installs. So, that’s really, really helpful and they’re very general round numbers but it can give you an indication of, okay, this is a good plugin to use and you can install on your site and use.
So, speaking of plugins, let’s head on over to the Is There a Plugin for That section and this one today is called – I don’t even know how to say it, Y-A-S-R, and it is Yet Another Star Rating. This is basically a simple plugin which allows you or your visitor to go in and rate a post or an element. You can use this for a reviewer type website; you can basically just set this up so people can vote on different things. If you wanted to add this to a piece of your store or things like that, you could do that as well. So, this is a plugin that you could find in the WordPress repository or you can find the link in the show notes for Episode No. 223.
All right, today I want to take us on a little journey through the WordPress database because the database is really the bread and butter, the heart and soul of our WordPress sites. If you remember back or if you ever did this in the past, like created an HTML website, you basically had to build each page individually for the most part. There were ways around it but each page had its own content and you had to log it in and you had to add your content in and things like that. So, every time that you wanted to make a change you had to open up that page and find it, and yadda, yadda, yadda. If you made a link change and you wanted to go site-wide you had to do a global find and replace.
I mean, it was all stored in individual files. Now, within WordPress everything’s stored in one database, and there’s lots of tables in the database and we’re gonna talk through each of these tables that come with the default installation of WordPress, but basically all of the stuff that controls your website, whether your plugins are turned on or off or activated or deactivated, what theme you may be using, what the settings for your, you know, everything, that’s all controlled – all your posts, your pages, all that stuff is controlled right from your database and that is one of the beautiful parts about WordPress and it makes it so easy to change from theme to theme is we just are changing the visuals and then all the data stays the same.
So, that’s why WordPress is as powerful as it is. So, today, we’re going to look at the standard tables. There are, one, two, three, four, five, six, seven, eight, nine, ten, eleven tables that come standard with every WordPress installation. So, if you would install WordPress and then immediately open up your database you would see these 11 tables. So, by default, all these tables start and they’re prefixed with wp_ and so I’m going to refer to them as wp_ whatever, but on the – it’s mostly recommended by a lot of the industry to rename these because of the fact that hackers know that, oh, general database installations are set up as wp_links or _options, or whatever it is, so they know how to get in and then once they’re in they can start hacking with your database and things like that.
So, you can go ahead and change these to whatever you want, and a lot of people would just make them eight random characters, underscore, and then links, options, posts, whatever, but for this example we’re gonna just talk through it’s going to be wp_ and then whatever the name is. So, as you’re listening if you want to dive in and take a look at what we’re doing, if you’re using a hosting company that has a cPanel, it’s the easiest to do is log in to your cPanel and then look for the little button that says PHP My Admin and then as you click on that it may ask you for your username and password again.
You can just go ahead and add that information, log in, and then you should be able to see, on the left-hand side you should be able to see all of the databases that are in your site. So, if you’ve installed two or three different WordPress installations on your one hosting company, then you’ll see two or three different databases there as well. If you are using something like Flywheel as your host, they have a special area. It’s in the advanced tab and then there’s a way to manage the database, so theirs is laid out and configured just a little differently, and so are a lot of the other hosts. Those are some of the bigger hosts, you know, the main hosts that are out there.
So, when you finally log in you’ll see all of these tables. You’ll see the tables and they’re wp_comment meta, comments, links, options, post meta, post, term relationships, term taxonomies, terms, user meta, and users. These are all listed alphabetically like this. I might do them in reverse order because comments will make more sense, or comment meta will make more sense once we know what’s in the comments table. So, we’ll just go ahead and start diving in. I’m just gonna do a real brief overview of what’s stored in each of these tables in case you’re looking for specific information you may be able to find it just by looking in the table.
So, let’s go ahead and start with the wp_comments table. This table stores all the comment information for each of your individual comments. So, this includes information like the author, the name of the author, the author email address that left the comment, the IP address of the comment author, the date and time the comment was posted. There’s also a table, or there’s also a column in the table as a GMT time and the date. In case you need that for some reason that’s the Green Mean Time, or whatever, UTC. It’ll actually include the content, it’ll include whether the comment is approved, it will say – actually, it gives you information on where the comment was made, you know, what type of browser, what operating system, stuff like that.
You can say that it also lets you know whether the comment is a comment, an actual comment, a ping back or a track back, and then it also lets you know if it refers to another comment. Is it a reply comment or is it a unique individual comment? I guess before I get into this a little bit more I didn’t really explain this very well, what the database is or what it looks like. The main thing is when you’re thinking about a database, think about an excel spreadsheet or a Google Docs page or whatever, you know, you’re thinking about it’s got lots of rows and lots of columns, and each individual record is in a row and then each individual record has a lot of different parameters that are set, and then each of these parameters are in its own column.
So, for example, in this wp_comments table, the very first column says comment_ID, and what that means is each comment is given a unique identifier which is like 001, 002, 003, you know, and it won’t all look perfectly like that based on the way that databases are formed and whatnot. It auto-increments, so it’s not always going to be 001, 002, 003, but so that’s the unique number. The next column is going to be the post ID, so that’s going to refer to which post in your WordPress database does this comment link to? It would be kind of silly if all of the comments would be posted to the very first Hello World post on your website. So, we want to make sure that the comment goes with the right post.
So, each one of these things that I mention, the author name, author email, these are all in their own columns within the WordPress database. So, essentially, we just have a big excel document that we’re talking about with all of these records. For the most part, every one of those columns has something in it. There are occasional cases where there aren’t items in the column but that’s okay. That’s just a way that they’re all set up. So, comments is the first one that we talked about, and then wp_comment meta, this basically stores further information related to the comment.
So, this one has four columns in the table and basically what this was is it basically will – most of the time this table is empty. On a brand new install it’s empty, even though there’s the comment by Hello World or Mr. WordPress, there’s no additional comment meta. Sometimes I looked into my database and I saw information like whether somebody replied via Live Fire or if you have discuss turned on, the two comment plugins, it gives you a little information about that. There was also a record in there for some people that said is a purchaser, or something along those lines.
For a while I was selling things on my website and then I could tell if somebody was a customer or not based on that criteria there, so that’s a little bit – comment meta, there’s really not a lot of information that’s in there. All right, the next table that I want to talk about is the wp_links table. This is the table to hold all of the links to your blog roll. A blog roll was something that happened way back when WordPress came out and it was basically a widget that you would install and you would put on your sidebar that would link to other blogs that you were currently reading or you were interested in following, and basically in – blog rolls are no more here in 2015.
They’re very rarely used. This feature was kind of the, I guess, the admin section of your dashboard. There used to be a link section in your dashboard. This was removed in WordPress 3.5 and you can actually get it back if you install – you can add a little custom code if you want to use it again or you can use a special plugin to get it back. The table’s still there for backward compatibility, but for the most part this table is empty as well. If you, I think, I’m not exactly sure, if you have a brand new installation of WordPress I’m not sure if it actually, you know, puts anything in there or not or just creates the table just so it makes everything happy. I’m not 100 percent sure on there.
So, that’s the wp_links table. You won’t see much activity there what so ever. The next one, however, you will see a lot of activity and that’s the wp_options table. This is basically where all your settings are stored for your site, you know, your configuration of your theme, your active plugins, the widget data, and temporary cache data is all stored in this plugin, or in this table. Theme settings are normally stored there too. If they’re not in their own table, sometimes plugins will create their own table and they’ll store their information there. All of this is stored in the database, and so if you ever deactivate the plugin and turn it back on, it automatically will pull back in the information that you need without having to reconfigure the plugin 100 percent from scratch again.
Another thing that’s housed right here in the wp_options table is the site URL and your home URL. So, if you’ve ever experienced that white screen of death and things have broken when you tried to change in your dashboard, the home URL, or the site URL, you can always go into this table and replace this with the correct URL and that will get things working again. Also in this table is a few other things that you can look at. It also shows what theme is being active, and so sometimes if you migrate a site from one place to another and you don’t bring in the default theme, something like that, sometimes the theme, it’ll show a white screen as well.
I’ve been able to go in and really play with the WP options table and fix things to make sure that things are working properly and get things back from a restore or some sort of plugin update breaks things. There’s a lot of things you can tweak around with the WP options table. Again, be very careful when you’re doing things. When you do things within the WordPress database there’s no warning that says this may break things. It will just break things. When you do things within WordPress it may give you an error, like hey, you can’t do this, you can’t rename this, you can’t do these things, but when you’re in the database it assumes that you know what you’re doing and you can break things very, very easily.
So, I should have put that as a disclaimer at the top of the show, but that’s what that is. The next one that I want to talk about is called the wp_post table and this is probably the one that’s the most important. I don’t know if it’s the most important, but it’s the one that’s going to be used the most, is probably going to be the biggest table in your database, it houses all the information for post, pages, attachments, revisions, menu items, and custom post types. Even though it’s just called post, everything else is stored in here. This table probably has the most amount of columns as well because there are 23 different columns that are built into this.
Basically, each post or page or attachment or things has a post author, it has a date, it has the content, it has an excerpt, it has a title, it has a status, whether it’s draft, pending, private, published. It also has if comments are allowed or not; there’s a toggle there, if pings are allowed or not, if it’s a password protected post, if it’s a post name so the slug of the URL is there. Let’s see, what else is in there? If the post was ever modified, if the content is filtered for any reason, what the parent is of the post, so if it’s a page it could have a parent or if it’s a child. Let’s see, there’s a handful of other things, the menu display order, post type, things like that. Another thing, I guess, that’s in this is the revisions, and I mentioned that.
Revisions is just basically a duplicate row of every revision that you can see in your WordPress dashboard. A few episodes go I talked about how the revisions tab – I really like that and I have over 100 revisions for the resources page because that’s the one I was creating. It was taking a lot of time and I was tweaking and changing a lot of things. Now, if I go into my WordPress database I will see 100 records that look almost identical because they’re very close, but the only thing that will be different is the post content column will actually be different because that’s where all of the data for the page is stored.
So, that’s the wp_post table. wp_post meta is basically a table that holds extra information about individual post items, like the attached image or custom fields, stuff like that that’s not in the WP post table is in the post meta table. The next three are kind of confusing, wp_terms, and terms are the items – they’re terms of a taxonomy and taxonomy’s always confused me, but I basically think of a taxonomy as kind of the categories and tags are examples of taxonomies. So, categories are kind of those broad level things that you can categorize your posts or – yeah, you can categorize your posts, your information, and then tags are also taxonomies.
You can create additional taxonomies for specific custom post types. Say an example, you are a car dealer and you have a custom post type for automobiles that you’re ready to sell, and then you could have a custom taxonomy as, like, Ford, Chevy, Honda, you know, whatever, as your main cars, and then you could have tags inside of those that are maybe, like, cars, trucks, vans, whatever, something along those lines. So, those are what the wp_terms are. The wp_term_taxonomy, so each of the terms from that table, the terms table is stored in this table and it’s basically the structure to make sure that the terms match up with the taxonomies.
Then, there’s a terms relationship and this table connects WordPress terms with appropriate taxonomies, and so it basically is making sure that all of the taxonomies match up. I don’t know exactly. I don’t get a lot of experience with these three tables, so they’re all there and they’re, by default, they won’t be used too often, but when you start adding those custom post types and stuff like that, that’s when you get a lot of activity in these three tables. The next two tables are all about the users, so there’s the wp_users table and the wp_user meta table. So, the users table is where all WordPress user management is stored.
Information like user login, user password, display name, all of – a lot of the stuff that’s in the user profile is stored right here in this table and you can – that’s the information that’s stored there in this table, so it’s basically, you know, general information about the user and themselves. Then, in the user meta table, this has some of the extra information about each user, some of the extra stuff like, I think, there’s the instant messenger name and some of the other stuff towards the bottom of that user profile page, that’s all stored in there. Then, also, the user privileges for the site are stored in there as well.
If you want to be really tricky you can go in and you could use the database and you could create a new user and you can create that in the users table, but then you also have to go into the user meta table and give them admin rights so you can make a login. That’s another story for another day but that is all possible and can be done specifically through the database. So, if you’ve opened up your PHP My Admin and you’re looking at your database right now you may see lots of other tables. I know that I have 30, 40, 50 tables in some of my sites and that’s because different tables have different – or, different databases have – different plugins add different tables to the database, and so those are more rows that you see there along the side.
I know the Male Poet Plugin adds about 10 rows based on different things because it has the different subscribers and it has the newsletters that are sent out and it has – you know, all of that content is all kind of broken up into different buckets. All that stuff is in there, so if you take a look through your database and you see maybe some tables for plugins that you don’t use anymore, you can go ahead and remove them if you like. Again, like I said, if you’re removing things and you’re making adjustments within the WordPress database, you have to do this at your own risk because it will break things. I’m just giving you fair warning there.
There are some plugins out there that you can make changes and you can tweak and customize your plugin – or, tweak your database from within the WordPress dashboard but I wasn’t able to wrap all that stuff into this short of an episode, so I’ll talk about that at another time. So, most of the data is always stored in the database. There’s very few plugins that will delete data once the plugin has been deactivated and removed from your site, mainly for the fact that they want it to be easy – like, sometimes plugin developers will make it easy in case you have to deactivate and then reactivate the plugin to troubleshoot something.
They don’t want you to have to log out and log back in, deactivate it, reactivate it, and then reconfigure it completely, so that’s why all the tables are kept in the database. So, that’s kind of a brief rundown of the 11 tables that are stored there in the WordPress database. It’s something to kind of play around with on a brand new install. This is what I’d recommend, like, going in and installing WordPress somewhere either locally or on your hosting company, you know, in just kind of a private folder or subdomain or something and then start playing with it and looking around and seeing what you can do. I mean, you can do a lot of things and I found it’s been very easy to add different things or make changes.
When I was building my new website that I recently launched, I wanted to make sure that the Smart Podcast Player was the very first thing that popped up in every page content, and it wasn’t there for a lot of them so I basically – you know, first I got all of the – I created all of them or actually had somebody for me create just a Google document with all the short codes I needed for each episode and then what I did was I went into the database and I was able to open one database entry at a time and I just pasted it there because it was much faster than opening up WordPress and then pasting it in and then saving it and then going back to the all post tab and then going to the next one and then – so, I was just able to go in and do it much, much quicker.
If you decide some day in the future you want to change every word in your website or maybe you want to make every time that I use the word WordPress I want to make sure it hyperlinks to WordPress.org, you can do that. You can do a find and replace right inside your database with a few custom queries and then you can do that just with a click of a button and a snap of your finger. So, there’s so much to learn about the WordPress database. Today’s was just a tour of all of the things that are housed in the WordPress database. All right, well that’s going to wrap up this episode for Your Website Engineer Podcast. I hope that you enjoyed this look inside the WordPress database.
It is very powerful and it controls your entire website. So, there’s a lot to learn about the database. You don’t have to be an expert but I just wanted to make sure that you knew this is where your content is stored inside of your WordPress site. That’s all I’ve got for you this week. Be sure to register over at YourWebsiteEngineer.com/webinar for the upcoming webinar and we’ll talk again next week. Take care. Bye-bye.