Search and display all duplicates posts in wordpress (by post_title)

I recently had to detect duplicate posts on a very big wordpress website, containing a lot of data about films (where films, actors, filmmaker… are different custom posts). In this database, there is a lot of relations between these custom posts.

To identify the duplicates filmmakers or actors, I had to create a small tool to search into the database and compare all post_title (or other meta). There is no native  function in the WordPress framework to do it. It can be made with a SQL request to search and display all duplicate posts. To do it, I used the php variable global $wpdb;

Here is my SQL request, directly built in a custom page template. I store my request results in a specific variable named $duplicate_titles :

$duplicate_titles = $wpdb->get_col("
  SELECT a.ID, a.post_title, a.post_type
  FROM {$wpdb->posts} AS a
  INNER JOIN (
  SELECT post_title, MIN( id ) AS min_id
  FROM {$wpdb->posts}
  WHERE post_type = 'films'
  GROUP BY post_title
  HAVING COUNT( * ) > 1
        ) AS b ON b.post_title = a.post_title
  AND b.min_id <> a.id
  AND a.post_type = 'films'
  ");

After that, I just need to list all duplicate titles. My result is an object, so I just need to extract the titles in a loop :

if($duplicate_titles) {
  echo '<h2>Films en double :</h2><br>';
  foreach( $duplicate_titles as $duplicate_id ) {
    echo get_the_title ($duplicate_id) . '<br>';
  }
}

retrouvez tous les outils et ressources en liens avec ce tuto ...

Technologies utilisées pour ce tuto :

PHP

SQL

Wordpress