What could be done to get the old values back in the table after execution of any update query on it?

Question

What could be done to get the old values back in the table after execution of any update query on it ?

I am using PHP and Mysql DB

Answer 1

If you're using transactions and haven't committed the transaction, you just do a rollback.

If you aren't using a transactions or have committed your transaction then you're reliant on one of:

• backups
• application-specific functionality eg a trigger on the table to log updates that will allow you to reconstruct the old values.

There is no other native mechanism.

Answer 2

By default: nothing. There is no way.

Unless maybe:

• You were in a transaction (used BEGIN) and did not yet COMMIT. You can then ROLLBACK.
• You started the statement with setting AUTOCOMMIT to 0. See above.
• You have got a recent backup of your row/table/schema/database. For example, using MySQLDump.
• You have got a recent backup of your filesystem where your database resides. For example, tape drives or a networker.
• You have a logfile with statements (eg the mysql query log or the binary log) enabled. Then you could start with an empty table and re-execute the commands that filled the table.
• The database is mirrored on another system and the mirror is not yet updated (be fast...)

But otherwise, nope, sorry. The information is lost.

You should start thinking about doing one of the things stated above (backups are a MUST!, logging is adviseable while developing if your server can handle it) to avoid these situations in the future.