Preview result of update/insert query without comitting changes to database in MySQL?

Question

I am writing a script to import CSV files into existing tables within my database. I decided to do the insert/update operations myself using PHP and INSERT/UPDATE statements, and not use MySQL's LOAD INFILE command, I have good reasons for this.

What I would like to do is emulate the insert/update operations and display the results to the user, and then give them the option of confirming that this is OK, and then committing the changes to the database.

I'm using InnoDB database engine with support for transactions. Not sure if this helps but was thinking down the line of insert/update, query data, display to user, then either commit or rollback transaction?

Any advise would be appreciated.

Answer 1

The easier method would be something like that.

On the preview page :

1. buffer your php output
2. start a transaction
3. do your inserts and updates
4. select your data
5. output it however your want
6. rollback

Then on the confirmation page :

1. buffer output
2. start a transaction
3. do inserts and updates
4. commit
5. output what you want

Answer 2

You could import the data into a separate "import" table. Show the user the result form the table. If he clicks "OK", copy that data to the main table and drop the import table. If he clicks "Cancel", just drop the import table.

The import table name should be chosen on a by-user basis so different users don't overwrite their tables.

Transactions won't help you here because the transaction will roll back when the PHP import script is finished.