Need to change customfields and clientdetails for an invoice

[Esi]

Hi all

i need to solve a problem with a wrong invoice ( obv paid from my client ) due a wrong data insert in registration from client. in my 7.5 whmcs version there's checked "Store Client Data Snapshot" option and it's working correctly, i need so ( i think that is the only way ) modify wrong data from db table mod_invoicedata.customfields and mod_invoicedata.clientdetails but value that i need to change is into a big array and i don't know how touch correctly it.

Someone has some suggestions or workaround to solve this problem that i think could be really common after whmcs upgrade system and changing ?

Thank you so much.

Regards

 

 

[Kian]

Data in mod_invoice data are stored serialized. First off open phpMyAdmin and run this query:

SELECT customfields FROM mod_invoicedata WHERE invoice = '{YOUR_INVOICE_ID}';

You'll get something like this:

a:2:{i:0;a:3:{s:2:"id";i:1;s:9:"fieldname";s:6:"VAT ID";s:5:"value";s:11:"I_LOVE_SARA";}i:1;a:3:{s:2:"id";i:4;s:9:"fieldname";s:6:"Tax ID";s:5:"value";s:6:"filler";}}

Copy & paste this mess in any online editor like this one in box 1 then edit values accordingly in box 2. For example change I_LOVE_SARA with I_LOVE_BERLUSCONI (I know, I'm grotesque 😞). Once finished copy the content of box 3 and use it to replace the old value with an UPDATE query:

UPDATE mod_invoicedata SET customfields = 'a:2:{i:0;a:3:{s:2:"id";i:1;s:9:"fieldname";s:6:"VAT ID";s:5:"value";s:17:"I_LOVE_BERLUSCONI";}i:1;a:3:{s:2:"id";i:4;s:9:"fieldname";s:6:"Tax ID";s:5:"value";s:6:"filler";}}' WHERE invoiceid = '{YOUR_INVOICE_ID}'

Now you no longer love Sara in PDF.

Edited November 22, 2018 by Kian

[Esi]

Hi Kian,

thank you for your answer and to help me, unfortunately i can't reach goal with this query because  i have two problem, first that i need to make a change from your first query using below

SELECT clientsdetails FROM mod_invoicedata WHERE invoiceid = '{invoice_number}' 

then if i follow your guide ans suggestion and copy the content in box 3 for the new query i have a syntax error as below

Syntax error in SQL query near ''a:2:{i:0)' on line 1

the query is

UPDATE mod_invoicedata SET clientsdetails = "a:2:{i:0;s:16:"I_LOVE_SARA";i:1;s:16:"I_LOVE_BERLUSCA";}" WHERE invoiceid = '{invoice_number]'

😕

 

[Kian]

Ups, my bad. Use single quotes:

UPDATE mod_invoicedata SET clientsdetails = 'a:2:{i:0;s:16:"I_LOVE_SARA";i:1;s:16:"I_LOVE_BERLUSCA";}' WHERE invoiceid = '{invoice_number]'

 

[Esi]

Hi Kian,

I have a different output from your query that from

SELECT customfields FROM mod_invoicedata WHERE invoice = '{YOUR_INVOICE_ID}';

need to be

SELECT clientdetails FROM mod_invoicedata WHERE invoice = '{YOUR_INVOICE_ID}';

anyway i changed data from db that i need. Thank you for your time and help that was very appreciated