Question 20826 in Mysql Question posted in
The official MySQL documentation can be found here.

Replace string in column value MYSQL

Hi i have a table named data_table with columns ID | DATA

Id is integer and Data stored like this:

a:19:{s:10:"store_name";s:9:"STORENAME";s:6:"social";a:7:{s:2:"fb";s:0:"";s:7:"twitter";s:0:"";s:9:"pinterest";s:0:"";s:8:"linkedin";s:0:"";s:7:"youtube";s:0:"";s:9:"instagram";s:0:"";s:6:"flickr";s:0:"";}s:7:"payment";a:2:{s:6:"paypal";a:1:{i:0;s:5:"email";}s:4:"bank";a:0:{}}s:5:"phone";s:0:"";s:10:"show_email";s:2:"no";s:7:"address";a:6:{s:8:"street_1";s:0:"";s:8:"street_2";s:0:"";s:4:"city";s:0:"";s:3:"zip";s:0:"";s:7:"country";s:0:"";s:5:"state";s:0:"";}s:8:"location";s:0:"";s:6:"banner";i:0;s:4:"icon";i:0;s:8:"gravatar";i:0;s:14:"show_more_ptab";s:3:"yes";s:9:"store_ppp";i:12;s:10:"enable_tnc";s:3:"off";s:9:"store_tnc";s:0:"";s:23:"show_min_order_discount";s:2:"no";s:9:"store_seo";a:0:{}s:24:"dokan_store_time_enabled";s:2:"no";s:23:"dokan_store_open_notice";s:0:"";s:24:"dokan_store_close_notice";s:0:"";}

Also i have another table named user_stores ID | STORE
Id is integer and store is string format.

I want to make trigger on update when table user_stores change some store name then change the a s:9:"STORENAME

s:9 is the length of the value, in our example is STORENAME

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The final answer who works is:

    BEGIN
  UPDATE data_table as b
  SET b.DATA = CONCAT(SUBSTRING_INDEX(b.STORE, 'store_name', 1),'store_name";s:',LENGTH(new.STORE),':"',new.STORE,'";s',SUBSTRING_INDEX(b.STORE, SUBSTRING_INDEX((SUBSTRING_INDEX(b.STORE, 'store_name', -1)), ':',3), -1))
  WHERE b.ID=new.ID
END;

  2. 0

    Assuming you’re using MySQL, you can create a trigger on the user_stores table to update the data_table when a store name changes. Here’s an example:

    CREATE TRIGGER update_storename_trigger
AFTER UPDATE ON user_stores
FOR EACH ROW
BEGIN
  UPDATE data_table
  SET DATA = REPLACE(DATA, CONCAT('s:', LENGTH(OLD.STORE), ':"', OLD.STORE, '"'), CONCAT('s:', LENGTH(NEW.STORE), ':"', NEW.STORE, '"'))
  WHERE DATA LIKE CONCAT('%s:', LENGTH(OLD.STORE), ':"', OLD.STORE, '";%');
END;

    This trigger will be executed after the user_stores table is updated and will update the DATA column of the data_table if the store name has changed. The REPLACE function is used to replace the old store name with the new store name in the serialized data stored in the DATA column. The WHERE clause is used to search the data table rows that contain the previous store name in the serialized data.

    Note that this trigger assumes that the serialized data in the DATA column is in the serialized format. If the data is in a different format, the trigger will have to be modified.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search