WordPress is the most popular Content Management System. Everything revolves around messages and pages, which come to life on your website with the support of an attractive theme. In WordPress you can install plugins that show a lot about the published posts. For example, the most recent or the most popular. The data for this is retrieved from the WordPress database.
Would you be interested in investigating whether you can compile these types of overviews yourself? We assume that you have some knowledge of MySQL and that you can execute queries in phpMyAdmin or in a MySQL client. There you can run the scripts described below. The next step is that you master writing a PHP script so that you can create a report. Creating such a script is beyond the scope of this article. If you want to display the output in a WordPress post or page, a plugin is required. This allows you to encapsulate a PHP script in a post or page. The name of this free plugin is Insert PHP Code Snippet.
All posts and pages at a glance
This is the MySQL query:
SELECT
post_title as 'Title',
post_name as 'Slug',
date(post_date) as 'Date',
ID as 'Id',
post_type AS 'Type',
guid as 'Website'
FROM wp_posts
WHERE (post_type = 'post' OR post_type = 'page') AND post_status = 'publish'
ORDER BY post_type DESC, post_title
Explanation of the steps:
- If necessary, replace the wp_ prefix with the prefix you chose during your WordPress installation
- The WHERE line shows that only the published posts and pages are selected
- The order is first determined by the post type (post or page) and then by the title
The above query produces the following result via the Insert PHP Code Snippet plugin on a WordPress website:
WordPress Posts and Pages
| Title | Slug | Date | Id |
|---|---|---|---|
| All Collections | all-featured-images | 2024-11-14 | 660 |
| All Images | all-images | 2024-11-14 | 656 |
| All Posts and Pages | all-pages | 2024-11-10 | 642 |
| Architecture | architecture | 2021-05-10 | 283 |
| Bridges | bridges | 2021-05-05 | 262 |
| Castles | castles | 2024-08-22 | 458 |
| Ceilings | ceilings | 2024-10-10 | 579 |
| Fortifications | fortifications | 2021-05-08 | 274 |
| Golfcourse Amelisweerd | golfcourse-amelisweerd | 2024-09-05 | 480 |
| Golfcourse De Haar | golfcourse-de-haar | 2024-10-10 | 600 |
To keep this page clear, the number of lines is limited to 10. The hyperlinks in the Title column refer to the corresponding page on the weremere.com website.
The Insert PHP Code Snippet is filled with the following code:
<?php
$phpcontent = file_get_contents('https://www.weremere.com/wp-reports/wprpt-posts10.php');
echo $phpcontent; ?>
The full report can be viewed in a PHP file on the site weremere.com.
Once you get the hang of making queries in MySQL, it is a small step to create new variations yourself. In the meantime we elaborate further on this subject.
Wordpress posts and pages supplemented with hits
A logical next step is to look at the number of visits to the web page. This option is not included in the standard WordPress installation, but it is easy to add. To do this, install and activate the plugin Popular Posts Data. This allows you to display various lists of popular posts on your WordPress site. But the purpose of this article is to set up a query in MySQL with the help of this plugin.
Popular Posts Data creates some new tables in your database. The most important tables are wp_popularpostsdata and wp_popularpostssummary. Apparently someone here had fun reversing the meaning of these 2 tables! wp_popularpostsdata contains the totals and wp_popularpostssummary records the individual hits.
The MySQL query looks like this:
SELECT
wp_posts.post_title as 'Title',
post_name as 'Slug',
date(wp_posts.post_date) as 'Date',
wp_posts.ID as 'Id',
post_type AS 'Type',
wp_posts.guid as 'Website',
wp_popularpostsdata.pageviews AS 'Hits',
date(wp_popularpostsdata.last_viewed) as 'Last Hit'
FROM wp_posts LEFT JOIN wp_popularpostsdata ON wp_posts.ID = wp_popularpostsdata.postid
WHERE (wp_posts.post_type = 'post' OR wp_posts.post_type = 'page') AND wp_posts.post_status = 'publish'
ORDER BY wp_popularpostsdata.pageviews DESC
Naturally, the order in terms of number of visits is from high to low
And this is what it looks like, limited to 10 records in a PHP file:
WordPress Posts, Pages and Hits
| Title | Type | Date | Hits | Last Hit |
|---|---|---|---|---|
| Night at the river | page | 2021-05-12 | 313 | 2025-09-29 |
| Windmills | page | 2021-05-01 | 301 | 2025-09-27 |
| Villages in lockdown | page | 2021-05-05 | 277 | 2025-10-01 |
| Architecture | page | 2021-05-10 | 191 | 2025-09-30 |
| Waterworks | page | 2021-05-05 | 129 | 2025-10-02 |
| Fortifications | page | 2021-05-08 | 114 | 2025-09-30 |
| Landscapes | page | 2021-05-12 | 104 | 2025-10-01 |
| Hockeygrounds | page | 2024-07-07 | 101 | 2025-10-02 |
| All Posts and Pages | page | 2024-11-10 | 93 | 2025-09-30 |
| Golfcourses | page | 2024-07-23 | 87 | 2025-10-02 |
The full report can be viewed in a PHP file at weremere.com.
WordPress posts and pages broken down by category
To keep your contributions organized, it is useful to link the posts and pages to a category. With a CMS like Joomla, the category is part of the articles table. Each item can only belong to 1 group. With WordPress it is set up differently. There is a series of tables starting with wp_term. An object (for example a post or page, but also media or tags) can be linked to 1 or more groups. A post can therefore be classified into multiple categories.
Remarkably, you can assign a post (a time-related message) to a category, but in a standard installation you cannot assign a page (a static message). Of course there is a solution for that. With the plugin Pages with category and tag you can select one or more categories in the right column of a page.
The following MySQL query appears:
SELECT
wp_posts.post_title as 'Title',
post_name as 'Slug',
wp_terms.name as 'Category',
date(wp_posts.post_date) as 'Date',
wp_posts.ID as 'Id',
post_type AS 'Type',
wp_posts.guid as 'Website',
wp_popularpostsdata.pageviews AS 'Hits',
date(wp_popularpostsdata.last_viewed) as 'Last Hit'
FROM wp_posts
LEFT JOIN wp_popularpostsdata ON wp_posts.ID = wp_popularpostsdata.postid
LEFT JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id
LEFT JOIN wp_terms ON wp_term_relationships.term_taxonomy_id = wp_terms.term_id
WHERE (wp_posts.post_type = 'post' OR wp_posts.post_type = 'page') AND wp_posts.post_status = 'publish'
ORDER BY wp_popularpostsdata.pageviews DESC
Limited to 10 records, the output of a PHP file looks like this:
Posts, Hits and Categories
| Title | Category | Type | Date | Hits | Last Hit |
|---|---|---|---|---|---|
| Night at the river | Photo Collections | page | 2021-05-12 | 313 | 2025-09-29 |
| Windmills | Photo Collections | page | 2021-05-01 | 301 | 2025-09-27 |
| Villages in lockdown | Photo Collections | page | 2021-05-05 | 277 | 2025-10-01 |
| Architecture | Photo Collections | page | 2021-05-10 | 191 | 2025-09-30 |
| Waterworks | Photo Collections | page | 2021-05-05 | 129 | 2025-10-02 |
| Fortifications | Photo Collections | page | 2021-05-08 | 114 | 2025-09-30 |
| Landscapes | Photo Collections | page | 2021-05-12 | 104 | 2025-10-01 |
| Hockeygrounds | Photo Collections | page | 2024-07-07 | 101 | 2025-10-02 |
| All Posts and Pages | Sitemap | page | 2024-11-10 | 93 | 2025-09-30 |
| Golfcourses | Photo Collections | page | 2024-07-23 | 87 | 2025-10-02 |
This report can be seen with this PHP file. And this is the version at weremere.com. The following Insert PHP Code Snippet has been applied:
<?php
$phpcontent = file_get_contents('https://www.weremere.com/wp-reports/wprpt-posts-hits-cats.php');
echo $phpcontent; ?>
Conclusion
Here above is discussed how you can use MySQL queries to create overviews of the content of your WordPress website on your own. During the process various plugins were proposed to include new columns in the reports.
- Hits: 224