PHP MySQL Update Serialised Data

[jarvis]

Hi All,

 

I'm really hoping someone can help!

 

I have a table which contains serialised data.

 

I need to run a query which updates 2 elements

 

The table structure is this:

`options` (`option_id`, `option_name`, `option_value`, `autoload`) 

Then within this I have as an example:

(1619, 'woocommerce_paypal_settings', '
a:18:{
s:7:"enabled";
s:2:"no";
s:5:"title";
s:6:"PayPal";
s:11:"description";
s:85:"Pay via PayPal; you can pay with your credit card if you don''t have a PayPal account.";
s:5:"email";
s:35:"your@email.com";
s:8:"testmode";
s:3:"yes";
s:5:"debug";
s:2:"no";
s:8:"advanced";
s:0:"";
s:14:"receiver_email";
s:35:"your@email.com";
s:14:"identity_token";
s:0:"";

I need to update email and receiver_email with a new value supplied ($newEmail)

 

I'm not 100% sure but think you have to unserialise, make the amend and re-serialise is that correct?

 

Or is there a better way of handling this?

 

Thanks in advanced

[gizmola]

It would be much faster to do an update of the value directly with sql.

 

Your question isn't clear as to which column the serialized string is stored, but my guess would be that it's in `option_value`?

 

If I understand you correctly,  you are looking for the sub-string

     's:14:"receiver_email";'

Mysql has the REPLACE function, which will do a direct replacement.

UPDATE tbl SET column_name = REPLACE(column_name, 'reciever_email', $newEmail)

I'm not clear if in your example reciever_email is a literal constant, or some random actual email.

 

If there is a constant available in the serialized structure that will let you always find the exact part of the string you need to find, then REPLACE is your best option.

 

The same idea would conceptually also work for email, if your example is literally the way the data appears in each and every row.

 

If however, there is some variation, then your only option will be write a program that reads each row, unserializes the data into a variable, updates it, reserializes and does an update of that value.  

 

Mysql does allow for regular expressions in searches, but unfortunately in this case, not for replacing values.  

 

This SO thread does a good job covering some of the potential work-arounds and variables involved in an "in database" regex based replace feature  (for example, MariaDB has this feature)  that might be applicable to your environment.

[jarvis]

Thanks @gizmola 

 

Apologies for not being clear! 

 

Yes, the column with the serialized data is in option_value

I need to update 2 parts:

1) email

2) receiver_email

 

The data will not be a constant (i.e. a different email address)  but the field name (email and receiver_email) will be constant.

The new value will be from $newEmail

 

Does that help?

 

Thanks again

[gizmola]

Yes, well, if that's the case, then you're going to have to read each row in, in a loop, unserialize it into an object, change those values in the php object, serialize and store the data back with an UPDATE statement.

[maxxd]

Is this not an option you can set from the WordPress dashboard? Admittedly I've not used it, but I assume WooCommerce PayPal plugin would have a settings or option page accessible to the site administrator.