Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I just exported a largish WP blog from MediaTemple to PHPFog.

I used the standard WordPress export and import plugins.

For some unknown reason all of my media assets have been duplicated. I now have twice as many images per post.

If an original file was called "Lot-44-Warrens.jpg" it now has a duplicate called "Lot-44-Warrens1.jpg" Both files are attached to the same post.

I now have many duplicate images across about 250+ posts.

So my question is how do I remove said duplicates from the media library and from the posts?

I tried to search the media library with "*1.jpg", but it didn't work.

Looking for a neat solution that doesn't mean removing each dupe manually.

Perhaps there is a MySQL query I can run to remove the dupes from the library and the posts?

The site in question is: http://igrealty.phpfogapp.com/ .

share|improve this question
    
Could you delete them all and reimport? –  developdaly Apr 20 '12 at 16:06
    
Tried that 4 times last night. Every time they imported with dupes. :( –  jnthnclrk Apr 20 '12 at 18:04

4 Answers 4

up vote 1 down vote accepted

combining the two answer on this page, I found this worked.

$args1 = new WP_Query(
  array('post_type' => 'post',
        'posts_per_page' => -1));
$loop = new WP_Query($args)
while($loop->have_posts()) {
  the_post();
  $args2 = array(
    'order' => 'ASC',
    'post_type' => 'attachment',
    'post_parent' => $post->ID,
    'post_mime_type' => 'image');
    $attachments = get_posts($args2);
    if($attachments) {
      foreach ($attachments as $img_post) {
        if( ((strpos($img_post->guid, '1.jpg')!== false) || (strpos($img_post->guid, '1.gif')!== false) || (strpos($img_post->guid, '1.png')!== false))){
          $stuff = $img_post->guid;
          wp_delete_attachment($img_post->ID);
        } 
      }
    }
} wp_reset_postdata();
share|improve this answer
    
I must try that –  jnthnclrk Mar 20 at 20:00
    
@jnthnclrk I had some issued importing images and it duplicated 2000+ of them, this did the trick in getting rid of the duplicates. Test it out first 5 or so posts, also though it works, I just noticed one or two errors with the loop which I'll edit now. –  UzumakiDev Mar 21 at 0:53

Use a run-once script to clean it up. Just an outline, no code:

  1. Get all posts. See get_posts( array ( 'numberposts' => -1 ) )
  2. For each post get all attachments. See get_children( array ( 'post_type' => 'attachment', 'numberposts' => -1 ) )
  3. For each attachment get the attachment URL. See wp_get_attachment_url()
  4. If you find the attachment URL in the parent post's content ($post->post_content):
    • If there is another attachment URL with the same file name plus the 1 and
    • both are part of the post content then
    • remove the second image first then
    • use wp_delete_attachment() to delete the physical file. This will remove all meta data and all associations in other posts too. It is the best way to remove attached files (imho).

This may take a while. Test it on a local copy of your site. Maybe you should run the process in steps of 50 posts ('numberposts' => 50).

share|improve this answer
    
Not sure why the bounty got awarded. This didn't actually work for me at all. –  jnthnclrk May 11 '12 at 11:03
    
Sorry to read that. It happened due to the votes. See the bounty FAQ for details. –  toscho May 11 '12 at 14:38
    
Ah well. No biggie. –  jnthnclrk May 11 '12 at 15:01
1  
Sorry to bring this back up, but it seems you've glossed over the meat of the question. For instance I need to the details of 4a, b and c. –  UzumakiDev Mar 8 at 21:00

This script will grab all of the attachments in the database, compare the file to one another through md5 and if it finds a duplicate and it has a 1 at the end of the file name it will remove the image:

require('wp-load.php');

global $wpdb;

$img_posts = $wpdb->get_results("SELECT * FROM {$wpdb->prefix}posts WHERE post_type like 'attachment'");

$img_md5s = array();

foreach($img_posts as $img_post){
  $single_img_md5 = md5_file($img_post->guid);

  if(in_array($single_img_md5, $img_md5s) && ((strpos($img_post->guid, '1.jpg')!== false) || (strpos($img_post->guid, '1.gif')!== false) || (strpos($img_post->guid, '1.png')!== false))){
    wp_delete_attachment($img_post->ID);

  }else{
    $img_md5s[] = $single_img_md5;
  }
} 

Just place it in a file in your root directory.

share|improve this answer
    
You might want to run the script and print out the img's guid to see how accurate it is first then if you like the results, run the script with wp_delete_attachment. –  Lee Apr 23 '12 at 20:49
    
Wow! Thanks! Trying it now. –  jnthnclrk Apr 23 '12 at 20:58
    
Won't work, as it is missing the $wpdb function for $image_post. Further $wpdb->("SELECT * FROM {$wpdb->prefix}posts WHERE post_type like 'attachment'"); should be: $wpdb->query( $wpdb->prepare( "SELECT * FROM %s WHERE post_type LIKE %s", $wpdb->posts, like_escape( '%attachment%' ) ) );... –  kaiser Apr 23 '12 at 21:08
    
Yeah, I get this error: Parse error: syntax error, unexpected '(', expecting T_STRING or T_VARIABLE or '{' or '$' in /var/fog/apps/24741/igrealty.phpfogapp.com/find-dupes.php on line 6 –  jnthnclrk Apr 23 '12 at 21:15
    
Now getting this error: Warning: Invalid argument supplied for foreach() in /var/fog/apps/24741/igrealty.phpfogapp.com/find-dupes.php on line 11 –  jnthnclrk Apr 23 '12 at 21:22

I learn't a valuable lesson yesterday, if an application does not provide you with adequate functions for finding and removing assets from a database, and you're trying to find duplicates across multiple, often unique, fields, and you're unsure how to create complex MySQL queries; then the best bet is to go back to basics.

In the end, I exported the tables with dupes into Excel, filtered them by creating my own "hash" of significant fields ( doing this in MySQL was complex and crashed the server a few times ) and pruned the data set so that I created a list of IDs that I was absolutely sure I wanted to remove. I then built a much more simple MySQL query to delete each row by ID.

This method worked great because I was able to take things slowly and consider each Excel filter I applied. This way I was much more confident I was deleting the correct records. I also have an accurate record in Excel of exactly what I did delete.

share|improve this answer
    
MySql and PHP are much more sophisticated and advanced than Excel or any other spreadsheet program. You just don't have the right skills to implement the solution with them. –  Omar Abid Jan 19 '13 at 23:07
1  
I couldn't agree more. –  jnthnclrk Jan 20 '13 at 15:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.