How can i find posts with duplicate featured images?

Question

I came across this plugin "Fix Duplicates" which is able to find duplicate posts based on post title and by replacing value from "post_title" to "post_content" in a few places i am able to make this plugin find posts by duplicate content.

Code for the Plugin http://snippi.com/s/yw9ztfu

Now i want to find posts by duplicate featured images and to be honest i have no clue how to do that, can someone please tell me or if possible make changes to the code for this plugin so that it is able to find posts by duplicate featured images?

Kind Regards
Micheal C.

Answer 1

The featured image's ID is saved in a post meta field by the name of _thumbnail_id, so with a little bit of joining, you can use MySQL to search them for you:

SELECT p1.ID as ID1, p2.ID AS ID2 FROM
wp_posts p1 LEFT JOIN wp_postmeta pm1 ON (p1.ID = pm1.post_id AND pm1.meta_key = '_thumbnail_id'),
wp_posts p2 LEFT JOIN wp_postmeta pm2 ON (p2.ID = pm2.post_id AND pm2.meta_key = '_thumbnail_id')
WHERE
p1.post_type = p2.post_type AND
p1.ID < p2.ID AND
pm1.meta_value = pm2.meta_value AND
pm1.meta_value IS NOT NULL;

This looks for shared featured images within the same post_type, you can just remove that condition if you want to look across all post types. p1.ID < p2.ID is in there so we don't get repeated reports (e.g. "1, 2" and "2, 1"), and it makes sure that we don't compare a post with itself.