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? Link to comment https://forums.phpfreaks.com/topic/248772-keeping-track-of-deleted-rows/ 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. Link to comment https://forums.phpfreaks.com/topic/248772-keeping-track-of-deleted-rows/#findComment-1277703 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. Link to comment https://forums.phpfreaks.com/topic/248772-keeping-track-of-deleted-rows/#findComment-1277913 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.