How to change _wp_attachment_metadata specific value in SQL?

Question

i had to resize a lot of existing images in my upload folder (around 1k). After reuploading them, Wordpress of course, doesn't recognize the new dimensions. My approach was to just change the size in the _post_meta table. But this looks like this:

a:6{s:5:"width";s:3:"330";s:6:"height";s:4:"1067";s:14:"hwstring_small";s:22:"height='96' width='29'";s:4:"file";s:22:"2012/03/2-IMG_1540.png";s:5:"sizes";a:3:{s:9:"thumbnail";a:3:{s:4:"file";s:21:"2-IMG_1540-56x183.png"; ...

All I need to change is the "width" value of the first entry from "330" to sth. else. Although it looks like a dictionary to me I do not find a way to get access to that value in SQL.

The wp_update_attachment_metadata reference states that all data must be given as existing data will be wiped. That's the reason why I thought it would be easier to do it in SQL.

Answer 1

You could do this via PHP instead of SQL, just get the existing metadata and change what you need to, it will handle the serializing for you:

$newwidth = '250'; // or whatever it is
$attachments = get_posts(array('post_type'=>'attachment'));
foreach ($attachments as $attachment) {
    $id = $attachment->ID;
    $metadata = wp_get_attachment_metadata($id);
    $metadata['width'] = $newwidth;
    wp_update_attachment_metadata($id,$metadata);
}

But really, you may do better using the Regenerate Thumbnails Plugin which may fix this and regenerate the different thumbnail sizes at the same time.

Answer 2

What you are looking at is PHP serialized data saved as string in MySQL database. SQL simply won't have any clue on what to do with it.

The most important thing to remember about serialized data is that isn't trivial to edit. It embeds the length of values in the format and if the edits mismatch the length the whole data gets corrupted.

So typically the preferred method to manipulate such data is with PHP code itself.

Notably there are multiple solutions out there to rebuild the image data (plugins, WP CLI command) but it's usually for additional sizes, not original image. I am not sure if they would handle your case, but you might try. Just test first so that it's not making it worse.

Answer 3

you need to unserialize this string then you will get an array that you can edit. Then serialize this array again.

$attachment_data_array=unserialize('a:6{s:5:"width"; ...');
//change array values as you need 
//for example
$attachment_data_array['sizes']['thumbnail']['file']='newlink.jpg';
//then serialize again
$data=serialize($attachment_data_array);

Answer 4

There is "Fix Media Library" plugin doing that, it updates _wp_attachment_metadata field as a part of thumbs regeneration.

https://wordpress.org/plugins/wow-media-library-fix/