0

I have 2000 products with row that is using serialized data and I need to update specific string

this is the row name data

a:35:{s:11:"expire_days";s:3:"30d";s:12:"trial1_price";s:0:"";s:11:"trial1_days";s:0:"";s:12:"is_recurring";s:0:"";s:10:"start_date";s:0:"";s:5:"terms";s:24:"$150 for 1 Per license";s:12:"rebill_times";s:0:"";s:15:"paypal_currency";s:0:"";s:4:"##11";N;s:3:"url";s:0:"";s:8:"add_urls";s:0:"";s:4:"##12";N;s:5:"scope";s:0:"";s:5:"order";s:4:"1010";s:11:"price_group";s:1:"7";s:13:"renewal_group";s:2:"28";s:14:"need_agreement";s:0:"";s:13:"require_other";a:1:{i:0;s:0:"";}s:16:"prevent_if_other";N;s:4:"##13";N;s:19:"autoresponder_renew";s:0:"";s:16:"dont_mail_expire";s:0:"";s:13:"joomla_access";s:2:"36";s:10:"files_path";s:108:"products/Boxes8.zip|Box 8
products/Boxes9.zip|Box 9";s:14:"download_count";s:0:"";s:18:"download_unlimited";}

and only thing I need changed is
s:24:"$150 for 1 Per license";

any help is appreciated.

Benn
  • 4,840
  • 8
  • 65
  • 106

2 Answers2

1

You should probably SELECT the row, make your changes, then UPDATE with the new value. The answer to this question may be helpful if you need to do this database side.

How to do a regular expression replace in MySQL?

Community
  • 1
  • 1
Joshua Martell
  • 7,074
  • 2
  • 30
  • 37
0

If you want to replace the value of that single field with something else, you can use the following query:

UPDATE table SET col = CONCAT(
  LEFT(col, LOCATE('s:24:"', col) + 5),                  -- up to and including the opening quote
  'Now for free',                                        -- new replacement text
  SUBSTR(col, LOCATE('"', col, LOCATE('s:24:"', col)+6)) -- closing quote and everything after that
) WHERE col LIKE '%s:24:"$150 for 1 Per license"%'

Note that there is potential for trouble: if the value of one of your fields should end in 's:24:', then that combined with the closing quote would get misinterpreded as the location you're looking at. I consider this risk unlikely, but if you want to play it safe, you might want to check for that with an elaborate regular expression that can deal with quoted strings and escaped quotes.

MvG
  • 57,380
  • 22
  • 148
  • 276
  • i had similar sql and tested it whit bad results , quotes gone and other mess, this is tricky – Benn Aug 12 '12 at 20:37