There are all sorts of unique and interesting bits of information you can access from a WordPress database if you know how to do it. In this tutorial, we are going to cover some of the most useful WordPress database queries that you should know in order to maximize the benefits of your database in WordPress. The first few queries we discuss will be queries you can run on your database from PHPMyAdmin. After those we will get into queries that you can run from within custom WordPress pages — those are the queries that can really help you add interesting dynamic content to your site.
Backing up your WordPress Database
Before you continue and do any of the exercises in this tutorial, we highly recommend that you make a backup of your WordPress database just in case you accidentally cause damage to your data. That way you can easily recover the data tables should something happen. Mistakes in PHPMyAdmin are not reversible without a backup file! Here are the steps to manually backup your WordPress database without having to install a plugin. There are plugins you can use for this, but you can do it right from PHPMyAdmin like this:
- Go directly to PHPMyAdmin (should be install in your web hosting control panel such as Plesk or cPanel) and log in using your database username and password. If your control panel doesn’t have PHPMyAdmin, you may have to download it and install it yourself.
- From within PHPMyAdmin, you will be able to see all your database names in the left column of the page. Locate the one your WordPress site uses and click on it to open and see its tables in the main page area. If you only have one WordPress site, your database will most likely be named “wordpress” or something similar like “WPS”. If you have multiple WordPress sites on your server, be sure you know the name of your database before continuing.
- In the left, main column of the page, click the tab that reads: “Export”.
- From the export dialog, nine times out of ten, most of the default options are okay for you to use with one exception: you will want to check the “save as file” checkbox towards the bottom of the page. To be safe, however, here are the basic options you use to create a backup on your local machine:
- In the export box on the left of the main page area, is a list of tables that reside in your database. The default settings should have them all selected. If you don’t want to backup them all for some reason (we recommend that you backup them all), you can select an individual table by clicking on it. You can also select more than one table by holding down the control key while clicking on the tables that you want backed up.
- Under the table names you selected, there is a list of file types. “SQL” should be selected as your file type.
- To the right of the “Export” box, is the “Options” box. There is a dropdown for “SQL compatibility mode”. Leave it set to “none”. Under the “Structure” heading, “add if not exists”, “add auto increment value” and “enclose table field name in back quotes” should all be checked. Under the “Data” heading, the first two should be checked as well as the last one. “Export type” should be set to “INSERT”.
- In the bottom box, where the “save as file” checkbox is, make sure it is checked. “Remember template” should be checked by default. Finally, for “Compression type” You can either use “none” for a smaller database. Use “zipped” or “gzipped” if you have a larger database or need a smaller file size.
- Lastly, click “Go” and it will save the file to the folder in which you have your browser set to save files to.
Now that your data is safe, let’s continue! Leave your PHPMyAdmin open for now.
Working with Queries from PHPMyAdmin
While you still have your PHPMyAdmin open after backing up your database, let’s look at some queries you can do from within PHPMyAdmin that could be of use to you as a WordPress developer. First go to the tab named “SQL” after selecting your WordPress database. Below is a list of useful queries that you can type directly into the “Run SQL queries” window in the “SQL” tab to accomplish various tasks in WordPress:
- Reset Password Query – Sometimes it can be difficult to reset your WordPress User Password. Here is a quick and easy query you can type in to the query window to reset the password in seconds. Just replace “new_pass” and “username” with your own new password and the username you want the password changed for:
UPDATE wp_users SET user_pass = MD5( ‘new_pass’ ) WHERE user_login = ‘username’;
- Removing Ping Backs – WordPress records all pingbacks for its articles in the database. This can cause your database to become bloated with pingback data, so to reduce the size of your database, you can use the following simple query to delete any unwanted pingback data:
DELETE FROM wp_comments WHERE comment_type = 'pingback';
- Delete Comment Spam – If you have had your WordPress site up for a while now, you probably have a great deal of spam in your comments. Luckily there is a quick and easy way to delete all comments of any type all at once using the following query. If you want to delete all spam comments, leave the query as it is. If you want to delete comments awaiting moderation or approved comments, change where it says “spam” to “0” or “1” respectively:
DELETE FROM wp_comments WHERE comment_approved = 'spam';
Working with Database Queries for Custom Pages or Posts
Another great capability of your WordPress database is it has the ability to display dynamic data from the database in your own custom pages. In the below exercises, we will show you how to include database data in a custom WordPress page. The first thing you will do here towards developing a page with dynamic content from your database is to create a bare-bones style custom page. We will not cover the finer points of creating custom pages or child themes because it’s outside the scope of this article. However, for the purpose of this tutorial, we will just create a very basic custom page and add it to your current WordPress theme. Here is how to create a basic custom page:
- Open notepad or another program you would normally use to write code and start a new file called custompage.php.
- In custompage.php put the following code at the very top of the page:
That’s all that is needed to make your own custom page show up in the WordPress admin. This is necessary to activate it later as you will soon discover. The name you use here is the name that will show up in your WordPress admin when you go to activate the custom page.
The wpdb Class
Now we will add some content to your page using the wpdb database class to do custom queries. We will start with a basic query used to display the number of posts in your WordPress site. To do this simple database query, type or copy the following code into the custompage.php file you started above:
Custom WordPress Database Queriesget_results("select count(*) from $wpdb->posts"); $postcnt = number_format($noof_posts); echo "
There are $postcnt posts on this WordPress site!
Here, in line one, we added a simple header to our page because the custom page we started won’t have the regular WordPress headers and footers unless you add them using the get_header and get_footer functions. We are keeping it simple for this demonstration so we just used a regular H1 tag with an optional style attribute added on to make it green. On line two, we include our standard opening PHP tag. On line three of the above code, you see “global wpdb;” which initializes the wpdb class so we can access its functionalities. This only needs done once in the file for as many queries as you need to make. Then on the next line we set the variable “$noof_posts” equal to the wpdb get_results function that performs the SQL command and returns the number of posts as a stdClass object. In the fourth line we used the variable “$postcnt” to convert the $noof_posts object to a number. Now that our variable is a number, we can use it in the echo statement near the final line of code in order to print the number of posts to your page. That’s all there is to it.
You could also specify just the number of public posts by adding a “where” clause to your database call after the word “posts” in the get_results function above like so:
$noof_posts = $wpdb->get_results("select count(*) from $wpdb->posts WHERE post_status = 'publish'");
Activating a Custom Page in WordPress
Now that we have a fully working custom page, we need to activate it within the WordPress Dashboard. To make your page display in your blog or site follow these steps:
- Upload the file to the wp-content/themes/yourtheme directory where “yourtheme” is replaced by the name of your current theme.
- Go to your WordPress dashboard and navigate to “pages” then “add new”.
- At the top of the “Add new page” dialog, give the page a fitting name such as “Custom Page Test”.
- Then, in the right column of the page, under “page attributes”, in the templates dropdown, select the name you gave your custom page which in our case was “custompage”.
- Go ahead and click “Publish” to publish the page to your site. Don’t worry about adding the content here since we added the content in the custompage.php file itself.
- Now, at the top of the “add new page” dialog, click on the “view page” link to see your custom page live.
Now you should know everything you need to know in order to make and test your own custom pages with a simple database query, but let’s take a look at some more queries so you have more options.
Understanding WordPress Queries
To be able to efficiently access the WordPress database, there are several query types you need to be aware of. Below, we will name and describe the basic query types and give some examples of how they might be used.
- query – you can use the basic query function to do almost any type of query on the database but it is often more efficient to use one of the more specific options listed below. The basic structure for a standard query call would be:
Replace ‘query’ with your SQL statement in the above example.
Here is an example that would delete the sample post that comes with WordPress from the wp_posts table as a test query:
query( "DELETE FROM wp_posts WHERE post_title = 'Sample Page'" ); echo "
Sample Page Deleted!”; ?>
If you want to test this function, go to your wp_posts table in PHPMyAdmin and locate a post you want to delete and replace “Sample Page” with the title of that post (especially if you have already deleted your sample page).
- get_var – the get_var function is used when you need to retrieve a single variable from the database. As an example, we will demonstrate how to get the total number of users from the database, but first, here is the standard usage of the get_var function:
Here is our example query used to get the number of users from the database:
get_var( $wpdb->prepare( "SELECT COUNT(*) FROM $wpdb->users;" ) ); echo "
You have $user_cnt WordPress users!"; ?>
- get_col – The get_col function is used to fetch an entire column from the database. In the following example, we will get the post_title column from the wp_posts table to see how many published posts exist in our blog site:
$title_array = $wpdb->get_col("SELECT post_title FROM $wpdb->posts WHERE post_type = 'post' AND post_status = 'publish'"); $title_cnt = count($title_array); echo "
You have $title_cnt published posts in your WordPress blog.";
First, we set the $title_array array equal to the get_col function results. Then in the next line we set the $title_cnt variable equal to the number of items in the $title_array array. Finally we echo our results and we have effectively reported the number of published posts on the site. Easy as pie!
- get_row – Finally, the get_row function is used to get an entire row of data from the database. This function is handy for cases where you need to report all or several fields of data from a certain row. For example, referring once again to the wp_posts table in our example below, we are going to fetch all the info for a specific post. For this example we will assume we want to get all the data on a single post according to the post’s ID field. To do this for one of your own posts, go to your PHPMyAdmin and select the wp_posts table and locate a row that has a status of “published” and remember the ID number.
Parameters: The get_row function accepts two parameters, the first is the SQL query and the second is the type of data it returns. For the second parameter, you can use either ARRAY_A or ARRAY_N where ARRAY_A returns an associative array and ARRAY_N returns a numerical array. An associative array is an array that uses the associated field names for array keys. A numerical array uses numbers starting at zero for keys. For simplicity, we like to use the ARRAY_N parameter here because if you are looking at the wp_posts table you can easily determine what array keys to use by looking at the field names of the table you are referring to.
Example code: Here is an example of how to get data from wp_posts regarding a single post ID:
get_row("SELECT * FROM $wpdb->posts WHERE ID = 2", ARRAY_A);//queries the wp_posts table for all data on post id #2. //set up variables to hold title, date, comment_count, ID and status $thedate = $entire_post['post_date']; $thetitle = $entire_post['post_title']; $thestatus = $entire_post['post_status']; $thecomments = $entire_post['comment_count']; $thepostid = $entire_post['ID']; echo "Details for post with ID #$thepostid:
"; echo "Title: $thetitle
"; echo "Date Started: $thedate
"; echo "Post Status: $thestatus
"; echo "Number of Comments: $thecomments
Checking Your Work
If you did everything correctly and added all of the queries to your own custom page file, the results should look like this:
The WordPress Database
Notice how we used $wpdb->posts to reference the wp_posts table in our above queries. You could replace that text with wp_posts and it would work also. That’s how most PHP and MySQL coders would know how to do a query, but WordPress stresses that they want you to use their own class references, so go ahead and do as they suggest when you can. It will make life simpler in the long run. The following list will give you the class references to use for common WordPress tables.
- $wpdb->posts = wp_posts table which is the table that holds essential post content data.
- $wpdb->postmeta = wp_postmeta table which holds Meta data regarding specified post and custom field information.
- $wpdb->comments = wp_comments table which holds comments from posts.
- $wpdb->commentmeta = wp_commentmeta table which holds additional information on comments.
- $wpdb->links = wp_links table which holds information on permalinks.
- $wpdb->terms = wp_terms table which holds term descriptions including link categories and tags.
- $wpdb->options = wp_options table which holds admin options for WordPress such as admin email, site URL, etc…
- $wpdb->term_relationships = wp_term_relationships table which holds data regarding the relationship between the term and the object using the term.
- $wpdb->term_taxonomy = wp_term_taxonomy table which holds the classifications for terms.
- $wpdb->users = wp_users table which holds user data.
- $wpdb->usermeta = wp_usermeta table which holds additional user information such as names they use and other metadata.
Okay fellow WordPress enthusiasts, now you know the basic database query types for retrieving data and deleting data from the WordPress database. There are hundreds of queries you can do with the knowledge you have just learned. Go ahead and use what you have learned to develop your own custom queries as needed. It is important to use proper PHP code according to the WordPress Codex. Be sure to reference it whenever you have a question as to how to code something in WordPress. Visit the WordPress Codex site at codex.wordpress.org for more help. Have fun working with your WordPress database and doing new queries to make your pages and posts more dynamic in nature. Code on!