An average website or blog owner will probably never need to fiddle with the WordPress database.
However, when it comes to troubleshooting, having a basic understanding of it and being able to access it quickly and easily can save you time (and money)
The WordPress database consists of tables. A table consists of rows and columns (think of it as looking like an Excel worksheet). Each column is known as a field and has a name to identify it. Each row is known as a record:
There are 11 tables in the WordPress database (your database may have more if you have installed certain plugins). I’m not going to go into detail about the entire database but to give you some idea:
The Posts table stores the actual content of each post or page on your blog/website. A post is an individual entry in your blog/website. For example on a site that contains tutorials, such as The Excel Trainer, each tutorial is a separate post. For a site such as MacBites, the show notes for each episode are individual posts. Pages are static entries and are not listed by date. Examples include an About Me page and a Contact page
The Comments table stores comments that people have submitted to your blog via the comments form that appears at the bottom of each post. Each record stores the name and email address of the person who submitted the comment, the date the comment was submitted and the ID of the post that the comment relates to
The Options table stores all the configuration information for the database including the website URL, the Administrator’s usernames and passwords (these are encrypted) and the name of the theme that is being used. Plugin information is often stored in the Options table too, although sometimes the installation of the plugin creates itself creates other tables.
As I said at the start of this post (yes, what you are reading right now is a post on this blog!), there’s often no need to access the database directly. Most of the time you create and manage your posts and blog/website configuration via the browser-based WordPress Control Panel. So when would you need to work directly with the database?
Here’s a few examples of when I’ve done it:
I needed a list of all the tutorials on The Excel Trainer (each tutorial is a post). For each post, I wanted the tutorial title, the date it was published and the URL. I wanted this information stored in an Excel file, one row for each post.
I needed a list of all the testimonials from the MacBites website. For each testimonial I needed the title, the text and the name of the person who submitted it. Again, I wanted this information stored in Excel.
I’ve also had several occasions where being able to access the database directly has been a life-saver. I recently changed the theme on one of my sites and for whatever reason, the theme installation failed. The result of this was that I was unable to login to the site’s Admin Control Panel nor was the site available for anyone else to see!
So how do you access the database directly? Depending on your web host, you may be able to do it via your web host’s control panel, using a browser based tool called PHPMyAdmin. However, PHPMyAdmin is not for the faint-hearted!
If you have a Mac, I recommend that you use a free app called Sequel Pro
To connect to your WordPress database, you’ll need to know the database name, the username and password. These are NOT your WordPress login details. You will have defined these when the database was first created during WordPress installation.
If you manage multiple WordPress-based sites, you can connect to as many databases as you like – you just supply the above credentials for each one.
Once you are connected to a database, you see a list of tables down the left hand side. Double clicking on the name of a table opens it and displays the data in a row/column format. With a table open, existing data can be edited (this is how I fixed the corruption that occurred when I tried to install a theme).
Sequel Pro includes a powerful SQL Query Editor which allows you to create and run your own queries. An SQL query is a set of instructions, written in a language called SQL, which can be used to extract specific information.
So for example, to get a list of the post title and post date of all the published posts in the Tutorials category, listed in date order with the most recently published post first, I switch to the Query Editor and type in the SQL instructions (SQL is not difficult to learn and there’s plenty of help and example code available online) and click RUN QUERY.
The requested information is displayed on the screen in a row and column format. I can then export this information to a CSV file and open it in Excel.
If you’d like to know more about opening CSV files in Excel, check out this tutorial from Hannah Sharron
Sequel Pro has come on a long way since I first used it a couple of years ago and is now a critical tool in my database management and website management toolkit.