In the article WordPress Posts Reporting with MySQL a first step has been taken to produce overviews from your WordPress database with MySQL queries. The advice is to first read this article as a basis and then read this sequel with the knowledge you have gained.
We still 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.
This article concerns the images that you have uploaded to illustrate your WordPress post or page. In the WordPress database, the wp_posts table has become a repository of all kinds of content components. Just look at the post_type field. In addition to the now gradually well-known components post and page, there are also elements such as revision (earlier versions of a post or page), menu components and items related to installed plugins. The post_type attachment is important for managing images and, relatedly, the value image/jpeg in the post_mime_type field. When we talk about a post in the future, this also means a page.
All images at a glance
First we create a simple query on the wp_posts table without linking to other tables.
This is the MySQL query:
SELECT
post_title as 'Image Title',
post_name as 'Slug',
post_content as 'Content',
date(post_date) as 'Date',
ID as 'Id',
guid as 'Image Location'
FROM wp_posts
WHERE post_type = 'attachment'
AND post_mime_type = 'image/jpeg'
AND post_status = 'inherit'
ORDER BY 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 attachments of the type image/jpeg are selected, which have the status inherit
- The order is determined by the title of the post
The above query produces the following result via the Insert PHP Code Snippet plugin on a WordPress website:
WordPress Images
| Image Title | Post | Date | Image |
|---|---|---|---|
| Almeerse Hockey Club - Almere - Netherlands | 421 | 2022-11-22 | Image |
| Amersfoortse Mixed Hockey Club - Amersfoort - Netherlands | 433 | 2021-09-09 | Image |
| Amsterdam Rijn Kanaal - Maarssen - Netherlands | 703 | 2025-07-25 | Image |
| Antic Varador - Valencia - Spain | 475 | 2023-06-26 | Image |
| Balcoes - Ribeiro Frio - Madeira | 488 | 2023-09-29 | Image |
| Baslica de la Mare de Du dels Desemparats - Valencia | 581 | 2023-06-24 | Image |
| Biggeblick - Attendorn - Germany. | 486 | 2022-05-10 | Image |
| Bismarckturm - Langenberg - Velbert - Germany | 403 | 2024-07-18 | Image |
| Breda Hockey Vereniging Push - Breda - Netherlands | 420 | 2022-12-08 | Image |
| Breukelen - Netherlands | 236 | 2020-04-10 | Image |
To keep this page clear, the number of lines is limited to 10. The hyperlinks in the Image column refer to the images.
The full overview can be seen at weremere.com.
The Insert PHP Code Snippet is filled with the following code:
<?php
$phpcontent = file_get_contents('https://www.weremere.com/wp-reports/wprpt-images10.php');
echo $phpcontent; ?>
WordPress images supplemented with the parent post
A WordPress post can contain multiple images. As described earlier, a separate record is created for each image in the wp_posts table. The post_parent field then contains a reference to the index number of the post. If the title must be searched for, it is necessary to create a self-join: The same table is searched for.
This means that wp_posts appears once in the FROM line and a second time in the JOIN line. To avoid confusion, aliases are assigned to both wp_posts entries. In this query the aliases are post and parent. These aliases appear in the query as prefixes for the field descriptions.
The MySQL query looks like this:
SELECT
post.post_title as 'Image Title',
post.post_name as 'Slug',
post.post_content as 'Content',
date(post.post_date) as 'Date',
post.ID as 'Id',
parent.ID AS 'Parent Id',
parent.post_title as 'Post Title',
post.guid as 'Image Location'
FROM wp_posts post
JOIN wp_posts parent
ON post.post_parent = parent.ID
WHERE post.post_type = 'attachment'
AND post.post_mime_type = 'image/jpeg'
AND post.post_status = 'inherit'
ORDER BY parent.post_title, post.post_title
The order goes first by the name of the parent post title and then by the post title of the image.
And this is what it looks like, limited to 10 records in a PHP file:
WordPress Images with Parent Post
| Image Title | Post | Date | Image |
|---|---|---|---|
| Central Station - Utrecht - Netherlands | Architecture | 2023-05-22 | Image |
| Ciudad de las Artes y las Ciencias - Valencia - Spain | Architecture | 2023-06-27 | Image |
| Deserted busstation - Leidsche Rijn - Utrecht - Netherlands | Architecture | 2020-02-14 | Image |
| Lisbon - Estao do Oriente | Architecture | 2022-06-29 | Image |
| Radio Kootwijk - Near Apeldoorn - Netherlands | Architecture | 2022-08-05 | Image |
| Van Leerbrug - Vreeland - Netherlands | Bridges | 2020-05-06 | Image |
| Castle Nijenrode - Breukelen - Netherlands | Castles | 2023-12-06 | Image |
| Kasteel Heemstede - Houten - Netherlands | Castles | 2023-11-30 | Image |
| Baslica de la Mare de Du dels Desemparats - Valencia | Ceilings | 2023-06-24 | Image |
| Fitzwilliam Museum - Cambridge - United Kingdom | Ceilings | 2024-09-26 | Image |
The Post column contains a hyperlink to the parent post. And the Image column contains a reference to the image.
The full report can be viewed in a PHP file at weremere.com.
Conclusion
This article explains how you can use MySQL queries to generate overviews of the images on a WordPress website. It is shown in a concise manner how the wp_posts table is structured and what the connections between the posts are.
- Hits: 218