Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2010
    Posts
    136

    Delete data after 1 month

    Hi...

    I have form of uploading data to the database, but I need to delete data after 1 month. how does it possibe.

    here is my query of insert data to the database:

    Code:
    $sql = "INSERT INTO sales_order (ProductType,WorkOrder,POIssueDate,SalesMonth) 
          VALUES 
          ('$ProductType','$WorkOrder','$POIssueDate','$SalesMonth')
          ON DUPLICATE KEY UPDATE
          ProductType = '$ProductType', WorkOrder = '$WorkOrder', POIssueDate = '$POIssueDate', SalesMonth = '$SalesMonth'" or die(mysql_error());
          mysql_query($sql, $con);
    Should I need to add fields for date upload?

    Thank you

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    860
    Unless you know the records that have been added today you will not know which ones should be removed 1 month from now. So you need to add a field to hold this information.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,426
    run a delete query (possibly as a cron job) that deletes based on comparing the current date to whatever date column(s) in your db
    do the comparison using one of the datetime functions such as datesub
    MySQL :: MySQL 5.5 Reference Manual :: 12.7 Date and Time Functions
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    860
    You can also use MySQL EVENTS which is an internal scheduler. But the problem is still do you know which records are added today or any day. I would suggest adding in a separate field which has a default of the current date. With this you can then identify the records that were added on whatever date and as such can remove all records based on date or age in the database.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •