2

I currently have a query where I delete a record on call, however after other consideration I would rather just update the record on a is_deleted basis so that I can always have a record of whats been in the system, and or undelete that record at a later time.

My current query:

$delete=mysql_query("DELETE FROM pin_status_types WHERE pinstatus_id='$delete'");

Instead of deleteing the record, i would rather change a value in a column from 0 to 1.

0 = false (not deleted) 1 = true (is deleted)

Correct me if I wrong, but wouldnt I do (Note; I added table. and column. to for note purposes.) something like below to achieve what I am after?

$delete=mysql_query("UPDATE table.pin_status_types SET column.is_deleted = 1 WHERE pinstatus_id='$delete'");
levi
  • 1,566
  • 3
  • 21
  • 37
  • Wow! Total shot in the dark. Thanks :) – levi Nov 06 '15 at 01:12
  • I always add deleted_date and deleted_by for a full audit trail. The final thing is to add a check that the row has not already been deleted. In fact you can just use the deleted_date to indicate the deleted status and do without the boolean column. NULL = not deleted, date = deleted. Remember you are going to have to add a check to every SELECT and UPDATE query as well. – David Soussan Nov 06 '15 at 01:15
  • @Fred-ii- I am getting this now UPDATE command denied to user 'main_webapp'@'localhost' for table 'pin_status_types' – levi Nov 06 '15 at 01:19
  • @LeviZoesch use PDO or MySQLi, the mysql_* driver is deprecated. Good luck!! – angelcool.net Nov 06 '15 at 01:19
  • @DavidSoussan Ah thank you for the advice. I like the idea of audit trails... building an web application and will have multi-admin so having that is a great idea :) thank you! – levi Nov 06 '15 at 01:30
  • Hard to say with so little code and not knowing which API you're using to connect with. – Funk Forty Niner Nov 06 '15 at 01:33

1 Answers1

1

Update your query to:

mysql_query("UPDATE pin_status_types SET is_deleted = 1, date_deleted = NOW() WHERE pinstatus_id = '{$delete}'"

Also, as David suggested in comments, you might want to add timestamp for when a record was deleted for audit purposed.

Update: changed query to cover the issue raised in your comment. Make the date_deleted column default to 0 instead on CURRENT_TIMESTAMP. See this question for more details on that.

Please learn about and use PDO going forward

Community
  • 1
  • 1
IROEGBU
  • 948
  • 16
  • 33
  • I was able to see where I messed up and resolved it. – levi Nov 06 '15 at 02:02
  • Oops, However I am adding deleted_on with timestamp of now. I get Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause because I already have a timestamp now for "created" column. Suggested work around? – levi Nov 06 '15 at 02:03
  • You are using an old version on MySQL, I'll update my answer to cover that. – IROEGBU Nov 06 '15 at 02:04
  • phpMyAdmin 4.0.10.7 documentation » – levi Nov 06 '15 at 02:05
  • Very nice. Success! Thank you for the great ideas. I will write everything to PDO prior to launch. TY – levi Nov 06 '15 at 02:30