• 0

Deleting MySQL rows based on dates


Question

+--------+--------+----------------+----------------+------+

|?FROM???|?TO?????|?MESSAGE????????|?DATE???????????|?SEEN?|

+========+========+================+================+======+

|?Joe????|?Monica?|?How?You?Doing??|?20030316035545?|?Yes??|

+--------+--------+----------------+----------------+------+

|?Monica?|?Joe????|?F.?Off!????????|?20030316092920?|?Yes??|

+--------+--------+----------------+----------------+------+

|?Joe????|?Monica?|?Whatever!??????|?20030316123309?|?No???|

+--------+--------+----------------+----------------+------+

Hello, I have a table that looks a little like the one shown above. It's a Table that stores messages from one user to another, sort of being built for a private instant-messenger type program.

The above example shows that Joe sent a message to Monica on Sunday 16th March, 2003. 3:55:45 AM. The message reads as "How You Doing?". And it also shows that Monica has read the message as the "SEEN" field in that row says "Yes".

It also shows that Monica had replied to Joe at Sunday 16th March, 2003. 9:29:20 AM with the message "F. Off!". And it shows that Joe has read the message.

It then shows an example of a message from Joe to Monica, posted at Sunday 16th March, 2003. 12:33:09 PM, that says "Whatever!". However, in this case Monica has not read the message, as the value in her "SEEN" field is still set to "No".

I was wondering if someone could help me with a piece of PHP code that will delete any messages that have been read (i.e. SEEN value == Yes) and are over 2 days old.

As you can see in the example, the messages are over two weeks old and I don't really wan to keep read messages on the database and would like an automatic bulk removing system.

I am not sure how to tackle this, so if you can help, I would be very grateful.

Link to comment
Share on other sites

3 answers to this question

Recommended Posts

  • 0

Try running this query:

DELETE FROM db WHERE date > NOW() - INTERVAL +2 DAY AND seen IS "Yes"

I don't have a server handy to mess around with, but off the top of my head, that should work (or at least the concept ought to).

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.