1

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.

  • i think you could do this with SQL after all, especially since you are only changing one value... the s:3:"330" means the length of the string value is 3 and the string value is 330... why not change one to test it first. – majick Mar 15 '16 at 10:57
  • My SQL knowledge is basic. So I do not know how to select the values and change them in SQL. I guess it will be a super long nested SQL statement. – Unfu Mar 20 '16 at 18:53
1

You could do this via PHP instead of SQL, just get the existing metadata and change what you need to, it will handling 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 use the Regenerate Thumbnails Plugin which may fix this and regenerate the different thumbnail sizes at the same time.

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.

  • Thank you very much for pointing me into the right direction – Unfu Mar 15 '16 at 9:19
0

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);
0

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/

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.