Jump to content

PHP MySQL Update Serialised Data


jarvis

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.