Generating an INSERT statement from a SELECT before deleting

I want to give customers the ability to delete data on a web site (LAMP).  Before each deletion I would like to generate and store an INSERT statement that could be used to restore the data if the deletion was in error.

I would guess that this is a common need and I was hoping that there are known functions, instruction sets, or a php class that already exists to do this.  I haven't found anything in my searching so far.  Does anyone know of such a script?

Thanks.

Tom
ttheimerAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
ShanksbenConnect With a Mentor Commented:
In the hope that I understood your concern, the best approach I can suggest is that you use triggers.
I won't be able to give you the code (I know nothing about your database) but here is an idea from wikipedia:
http://en.wikipedia.org/wiki/Database_trigger#Triggers_in_MySQL

Using triggers may help maintain the integrity of your databases.
0
 
AngryBinaryCommented:
Here's a script that generates an INSERT statement for either an entire table or individual rows. You will have to tweak the parameters to decide which behavior the script will have:

http://snipplr.com/view/12003/generate-insert-statements/
0
 
ttheimerAuthor Commented:
Thanks AngryBinary.  Translating from TransSQL to PHP and SQL is more work than I was hoping for.  If I have to build my own script I may use this as a model but I still believe somebody has done this for the LAMP environment and I'll wait for a reference to appear.

Tom
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
ShanksbenCommented:
I don't know the requirements of your application but I suggest you mark the 'deleted' field as deleted. If the delete was an error,  remove the mark. Because if you perform a delete operation, and need to use INSERT, you'll need the data source again which will give you overwork.
If this didn't help, please enlight me
0
 
dsmileCommented:
What kind of error it might be?
If it's db's exception, then you should use transaction for your delete queries.

Something like this

begin transaction
try {
   delete
}
catch {
   rollback
}

For data safety, I think you should use logical deletion machanism instead of physical deletion machanism.
Each record has a delete flag.
When user deletes a record, the flag is updated to 'true' and that record won't be shown up again.
Then you have a script that automatically deletes deleted records periodically.
0
 
dsmileCommented:
Sorry I missed Shanksben's post.
What he said is what I mean logically deletion machanism.
0
 
ttheimerAuthor Commented:
Shanksben, if I were redesigning this application from scratch I would do exactly as you recommend.  But to retrofit a "deleted" field means that I would need to locate and modify every SELECT and UPDATE query that uses the modified table so that a record is not included in the query result if it has been marked as deleted.  Because I am just adding the restore functionality to an existing site I want to use a more localized approach.  Storing records as INSERT statements before deleting is a simple and universal approach.

dsmile, our customers have the ability to add and delete employees.  When they opt to delete we warn that it is usually a better option to de-activate but there are conditions in which deletion is the right choice.  But every month or two we will receive a call from a customer stating that they accidentally deleted an employee two days or two weeks ago.  Therefore a rollback is not an option because of the delay between the deletion and the recognition that a mistake had been made.
0
 
dsmileCommented:
Hope this helps http://www.tech-recipes.com/rx/1475/save-mysql-query-results-into-a-text-or-csv-file/

You run this command before each delete query with output filename named base on time & delete condition (maybe with customer info also, so you can trace back)
0
 
ttheimerAuthor Commented:
dsmile, INTO OUTFILE and INTO DUMPFILE both generate a new file with each use and the result is a tab delimited file (OUTFILE).  I'm looking for a "production" solution that can create the INSERT statement which I would programmatically drop it into a table each time a customer calls the delete option.  And I'm still hoping that someone else has worked out the code to construct the INSERT stmt.
0
 
ShanksbenCommented:
I also suggest you this quick tutorial (in case this is new to you):
http://www.mysqltutorial.org/create-the-first-trigger-in-mysql.aspx

Regards
0
 
CWS (haripriya)Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
All Courses

From novice to tech pro — start learning today.