0

I am trying to hack together something in mySQL to allow me to change from one plugin to another without having to do manual data entry for all of my products on my woocommerce store.

The goal is to change every meta_value with the meta_key _woofv_video_embed from this format https://youtu.be/JCdljysorMo to this format a:1:{s:3:"url";s:28:"https://youtu.be/JCdljysorMo";} where every product has a different video link that needs to be updated.

I have:

update `wp_postmeta` set `meta_value`= concat( a:1:{s:3:"url";s:28:", meta_value, ";} ) where `meta_key` = '_woofv_video_embed'

I am trying to use the concatenate function but I need everything except for commas to be a string literal I tried:

update `wp_postmeta` set `meta_value`= concat( 'a:1:{s:3:"url";s:28:"', meta_value, '";'} ) where `meta_key` = '_woofv_video_embed'

but I still got an error. What do I need to do differently? Also will I need back ticks around meta_value inside the concatenate function? I am new to mySQL so I am just looking at other peoples code but wasn't able to find and example that worked for this specific instance. Any help would be greatly appreciated. I am running this script by using "SQL" within phpMyAdmin if that changes the answer at all.

Thank you

2
  • are all of the URLs exactly 28 characters long?
    – Milo
    Apr 2, 2018 at 3:00
  • Yes they seem to be
    – nicolai
    Apr 2, 2018 at 3:28

2 Answers 2

1

I misplaced a single quote. This query worked for me and solved my problem.

update wp_postmeta set meta_value= concat( 'a:1:{s:3:"url";s:28:"', meta_value, '";}' ) where meta_key = '_woofv_video_embed'

0

Never manipulate the DB directly, instead write some small wordpress plugin to do it. The amount of gotchas when trying to change a serialized data "by hand" is ridiculous and the risk do not justify the small amount of time it seems like it will save you.

2
  • Don't suppose you could tell me how to do it the wrong way anyways so I can learn for myself :P?
    – nicolai
    Apr 2, 2018 at 6:28
  • you query all relevant posts, read the meta, change it, than save it to the DB. It is not very complex Apr 2, 2018 at 8:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.