dadamssg87 Posted October 9, 2011 Share Posted October 9, 2011 Right now, my users can delete pieces of content that they've created and the rows really do get deleted from the database. I'd like to change this by adding a "deleted" column in my database table. The column datatype needs to hold the date/time of deletion. So i'm thinking datetime, timestamp, or int(11). I'm just not sure which datatype is ideal. I was just going to set the datatype to int(11) to store the integer value of the timestamp of when the row was "deleted". Then i could just query non-deleted items by "select * from table where deleted != '0'". But i think i should be using datetime or timestamp. I don't really understand the difference between the two. What do the mysql wizards suggest? Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted October 10, 2011 Share Posted October 10, 2011 I dunno about the wizards, but I would use a timestamp field with default value of NULL and no on update action. Update it it with CURRENT_TIMESTAMP() when something is deleted, then select the info by NOT using * with the WHERE <timestamp_filed> IS NOT NULL. Using a date time formated field lets you report against the filed (as I am assuming you want to do or else you would just be using a flag field) without having to convert values to get the ranges that you are looking for. Quote Link to comment Share on other sites More sharing options...
fenway Posted October 10, 2011 Share Posted October 10, 2011 Using NULLs with date ranges is annoying, but you can store all zeroes. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.