phpmyadmin Question posted in
The official documentation can be found here.

SQL Replace value in table after 90 days – Phpmyadmin

So to make my project GDPR friendly, I have to remove all customer data after 90 days.

This is how the sales table looks like (the input is just for example), using phpMyAdmin:

| ID | CUSTOMER  | EMAIL               | PRODUCT | DATE                | 
|  1 | Ken James | [email protected] | 4816419 | 2019-12-25 10:26:19 |
|  2 | Amy Wen   | [email protected]  | 6662341 | 2019-11-23 10:26:19 |
|  3 | Chris Pet | [email protected] | 4816419 | 2019-05-05 10:26:19 |

Is there somehow I can automatically replace the CUSTOMER and EMAIL with just XXXXXX if the DATE is older then 90 days?

Tags:

3

Answers


  1. 0

    You can use the built on Scheduler.

    But it has to be Enabled

    CREATE EVENT event1
ON SCHEDULE EVERY '1' DAY
STARTS '2020-08-17 00:00:01' -- should be in the future
DO
    UPDATE sales  
        SET EMAIL = 'xxxxxxxx', CUSTOMER = 'xxxxxxxx' 
    WHERE EMAIL <> 'xxxxxxxx' AND  CUSTOMER <> 'xxxxxxxx' AND `DATE` < DATE_ADD(NOW(),  INTERVAL -90 DAY);
    Login or Signup to reply.
  2. 0

    You can do as follows:

    UPDATE *table_name*
SET CUSTUMER = 'xxxxxxx', EMAIL = 'xxxxxxx'
WHERE CURRENT_DATE() - DATE > 90;

    CURRENT_DATE() return the date of the current date
    (You can also use GETDATE() instead of CURRENT_DATE())

    Login or Signup to reply.
  3. 0

    This is perhaps too long for a comment.

    90 days seems a bit aggressive based on what I know about GDPR.

    That said, what you are doing is quite dangerous, at least from an analytic and financial perspective. Knowing that a customer has made multiple purchases over time can be quite important. And GDPR is pretty explicit that you can keep historical information about active (and recently former) customers. (Of course, I’m not a lawyer and even if I were, you should be very suspicious about free advie over the web.)

    That said, your sales table should be designed to be privacy compliant. How? Store the customer information in a different table.

    So, the sales table should look like:

    | ID | CUSTOMERID | PRODUCT | DATE                | 
|  1 |     1      | 4816419 | 2019-12-25 10:26:19 |
|  2 |     2      | 6662341 | 2019-11-23 10:26:19 |
|  3 |     3      | 4816419 | 2019-05-05 10:26:19 |

    This table is perfectly fine from a privacy perspective, assuming that the CUSTOMERID is strictly used internally.

    You should then have a CUSTOMERS table:

    | CUSTOMERID | CUSTOMER  | EMAIL               | LASTSALEDATE                | 
|      1     | Ken James | [email protected] | 2019-12-25 10:26:19 |
|      2     | Amy Wen   | [email protected]  | 2019-11-23 10:26:19 |
|      3     | Chris Pet | [email protected] | 2019-05-05 10:26:19 |

    This is the table where you can replace the name, address, email, phone number, and so on with NULL values (or 'XXXX' if you prefer).

    This structure allows you to maintain the internal history of repeated purchases and purchase dates and so on — even when the data has been anonymized. By putting all the PII in one place, it is easier to maintain, audit, and ensure that it meets the requirements of GDPR and other privacy laws.

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

Back To Top
Search