This article covers the Featured Images in Wordpress. It is a follow-up to the previous article WordPress Images Reporting with MySQL . For a good understanding, it is recommended to read that story first.
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. When we talk about a post in the future, this also means a page.
This article is about the Featured Image in WordPress - the image that is set as the default for a post. Because only 1 image can be selected as default for each post, you would expect the corresponding field to appear in the wp_posts table. However, that is not the case. In 2009, the Post Thumbnail was launched in version 2.9. Later the name was changed to Featured Image. WordPress policy is to leave the original wp_posts table unchanged - for backward compatibility.
Where can we find the Featured Images? In the wp_postmeta table, where many settings of a post are stored. It is a table with easily thousands of lines. At the index number of a post (post_id), a Featured Image contains the text _thumbnail_id (the former name) in the meta_key field and the meta_value field refers to the post_id of the image.
All Featured Images at a glance
In this query, the wp_posts table is not the basis, but we choose the wp_postmeta table as the starting point. A JOIN creates a link with data from wp_posts.
This is the MySQL query:
SELECT
post.post_title as 'Post Title',
post.post_name as 'Slug',
post.post_content as 'Content',
date(post.post_date) as 'Date',
meta.meta_value as 'MetaValue',
meta.post_id as 'PostId',
post.ID as 'Id',
post.guid as 'Page Location',
parent.ID AS 'Parent Id',
parent.post_title as 'Image Title',
parent.guid as 'Image Location'
FROM wp_postmeta meta
JOIN wp_posts post ON meta.post_id = post.ID
JOIN wp_posts parent ON meta.meta_value = parent.ID
WHERE meta.meta_key = '_thumbnail_id'
ORDER BY post.post_title
Explanation of the steps:
- If necessary, replace the wp_ prefix with the prefix you chose during your WordPress installation
- In deze query wordt gewerkt met aliassThis query uses aliases for the table names. The wp_posts are searched twice via a JOIN
- The WHERE line shows that only records that have the value _thumbnail_id are selected
- 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 Featured Images
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-images-featured-parent10.php');
echo $phpcontent; ?>
A world full of possibilities
If you get the hang of it, you can go in many directions with WordPress, PHP and MySQL. An example is adding thumbnails of the Featured Images.
Then a report limited to 10 records in a PHP file looks like this:
WordPress Featured Images
| Post Link | Date Created | Image Link | Thumbnail |
|---|---|---|---|
| Architecture | 2021-05-10 | Deserted busstation - Leidsche Rijn - Utrecht - Netherlands |  |
| Bridges | 2021-05-05 | Van Leerbrug - Vreeland - Netherlands |  |
| Castles | 2024-08-22 | Kasteel Heemstede - Houten - Netherlands |  |
| Ceilings | 2024-10-10 | Fitzwilliam Museum - Cambridge - United Kingdom |  |
| Fortifications | 2021-05-08 | Fort Kijkuit - Kortenhoef - Netherlands |  |
| Golfcourse Amelisweerd | 2024-09-05 | Golfcourse Amelisweerd - Utrecht - Netherlands |  |
| Golfcourse De Haar | 2024-10-10 | Golfcourse De Haar - Haarzuilens near Utrecht - Netherlands |  |
| Golfcourses | 2024-07-23 | Golfplatz Gut Kuhlendahl - Velbert - Germany |  |
| Hockeygrounds | 2024-07-07 | Almeerse Hockey Club - Almere - Netherlands |  |
| Landscapes | 2021-05-12 | Clouds above the heather |  |
Conclusion
In this article we have taken a closer look at the images in a WordPress website. It has been explained how you can use MySQL queries to generate overviews of the Featured Images on a WordPress website.
- Hits: 224