d.ariel d.ariel - 24 days ago 7
MySQL Question

Using awk on sql database to change all iframe "width" and "height" sizes

I have a WordPress database with thousands of posts in it. I need some regex and help with the awk command to go through the database (after i mysql dump it) and change all the sizes to a uniform size like

width="600" height="350"


Currently It looks like this:

Some paragraph text here, like a description
[tab:Short Code Here]
<iframe width="200" height="180" src="https://www.youtube.com/embed/aD4grQXZPfg" frameborder="0" allowfullscreen></iframe>
[tab:Different Short Code Here]
<IFRAME SRC="http://other-videohost.com/embed-ksq9vn3gwc16.html" FRAMEBORDER=0 MARGINWIDTH=0 MARGINHEIGHT=0 SCROLLING=NO WIDTH=250 HEIGHT=300></IFRAME>


Result in the database should look like this:

Some paragraph text here, like a description
[tab:Short Code Here]
<iframe width="600" height="350" src="https://www.youtube.com/embed/aD4grQXZPfg" frameborder="0" allowfullscreen></iframe>
[tab:Different Short Code Here]
<IFRAME SRC="http://other-videohost.com/embed-ksq9vn3gwc16.html" FRAMEBORDER=0 MARGINWIDTH=0 MARGINHEIGHT=0 SCROLLING=NO WIDTH=600 HEIGHT=350></IFRAME>


So some additional information:

I'm going to dump the database with mysqldump then hopefully run a successful command with regex to change my target pattern without affecting images or other elements which are not iframes.
It would be fine to target all iframes since iframes are only used on the site for video embeds.

The server is running WordPress on ubuntu 14.04

Take notice that the width and height in some cases are behind the src and in others in front of the src such as in my example. Also we would want to make it non-case-sensitive.

Suggestions and solutions are welcome. Thank for any help guys.

Answer

With GNU awk for gensub() and word boundaries (\<):

$ awk -v w=600 -v h=350 -v IGNORECASE=1 '{
      $0 = gensub(/\<(width="?)[0-9]+("?)/,"\\1"w"\\2","g")
      $0 = gensub(/\<(height="?)[0-9]+("?)/,"\\1"h"\\2","g")
  } 1' file
Some paragraph text here, like a description
[tab:Short Code Here]
<iframe width="600" height="350" src="https://www.youtube.com/embed/aD4grQXZPfg" frameborder="0" allowfullscreen></iframe>
[tab:Different Short Code Here]
<IFRAME SRC="http://other-videohost.com/embed-ksq9vn3gwc16.html" FRAMEBORDER=0 MARGINWIDTH=0 MARGINHEIGHT=0 SCROLLING=NO WIDTH=600 HEIGHT=350></IFRAME>