4 Replies - 1925 Views - Last Post: 09 April 2009 - 10:58 PM Rate Topic: -----

#1 ninethousandfeet@msn.com  Icon User is offline

  • D.I.C Regular

Reputation: 4
  • View blog
  • Posts: 310
  • Joined: 09-February 09

delete records in db after certain time period

Post icon  Posted 31 March 2009 - 03:04 PM

hello,

i have a comment/chat system that is a dedicated table in my db. the users are allowed to enter current conversations on someone else's profile or they can start a new topic. i don't allow the users to delete the conversation b/c then there might be a lot of other users involved and the whole convo would go away...
so, is there a way for me to set something up so any conversation where convo_topic = convo_topic and 0 posts have been made in the last 10 days for example? ... if 5 people are in a conversation with the topic, 'welcome to my site'... and not one of these five people enter a comment in 'welcome to my site' for 10 days, then 'welcome to my site' is deleted.

thank you!

Is This A Good Question/Topic? 0
  • +

Replies To: delete records in db after certain time period

#2 cardographer  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 15
  • Joined: 24-March 09

Re: delete records in db after certain time period

Posted 31 March 2009 - 08:34 PM

How you schedule the cleanup depends on what platform you are on.
On Sql Server you would likely use a SqlAgent job which would run, say nightly at midnight, and execute a statement such as:

DELETE 
FROM Convo WHERE ConvoId IN (
  SELECT ConvoId 
  FROM Post GROUP BY ConvoId
  HAVING MAX(postdate) < DATEADD(d, -5, GetDate())
)


Modify the date functions for your platform...

This post has been edited by cardographer: 31 March 2009 - 08:35 PM

Was This Post Helpful? 0
  • +
  • -

#3 ninethousandfeet@msn.com  Icon User is offline

  • D.I.C Regular

Reputation: 4
  • View blog
  • Posts: 310
  • Joined: 09-February 09

Re: delete records in db after certain time period

Posted 01 April 2009 - 12:37 PM

i am using MYsql, would that still work with that server?
Was This Post Helpful? 0
  • +
  • -

#4 cardographer  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 15
  • Joined: 24-March 09

Re: delete records in db after certain time period

Posted 03 April 2009 - 04:30 PM

View Postninethousandfeet@msn.com, on 1 Apr, 2009 - 10:37 AM, said:

i am using MYsql, would that still work with that server?


No, the date functions would be different. I'm not a mysql expert, but I think GetDate() becomes Now() and DateAdd becomes Date_Add with different params. You should be able to find some examples here: http://dev.mysql.com...nction_date-add
Was This Post Helpful? 1
  • +
  • -

#5 ninethousandfeet@msn.com  Icon User is offline

  • D.I.C Regular

Reputation: 4
  • View blog
  • Posts: 310
  • Joined: 09-February 09

Re: delete records in db after certain time period

Posted 09 April 2009 - 10:58 PM

okay, so i need to delete the conversation by the comment_title where the date is past 10 days... here is what i know, but i'm unsure about writing something about the date... any suggestions?
$deleteSQL = "DELETE FROM testComment WHERE comment_date < DATE_SUB(comment_date, INTERVAL 5 DAY)";


i'm using mysql and i am new to all of this. didn't really see what i was looking for in the recommended article so any other help would be appreciated.

thank you!

This post has been edited by ninethousandfeet@msn.com: 09 April 2009 - 11:26 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1