What is the structure of the “Featured Image” field in the DB?

3 comments

1. 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.

2. 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

3. 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/

Comments are closed.