Simple PHP & SQL Query - Update value

Question

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'");

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
– David Soussan, Commented Nov 6, 2015 at 1:15
@Fred-ii- I am getting this now UPDATE command denied to user 'main_webapp'@'localhost' for table 'pin_status_types' — LeviZoesch
– LeviZoesch, Commented Nov 6, 2015 at 1:19
@LeviZoesch use PDO or MySQLi, the mysql_* driver is deprecated. Good luck!! — angelcool.net
– angelcool.net, Commented Nov 6, 2015 at 1: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! — LeviZoesch
– LeviZoesch, Commented Nov 6, 2015 at 1:30

Community · Accepted Answer · 2017-05-23 12:03:59Z

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

edited May 23, 2017 at 12:03

CommunityBot

11 silver badge

answered Nov 6, 2015 at 1:53

IROEGBU

95816 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

LeviZoesch Over a year ago

I was able to see where I messed up and resolved it.

LeviZoesch Over a year ago

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?

IROEGBU Over a year ago

You are using an old version on MySQL, I'll update my answer to cover that.

LeviZoesch Over a year ago

phpMyAdmin 4.0.10.7 documentation »

LeviZoesch Over a year ago

Very nice. Success! Thank you for the great ideas. I will write everything to PDO prior to launch. TY

Collectives™ on Stack Overflow

Simple PHP & SQL Query - Update value

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related