Don't Brake My Art

Replacing WordPress Shortcode with SQL Rewrite after WP 5.5 Update

Published · Updated

A friend with an entertainment blog had a lovely WordPress theme for the longest time called Ciola. It was an e-zine theme with lots of customizations and rich features, but after the WordPress 5.5 update, the WP text editor and tags field stopped working. After a lot of searching, turns out WordPress has made some changes that effect backwards compatibility. Full article explaining the change is here.

Suffice to say, that theme either needed to be updated or removed. (Uploading this plugin will provide backwards compatibility while also producing errors messages to let you know where the issues lie, but it’s not a forever fix.) Easy! Remove it!

Except that removing the theme also removed all of the Vimeo and YouTube videos! Why? Because the theme’s video feature allowed you to select a video icon in the visual editor, paste the full video URL into a field, and then removed everything but the video ID to displayed it on the page using shortcode. So everywhere that the video was once present, now looked like:

[embedvideo id="VIDEOID" website="youtube"]

OR

[embedvideo id="VIDEOID" website="vimeo"]

I tried finding something easy – as in, a plugin that used that exact shortcode to install and activate; or some php code that would fix it. I didn’t even know yet what was creating the problem, but I knew her theme was several years old without an update because the developers had ceased updating it. My husband took a look at it and said the easiest fix would be to take the parameters around the video ID and replace it through a database (SQL) rewrite using the proper embed code. YouTube? Easy. The ID is in the same place for every video, so removing the beginning shortcode:

[embedvideo id="

and replacing with

<iframe src="https://www.youtube.com/embed/

and also removing the end shortcode

" website="youtube"]

and replacing with 

" width="560" height="315" frameborder="0" allowfullscreen="allowfullscreen"></iframe>

should entirely remove and replace the shortcode with the proper YouTube embed html. Easy! Uh…

Well, it doesn’t exactly work that way for Vimeo. Also, you have to write the right code, because a database rewrite like that – if incorrect – can really screw up your site. So he wrote out some instructions that should have been perfect, but the Simulate Query function returned some character errors.

What happened next were about 3 days of reaching out to people, Stackoverflow.com, etc. to try to find out why the code was producing these errors. In the meantime I had to manually change the Vimeo embeds because Vimeo is different and that code was not going to work at all.

Finally, we used the following code to rewrite the shortcode on the database with perfect results!

** DISCLAIMER **

YOU HAVE TO BACK UP YOUR DATABASE. You have to back up your entire site and you have to run code that does not produce an error. The SQL version we worked on is 5.6, so test and test and test and back up everything in case you need to upload it to restore your site.

UPDATE
  wp_posts
SET
  post_content = REPLACE(
    post_content,
    "[embedvideo id=\"",
    "<iframe src=\"https://www.youtube.com/embed/"
  )
WHERE
  post_content LIKE "%website=\"youtube\"%";


UPDATE
  wp_posts
SET
  post_content = REPLACE(
    post_content,
    "\" website=\"youtube\"]",
    "\" width=\"560\" height=\"315\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\"></iframe>"
  )
WHERE
  post_content LIKE "%website=\"youtube\"%";

We didn’t escape brackets, we had to change single quotations to double and we had to escape quotations within the string to create the code that didn’t produce an error.

It worked and in a single stroke (and 3 days of stress and overthinking and panic and crazy doubt) it rewrote the shortcode and we didn’t have to find some “fix” that 1) was so much work it meant days of rewriting theme code, or 2) would eventually lead to the same issue down the road.

Run it by an expert, if you can, but I ran it by several and in the end, no one wants to pull the trigger on saying, “Yes! This is correct,” just to watch you do it and complain that you were given bad advice. No one could tell me it was right. Back up your website files and database, make sure you can upload the backup (we ran into that issue, too – the backup was very large), run it through an SQL validator and ‘Simulate Query’ before committing to the final change, and just generally make sure you don’t do it without precaution. Don’t run the code if you get an error. Double check, triple check, quadruple check your code. This worked for us but again, the database was version 5.6 and we sweated bullets over it before committing!

You may also like...