Search and replace image dimension within content in mysql?

7 Views

I have a wordpress blog with over 500 posts. The first post image is always 550px wide and the hight is between the 300px and 450px.

The blog design has been updated and the first post image is now 600px width and between the 350px and 500px high. I have manually downloaded the first images, and resized them from 550 to 600, with some minor quality lose.

Read More

The easiest thing would be to search and replace in the database. Look for width=”550″ and replace it with width=”600″, but then there is the problem with the height. This is not a fixed variable, like 550.

If wordpress images had their own table, I could simply do something like 

update  wp_posts
set height = height*1.09;

But in the case of wordpress, this cannot be done because the information is stored in post_content and looks like this: 

<img class="alignnone size-full wp-image-4352" title="Image Title" src="http://www.website.com/wp-content/uploads/2012/12/image.jpg" alt="image" width="550" height="351" />

How can the height be changed in proportion to the width, in mysql?

Post Views: 7

Related posts

Leave a Reply

1 comment

  1. This is a really tough question. But it’s a fun question too.

    Anyway, here it goes. To extract the width and height from the post_content images, create a view

    create view temp_images (id, width, height) as
select id,
       ExtractValue(post_content, '/img/@width'),
       ExtractValue(post_content, '/img/@height')
from wp_posts
where post_content like '<img%'
      and ExtractValue(post_content, '/img/@width') = 550;

    You don’t need the width in your case, but if you want to play with, it’s here.
    Now, you have the image post’s id and corresponding width and height. With this, you can update the image elements width and height in two steps

    update wp_posts, temp_images
set post_content = UpdateXml(post_content, '/img/@width', 'width="600"')
where wp_posts.id = temp_images.id;

update wp_posts, temp_images
set post_content = UpdateXml(post_content, '/img/@height',
                             concat('height="', round(height * 1.09), '"'))
where wp_posts.id = temp_images.id;

    and finally clean up, of course

    drop view temp_images;

    and last but not least, here’s a SQL Fiddle for testing and playing.