SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    <? echo "Kick me"; ?> petesmc's Avatar
    Join Date
    Nov 2000
    Location
    Hong Kong
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Sorry to ask even more question and as you can guess, I'm writing another PHP script.

    This time I need to know how to say if something is old that two weeks then delete.

    In a table called emails I have a field called date and a field called valid.

    I want to say:

    PHP Code:
    $get mysql_query("SELECT * FROM email WHERE listID='$listID' AND valid='0'");
    if(!
    $get) {
    errorMsg("Cannot query the database");
    print 
    mysql_error();
    } else {
    $num_rows mysql_numrows($get);
    while (
    $result mysql_fetch_array($get)) {
    $email $result["email"];
    $id $result["ID"];
    $date $result["date"];

    #####################
    THE PART BELOW
    #####################
    if($dateis older than two weeks ) {
    add $id  to delid[] array
    print 
    $email;

    }

    My delid[] delete part is like this:

    PHP Code:
    if(count($delid) > 0) { 
    $delidStr implode(",",$delid); 

    $del mysql_query("DELETE from emails where ID in ($delidStr)");
    if(
    $del) {
    msg("The selected emails have been deleted.");
    } else {
    errorMsg("There was an error deleting the emails from the database.");

    So, can anyone help me out with this one. Oh yeah, the date is a date type field in the mysql database and gets its format from CURDATE() when added to the database.

    Thanks
    Peter

  2. #2
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How about just using MySQL date functions and delete all records with one query


    Code:
    $result = mysql_query("DELETE from tablename WHERE date < DATE_SUB(date, interval 14  day);
    Last edited by freddydoesphp; Apr 16, 2001 at 18:00.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  3. #3
    <? echo "Kick me"; ?> petesmc's Avatar
    Join Date
    Nov 2000
    Location
    Hong Kong
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Was a bit of that code cutoff?
    Thanks

  4. #4
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes 'm
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  5. #5
    <? echo "Kick me"; ?> petesmc's Avatar
    Join Date
    Nov 2000
    Location
    Hong Kong
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    It does'nt seam to work, I use phpMyADMin and changed it to a select statement. Here is the mysql results:

    Code:
    Error 
    SQL-query: 
    
    SELECT * FROM emails WHERE ID='4' AND validated='0' AND date < DATE_SUB(date, INTERVAL 14 DAY);
    MySQL said:
    Any ideas? BTW, I don't get an error, there is no typo.
    Last edited by petesmc; Apr 16, 2001 at 18:15.

  6. #6
    <? echo "Kick me"; ?> petesmc's Avatar
    Join Date
    Nov 2000
    Location
    Hong Kong
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Also this:

    Code:
    $result = mysql_query("DELETE from tablename WHERE date < DATE_SUB(date, interval 14  day);
    Shoudl it be:

    Code:
    $result = mysql_query("DELETE from tablename WHERE date < DATE_SUB(date, interval 14  day);");

  7. #7
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is ID a unique key if so then if the record with ID 4 is not older than two weeks then you won't get any results. Try changing your query to be


    Code:
    SELECT * FROM emails WHERE date < DATE_SUB(CURDATE(), INTERVAL 14 DAY);


    Then it should return all rows that are older than two weeks
    Last edited by freddydoesphp; Apr 16, 2001 at 18:35.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.


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
  •