1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

How do I delete the rows that are 30+ days old?

Discussion in 'PHP' started by qwikad.com, Sep 19, 2015.

  1. #1
    When I run a cronjob, I want to delete from ajaxfavourites in the exptime column all rows that are 30+ days old. I tried:

    "DELETE FROM ajaxfavourites WHERE exptime < NOW() + INTERVAL 30 DAY"
    Code (markup):
    For some reason it deletes them like they need to be deleted now. I know I am doing something wrong. I don't know if it makes a difference or not the date format in the expire column is 2015-09-19 21:54:24
    Solved! View solution.
    Last edited: Sep 19, 2015
    qwikad.com, Sep 19, 2015 IP
  2. #2
    Well, first of all, your comparison checks to see if they're LESS than now + 30 days. Also, you should probably put now + the interval in a ()
    PoPSiCLe, Sep 19, 2015 IP
  3. qwikad.com

    qwikad.com Illustrious Member Affiliate Manager

    Likes Received:
    Best Answers:
    Trophy Points:
    LOL. I didn't see it. Thanks bro.
    qwikad.com, Sep 19, 2015 IP
  4. ThePHPMaster

    ThePHPMaster Well-Known Member

    Likes Received:
    Best Answers:
    Trophy Points:
    Another way of doing it:

    DELETE FROM `ajaxfavourites` WHERE exptime  < DATE_SUB(curdate(), INTERVAL 30 DAY);
    Code (markup):
    ThePHPMaster, Sep 19, 2015 IP
    qwikad.com likes this.
  5. deathshadow

    deathshadow Acclaimed Member

    Likes Received:
    Best Answers:
    Trophy Points:
    Some advice, store the expiration in the advert as the ACTUAL expiration time instead of during your delete. Adding new ones is going to be run a lot less frequently (you hope) than checking for deletion, and the more complex delete is going to bog things down longer.

    so when you INSERT INTO:

    exptime = DATE_ADD(NOW(), INTERVAL 30 DAY)

    that way your delete query is just:

    DELETE FROM ajaxfavorites WHERE exptime < NOW()

    Far simpler, makes the larger query faster. Only problem with this approach is that if you want to change the expiration time you'd have to go through and update them all -- which really shouldn't be that big a deal.

    Basically, if you're going to store the expiration time, STORE THE EXPIRATION TIME not the CREATED TIME.
    deathshadow, Sep 21, 2015 IP