Replace value from array with text if exist before writing it to a MySQL table

[RichardB-holland]

Hi all, hope some can help because this is giving me more grey hairs than I allready have.

 

I am trying to solve the following:

I'm pulling in JSON data, decoding it to a array which I write to a database. However in the array are social security numbers that I don't want to store (privacyregulations). So I need to check if a row has a social security number and if it has one I want to replace it with 'anonymized'.

I have got it to work successfully on a local Xampp test environment using the following script (extract off course):

//first anonymize the social security number (ssn) if exists

$data = json_decode($jsondata, true);

foreach($data as &$ssn){
    
            if(!empty($ssn['ssn'])) {
            $ssn['ssn']= '0';$ssn['ssn']= 'anonymized';
            }
    }

// then fill the tables

foreach($data as $row)

{

$insertstatement = mysqli_query($link,"INSERT INTO $tablename (personid, type, ssn) VALUES ('".$row["personid"]."', '".$row["type"]."', '".$row["ssn"]."') ON DUPLICATE KEY UPDATE type = VALUES(type), bsn = VALUES(ssn)");
}

 

This leads to a filled table where the ssn's are filled with 'anonymized' if they exist in the array and empty if it does not exist for that row. This is exactly what I want ;-).

Personid        type    ssn
1                     a    
2                     a        anonymized
3                    b    
4                    a        anonymized

 

However, when I run the same script on a Lamp production environment (lamp stack on an Azure server) it does not work. Then the scripts results in totally ignoring the whole row that contains a ssn??

Personid        type    ssn
1                     a    
3                    b    
 

Hope someone can help to get this working on my production environment as well.

 

Richard

 

[Barand]

Are the table structures the same in both instances, in particular the definition of the ssn column?

[RichardB-holland]

Hi Barand, thanks for the reply.

There is no difference between de table structures as far as I can see. I have used exactly the same SQL script for creating the tables in both the Xamp and Lamp stack.

Richard

[Barand]

what is the structure?

[RichardB-holland]

This is the structure of the two databases (sorry for the Dutch PHPMyAdmin language)

 

I have attached an image of the structure on Xampp stack as well as on the Lamp stack.

(The Dutch ssn is a 11 digit number)

[Barand]

[RichardB-holland]

Excuse me😟 For posting this question I replaced 'bsn' (which stands for burgerservicenummer, the Dutch social socialsecuritynumber) to 'ssn'.

I overlooked the instance you found, but that's not the problem why it is not working. In my PHP scripts it says 'bsn' everywhere and the column in the table is also called 'bsn'.

Richard.

 

[mac_gyver]

apparently the INSERT part of the query is failing for these new rows. do you have any error handling for the database statements so that you would know if and why they are failing?

temporarily add the following to your code to get php to report and display all errors, including database statement errors -

// report all php errors
error_reporting(-1);
// display any reported errors
ini_set('display_errors', '1');
// use exceptions for mysqli errors, which php will then report and display via an uncaught exception
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

 

[RichardB-holland]

Yes found it!!

This is the error I get (read 'ssn' where it says 'bsn' due to my translation for asking this question in the forum).

Fatal error: Uncaught mysqli_sql_exception: Data too long for column 'bsn' at row 1 in /var/www/html/Requests/Scripts/wsrequest-person.php:143 Stack trace: #0 /var/www/html/Requests/Scripts/wsrequest-person.php(143): mysqli_query(Object(mysqli), 'INSERT INTO per...') #1 {main} thrown in /var/www/html/Requests/Scripts/wsrequest-person.php on line 143

Silly me, I used the dutch translation for 'anonymized' which is 'geanonimiseerd' and this exeeds the maximum length of 11 characters for the column.

I now found out that in the Xamp stack the same script did not throw in any errors but the word 'geanonimiseerd' was stripped to 'geanonimise' (which is 11 characters long). In the Lamp stack the row was completely ignored. Maybe this has something to do with the PHP version.

Anyway, it works in the production environment now. Thank you so much for pointing me in the right direction 🎉

[mac_gyver]

24 minutes ago, RichardB-holland said:

Maybe this has something to do with the PHP version.

no. it's due to the database server sql strict mode setting. on your localhost system, it is set to truncate/limit out of range data values without producing an error. on the live system, it is set to produce an error for out of range values.

Edited May 17, 2021 by mac_gyver

[Barand]

If you posted the code you were actually using we might have got there a lot quicker.