Professional Web Applications Themes

Help with deleting records based on time difference - MySQL

Hi How can I delete records in a table based on a timestamp where the timestamp is greater than 20 mins old. I am using part php and part mysql. This is the type of query I want to execute. $firstquery = "DELETE FROM Pendingtransactions WHERE time=(any records that are older than 20mins)"; I have kinda worked out for myself how to calculate the number of minutes since the last entry by using the following code in php. <? //execute $result query to find timestamp value. I'm ok with that part - then $row = mysql_fetch_row($result); $timeoflastentry = $row[1]; $timenow ...

  1. #1

    Default Help with deleting records based on time difference

    Hi
    How can I delete records in a table based on a timestamp where the timestamp is
    greater than 20 mins old. I am using part php and part mysql. This is the type
    of query I want to execute.
    $firstquery = "DELETE FROM Pendingtransactions WHERE time=(any records that are
    older than 20mins)";

    I have kinda worked out for myself how to calculate the number of minutes since
    the last entry by using the following code in php.
    <?
    //execute $result query to find timestamp value. I'm ok with that part - then
    $row = mysql_fetch_row($result);
    $timeoflastentry = $row[1];
    $timenow = mktime();
    $unix_timestamp = strtotime($timeoflastentry);
    $differenceinminutes = ($timenow - $unix_timestamp)/60;
    ?>

    Only problem now is how do I utilise the result $differenceinminutes in the
    delete query above or is there an easier way to delete records that are older
    than 20 mins?

    Please note that i first posted this question in another group and was told that
    it is more a database question than a php question.

    Any help greatly appreciated.
    Dynamo


    Dynamo Guest

  2. #2

    Default Re: Help with deleting records based on time difference

    Dynamo wrote:
    > How can I delete records in a table based on a timestamp where the timestamp is
    > greater than 20 mins old. I am using part php and part mysql. This is the type
    > of query I want to execute.
    > $firstquery = "DELETE FROM Pendingtransactions WHERE time=(any records that are
    > older than 20mins)";
    I'd do it in a SQL expression instead of in PHP code. SQL has some
    syntax for doing date & time arithmetic and comparisons:

    DELETE FROM Pendingtransactions
    WHERE `time` < NOW() - INTERVAL 20 MINUTE;

    See also the DATE_ADD() function on this page:
    [url]http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html[/url]

    Regards,
    Bill K.
    Bill Karwin Guest

  3. #3

    Default Re: Help with deleting records based on time difference

    Thank you sooooo much. I guess I was trying to overcomplicate matters. I wiil
    give it a whirl.
    Many many thanks
    Dynamo
    In article <e5ff8b01r0venews3.newsguy.com>, Bill Karwin says...
    >
    >Dynamo wrote:
    >>How can I delete records in a table based on a timestamp where the timestamp is
    >>greater than 20 mins old. I am using part php and part mysql. This is the type
    >> of query I want to execute.
    >>$firstquery = "DELETE FROM Pendingtransactions WHERE time=(any records that are
    >> older than 20mins)";
    >
    >I'd do it in a SQL expression instead of in PHP code. SQL has some
    >syntax for doing date & time arithmetic and comparisons:
    >
    >DELETE FROM Pendingtransactions
    >WHERE `time` < NOW() - INTERVAL 20 MINUTE;
    >
    >See also the DATE_ADD() function on this page:
    >[url]http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html[/url]
    >
    >Regards,
    >Bill K.
    Dynamo Guest

Similar Threads

  1. Deleting multiple records
    By Looch in forum Coldfusion Database Access
    Replies: 14
    Last Post: April 19th, 01:37 PM
  2. Deleting duplicate records
    By Dinesh.T.K in forum FileMaker
    Replies: 3
    Last Post: August 13th, 12:00 AM
  3. Deleting records against a list
    By Rick Altman in forum FileMaker
    Replies: 2
    Last Post: July 18th, 03:38 AM
  4. deleting records and keeping them
    By kabir in forum Microsoft Access
    Replies: 1
    Last Post: July 13th, 05:33 PM
  5. deleting selection of records
    By George Mizzell in forum Macromedia Dreamweaver
    Replies: 0
    Last Post: July 13th, 01:30 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139