Deleting a record from a table

[PaulRyan]

I was wondering, what is the common practice for deleting a record from the database?

 

I can personally think of 2 ways I have done it in the past.

 

You send a delete query, whether the record exists or not.

 

Or you can first query the database, to see if the record exists, then if it does, delete it.

 

Just looking for some external insight into this

[DaveyK]

I personally always run a DELETE query, but I'm interested as to how others (better programmers?) do this.

 

When I need to let the user if the delete was a success, I check *_affected_rows(). If thats 0, the *_query() would still return as if it was a success. If affected rows returns 0, it means the delete didnt actually occur. Just the way I tried it. Mostly because I thought it makes sense, I'm no pro.

[PaulRyan]

I too do it that way, when I want the user to see a response, but for personal things, I just expect the delete to run and work.

 

I used to select first, if it existed, delete it, if not, then display an error saying "You cannot delete what does not exist" or something of that nature.

[DaveyK]

Performance wise, I wouldnt know what the best thing is. But im guessing that the extra query counts more heavily then *_affected_rows().

[PaulRyan]

*_affected_rows() might not mean it didn't occur because it didn't exist, could be because of an error or something.

 

It would only be heavier if the row exists, because you then have to delete the record.

 

I think there's a difference between something not existing and the query not working as expected, which is useful for the end user.

 

I might be thinking about this from too far outside the box if I'm honest, your method is logical and normally what I'd think of doing.

[DaveyK]

if the *_query() fails I wont even run *_affected_rows().

[PaulRyan]

I totally missed the concept of error catching when thinking about this, that was where I was going wrong.

 

Thanks for clearing that up for me

[KevinM1]

There's another option: not actually deleting the row at all, and instead marking it as deleted via a flag column.  Very useful if you want to remove data publically but keep it for your own internal records.  Our old SMF forums here did that.  Deleted posts/threads weren't actually deleted, but rather moved to a staff-only section.  It allowed us to keep a history of disruptive and/or abusive members.

[PaulRyan]

  On 3/22/2013 at 2:20 PM, KevinM1 said:

There's another option: not actually deleting the row at all, and instead marking it as deleted via a flag column.  Very useful if you want to remove data publically but keep it for your own internal records.  Our old SMF forums here did that.  Deleted posts/threads weren't actually deleted, but rather moved to a staff-only section.  It allowed us to keep a history of disruptive and/or abusive members.

 

Well now there's a thought, think I might just do that from now on then. It's always good to keep a record of things.

I remember doing this some time ago, dunno why I don't do it anymore.

 

Thanks KevinM1.

[DaveyK]

To be completely honest, I just checked my codes and I use a delete query one time, to delete an instance of an image if it somehow didnt get uploaded.

 

Other than that, I am using status columns to mark things as deleted.