#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2000
    Location
    Sweden
    Posts
    384
    Rep Power
    15
    Hello all,

    Does anybody know how to make a script that automatically removes mySQL records older than 14 days?

    Best regards,
    Christoffer
    SWEDEN
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 1999
    Location
    Annapolis, Maryland US
    Posts
    113
    Rep Power
    15
    I'm making an assumption that you're using some flavor of UNIX, you have an account on the system, and the mysql table has some sort of date/timestamp field.

    1) create a file called sqlexec containing the following:
    mysql -u your_username -pyour_password

    2) create a file called delete.sql containing the following:
    use your_dbname;
    delete from table_name where to_days(now())-to_days(date_field) > 14;

    3)run it as a crontab:
    # crontab -e
    (insert into crontab file)
    2 50 * * * /path/to/file/sqlexec<delete.sql

    This will execute the script once a day at 2:50 AM

    If the above assumptions weren't correct, then there are other ways, but that's the Q&D method for *NIX.

    Kyuzo
  4. #3
  5. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    That depends, do you have a field in the table that stores the date?

    mysql_query("delete from table_name where to_days(datesub(now(), interval 14 day)) > to_days(date_field)");

    You can set this up in a cron job to run after midnite.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2000
    Location
    Sweden
    Posts
    384
    Rep Power
    15
    Thanks, guys!

    I just have one more question. In witch format shall the date be? Should I use:
    $date=date("dm", time());

    Or should I use the mySQL timestamp field?

    Thanks again.

    Best regards,
    Christoffer
    SWEDEN
  8. #5
  9. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    You need to use some type of mysql date/time field. Don't use a unixtimestamp if you want to use mysql's built in time functions. However, you don't necessarily want to use the timestamp datatype as that will automatically change whenever you make an insert/update/replace on the record. If you don't want that use a date or datetime (if you need the time recorded as well as the date).

Similar Threads

  1. select records with 2 days after currnent date
    By martinwtp in forum MySQL Help
    Replies: 2
    Last Post: February 17th, 2004, 07:36 PM
  2. Replies: 1
    Last Post: February 5th, 2004, 02:45 PM
  3. Delete identical records
    By cmouysset in forum MySQL Help
    Replies: 1
    Last Post: December 11th, 2003, 02:07 AM
  4. How to delete records form DB based on ticked checkboxes?
    By rob howells in forum PHP Development
    Replies: 2
    Last Post: November 7th, 2003, 11:00 AM
  5. delete records from binary
    By jinkhai in forum C Programming
    Replies: 0
    Last Post: October 22nd, 2003, 10:24 AM

IMN logo majestic logo threadwatch logo seochat tools logo