Take the values that were changed in the bank and match the new values

Question

I have the following question. When making changes to a certain register, I need to keep the old information somewhere, I first thought of a log scheme, but it was time to take the old values, I could not identify which values were changed. What would be a way to do the following:

TELEFONE_ANTIGO -> NOVO_TELEFONE
EMAIL_ANTIGO -> NOVO_EMAIL

Only when the value is changed. Working with PHP.

Answer 1

Friend, I believe that the best way to do this is by own BD using triggers.
For example, let’s suppose you contact the table below:

|   CONTATO  |  
| id_contato |  
|  telefone  |  
|   email    |   

Then you need to create another table equal to CONTATO to store the history:

|   CONTATO_HIST  |  
| id_contato_HIST |
| id_contato      |  
| telefone        |  
| email           |  

And now create a Rigger BEFORE UPDATE in the contact table, like this:

CREATE TRIGGER contato_before_update
BEFORE UPDATE
   ON contato FOR EACH ROW
BEGIN

    INSERT INTO contato_hist(id_contato,telefone,email)
     SELECT * FROM contato where id_contato = NEW.id_contato;

END

So, before a table update happens CONTATO will run the code of your Rigger, which is inserted in the table CONTATO_HIST everything on the table CONTATO with the same id_contato that you are doing the update (NEW.id_contato). If you want you can add more fields in your table CONTATO_HIST and record the user who did the update, the date, time, whatever you want.
This way this history becomes transparent for your application, you don’t need to change anything in PHP.

I put an example on SQLFIDDLE