0

On a WP install with about 1400 images and the Media Tools 1.1 plugin, I ran both "Import External and Set Featured Image" and "Set Featured Image".

But the featured images functionality didnt work, because it designated (in the DB) the wrong filename for the images: Instead of pointing to the correct file, it appended a "1" to its name, causing, for example, a 404 for "Protest-11-620x264.jpg", when it should point to "Protest-1-620x264.jpg"

I need to do a search & replace in the DB on _wp_attachment_metadata field and remove the extra "1" from the filenames. Naturally I will use a regex, but how do I process the contents of the field? Which one of the sections of the data has which featured image to use? In other words, what do I need look for to know the filename is the right one to search&replace?

For example (note the "Protest-11-620x264.jpg" in there)

_wp_attachment_metadata = a:5:{s:5:"width";i:800;s:6:"height";i:536;s:4:"file";s:22:"2013/11/Protest-11.jpg";s:5:"sizes";a:9:{s:9:"thumbnail";a:4:{s:4:"file";s:22:"Protest-11-150x150.jpg";s:5:"width";i:150;s:6:"height";i:150;s:9:"mime-type";s:10:"image/jpeg";}s:6:"medium";a:4:{s:4:"file";s:22:"Protest-11-300x201.jpg";s:5:"width";i:300;s:6:"height";i:201;s:9:"mime-type";s:10:"image/jpeg";}s:6:"slider";a:4:{s:4:"file";s:22:"Protest-11-800x400.jpg";s:5:"width";i:800;s:6:"height";i:400;s:9:"mime-type";s:10:"image/jpeg";}s:7:"content";a:4:{s:4:"file";s:22:"Protest-11-620x264.jpg";s:5:"width";i:620;s:6:"height";i:264;s:9:"mime-type";s:10:"image/jpeg";}s:9:"spotlight";a:4:{s:4:"file";s:22:"Protest-11-580x326.jpg";s:5:"width";i:580;s:6:"height";i:326;s:9:"mime-type";s:10:"image/jpeg";}s:4:"loop";a:4:{s:4:"file";s:22:"Protest-11-174x131.jpg";s:5:"width";i:174;s:6:"height";i:131;s:9:"mime-type";s:10:"image/jpeg";}s:8:"carousel";a:4:{s:4:"file";s:21:"Protest-11-174x98.jpg";s:5:"width";i:174;s:6:"height";i:98;s:9:"mime-type";s:10:"image/jpeg";}s:8:"cp_large";a:4:{s:4:"file";s:22:"Protest-11-300x225.jpg";s:5:"width";i:300;s:6:"height";i:225;s:9:"mime-type";s:10:"image/jpeg";}s:8:"cp_small";a:4:{s:4:"file";s:20:"Protest-11-70x53.jpg";s:5:"width";i:70;s:6:"height";i:53;s:9:"mime-type";s:10:"image/jpeg";}}s:10:"image_meta";a:10:{s:8:"aperture";d:3.5;s:6:"credit";s:0:"";s:6:"camera";s:10:"NIKON D40X";s:7:"caption";s:0:"";s:17:"created_timestamp";i:1383833520;s:9:"copyright";s:0:"";s:12:"focal_length";s:2:"18";s:3:"iso";s:3:"200";s:13:"shutter_speed";s:7:"0.00125";s:5:"title";s:0:"";}}  
  • 3
    attachment meta data is not related to featured image. the featured image is stored as post meta data with key _thumbnail_id that contains the ID of the attachment, there's no filename tied to a featured image. – Milo Mar 1 '14 at 22:20
  • If you are pretty sure that the 1 is appended then write a script to look for all the images in the wp-uploads folder for that extra 1 and remove it and rename the files. – Maruti Mohanty Mar 4 '14 at 8:05
  • That wouldnt work because only featured images had the 1 appended. All other images work fine. – Gaia Mar 4 '14 at 15:46
  • No need to scream and shout that much ;-) – Pieter Goosen Mar 18 '16 at 7:32
3
+25

You should probably not run preg_replace on the meta data for a _wp_attachment_metadata field as the data is serialized. I would recommend instead you write a small script to loop through the images, unserialize the data, update the value, serialize and store the modified array. Here's a sample function which does just that:

function wpse_135525_fix_imagepaths() {
    $images = get_posts(array(
        'post_type' => 'attachment',
        'post_mime_types' => 'image',
        'posts_per_page' => -1,
        // Extra filtering, e.g. by post_date etc.
    ));

    foreach ($images as $image) {
        $attachment_meta = get_post_meta($image->ID, '_wp_attachment_metadata', true);
        if ($attachment_meta) {
            $attachment_meta = unserialize($attachment_meta);

            $filename = $attachment_meta['file'];
            $filename = preg_replace('/(\d)\1/', '$1', $filename); // Replace any two repeating digits (44 => 4, 11 => 1 etc.)

            $attachment_meta['file'] = $filename;

            update_post_meta($image->ID, '_wp_attachment_metadata', serialize($attachment_meta)); 
        }
    }
}

You'll probably want to limit the post query as to only process attachments created within a defined period of time, and the regex should need some improvement to prevent it from replacing any repeating digits in the first part of filenames etc.

  • I can't limit by date, as all featured images are gone. What I can do is limit to only attachments that are being used as featured images. This is RegEx pattern I came up with, in Perl/Boost syntax: 1_\d+?-\d+?\.jpg – Gaia Mar 7 '14 at 16:23
1

You can get all ids of featured images querying the "post_meta" table for "_thumbnail_id" (as Milo said) in the "meta_key" column and selecting the "meta_value" column.

Then replace your values in the same "post_meta" table where "post_id" are the ones above and "meta_key" is "_wp_attached_file" and / or "_wp_attachment_metadata"

Hope that makes sense

0

I had the same problem with 1100 images:

This will remove text from the meta data entirely. You could also replace the text with your site name or something, eg "Mysite image".

  UPDATE wp_postmeta SET meta_value = 
  REPLACE( meta_value,  'OLYMPUS DIGITAL CAMERA',  
  '                      ' ) 
  WHERE meta_value LIKE  '%OLYMPUS DIGITAL CAMERA%' 
  AND meta_key =  '_wp_attachment_metadata';

To replace with specific text:

  UPDATE wp_postmeta SET meta_value = 
  REPLACE( meta_value,  'OLYMPUS DIGITAL CAMERA',  'Mysitename Image    ' ) 
  WHERE meta_value LIKE  '%OLYMPUS DIGITAL CAMERA%' 
  AND meta_key =  '_wp_attachment_metadata';

For a specific image or to test limit by meta_id or post_id like

 UPDATE wp_postmeta SET meta_value = 
 REPLACE( meta_value,  'OLYMPUS DIGITAL CAMERA',  '                      ' ) 
 WHERE meta_value LIKE  '%OLYMPUS DIGITAL CAMERA%' 
 AND meta_key =  '_wp_attachment_metadata' AND meta_id =705412;

edit: Based on Gaia's point, I should mention that the length of the new string must match the length of the old string or the meta data could break.

And, just to add another point, there's a script here that I have not used, but found when researching this issue.

http://davidcoveney.com/575/php-serialization-fix-for-wordpress-migrations/

  • 1
    You should probably not run preg_replace on the meta data for a _wp_attachment_metadata field as the data is serialized. – Gaia Mar 18 '16 at 0:54
  • Yes, that's right. I had forgotten that we had that problem. Best to unserialize and reserialize, but brute force is faster. – ssaltman Mar 18 '16 at 11:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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