Solved

Generating an INSERT statement from a SELECT before deleting

Posted on 2010-11-11
12
231 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:ttheimer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
  • +2
12 Comments
 
LVL 13

Expert Comment

by:AngryBinary
ID: 34117383
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
 

Author Comment

by:ttheimer
ID: 34117905
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
 

Expert Comment

by:Shanksben
ID: 34117982
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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 13

Expert Comment

by:dsmile
ID: 34119779
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
 
LVL 13

Expert Comment

by:dsmile
ID: 34119787
Sorry I missed Shanksben's post.
What he said is what I mean logically deletion machanism.
0
 

Author Comment

by:ttheimer
ID: 34120454
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
 
LVL 13

Expert Comment

by:dsmile
ID: 34122586
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
 

Author Comment

by:ttheimer
ID: 34123423
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
 

Accepted Solution

by:
Shanksben earned 250 total points
ID: 34125863
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
 

Expert Comment

by:Shanksben
ID: 34125893
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
 
LVL 16

Expert Comment

by:CWS (haripriya)
ID: 35499618
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
PHP substring 3 54
Format Date 7 28
Change background images after 5 seconds. 12 48
Posting and receiving data with JSON in PHP 12 39
This article discusses how to create an extensible mechanism for linked drop downs.
This article discusses how to implement server side field validation and display customized error messages to the client.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question