Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Regular Coder
    Join Date
    Jul 2011
    Location
    Toronto, ON
    Posts
    102
    Thanks
    12
    Thanked 1 Time in 1 Post

    Effeciency - best way to filter through database and delete some entries

    Hi guys,

    I have a database that usually has around 3000 entries. New entries are entered each day, and I want to set up a cron job to delete old entries. Each entry has a timestamp and I want to delete anything that is at least 24 hours old.

    What is the most efficient way to do this? I can think of 2 options.

    OPTION 1:
    PHP Code:
    $row mysql_query("SELECT * FROM table");
    foreach(
    $row as $row){
        
    //if $row['timestamp'] is older than 24 hours --> DELETE

    OPTION 2: is this possible? better?
    PHP Code:
    DELETE FROM table WHERE currenttime timestamp 24 hours 
    Thanks guys!!
    Last edited by inchecksolution; 08-05-2011 at 03:58 AM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,121
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Don't even *THINK* about OPTION 1. Horrible practice.

    Several ways to do that.

    Look in the MySQL docs.

    http://dev.mysql.com/doc/refman/5.5/..._timestampdiff
    Code:
    DELETE FROM tablename WHERE TIMESTAMPDIFF(HOUR, yourfield, NOW() ) > 24
    Or you could use
    http://dev.mysql.com/doc/refman/5.5/...ction_date-sub
    Code:
    DELETE FROM tablename WHERE DATE_SUB( NOW(), INTERVAL 24 HOUR ) > yourfield
    Or turn that around and do
    http://dev.mysql.com/doc/refman/5.5/...ction_date-add
    Code:
    DELETE FROM tablename WHERE DATE_ADD( yourfield, INTERVAL 24 HOUR ) < NOW()
    And there are still more ways to do it if you just look at all the functions available in that same chapter.

  • Users who have thanked Old Pedant for this post:

    inchecksolution (08-05-2011)

  • #3
    Regular Coder
    Join Date
    Jul 2011
    Location
    Toronto, ON
    Posts
    102
    Thanks
    12
    Thanked 1 Time in 1 Post
    Thank Old Pedant! I am still relatively new to MySQL - I appreciate the help!!!

  • #4
    Regular Coder
    Join Date
    Jul 2011
    Location
    Toronto, ON
    Posts
    102
    Thanks
    12
    Thanked 1 Time in 1 Post
    Actually I have 1 more question.

    I like the TIMESTAMPDIFF function, but when you say use 'yourfield' I typed in the column name of my database (called timestamp) but it didn't work.

    Again, the column is called timestamp - how would I modify this query which I assume will delete anything that is over 30 minutes old?

    PHP Code:
    $findold "DELETE FROM deals WHERE TIMESTAMPDIFF(MINUTE, timestamp, NOW() ) > 30";
    $execute mysql_query($findold); 

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,121
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    TIMESTAMP is a keyword in MySQL, so you made a minor error when you named your field that.

    Not biggy. Just wrap any name that causes problems in back tick marks: `timestamp`

    The back tick is usually on the same key as the tilde ~

    You can always wrap a table or field name in backticks; will never hurt, may help.

  • #6
    Regular Coder
    Join Date
    Jul 2011
    Location
    Toronto, ON
    Posts
    102
    Thanks
    12
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Old Pedant View Post
    TIMESTAMP is a keyword in MySQL, so you made a minor error when you named your field that.

    Not biggy. Just wrap any name that causes problems in back tick marks: `timestamp`

    The back tick is usually on the same key as the tilde ~

    You can always wrap a table or field name in backticks; will never hurt, may help.
    Great! So I will rename my timestamp column to "insertdate" and my query will become:

    PHP Code:
    $findold "DELETE FROM deals WHERE TIMESTAMPDIFF(MINUTE, `insertdate`, NOW() ) > 30";
    $execute mysql_query($findold); 
    I will let you know how it goes!
    THANKS AGAIN

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,121
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Ummm...you don't *NEED* to put the back ticks around insertdate since it's not a keyword.

    But, again, they won't hurt.

  • Users who have thanked Old Pedant for this post:

    inchecksolution (08-05-2011)

  • #8
    Regular Coder
    Join Date
    Jul 2011
    Location
    Toronto, ON
    Posts
    102
    Thanks
    12
    Thanked 1 Time in 1 Post
    Great insight - hopefully one day I will have this kind of knowledge readily available!!!

    It's working great!

    Thanks again Old Pedant!


  •  

    Posting Permissions

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