What's wrong with my query? Deleting rows by date

Discussion in 'PHP' started by Colbyt, Oct 23, 2009.

0
  1. #1
    Okay I am confused about why this does not work. Every reference I can find online including a post ofn this site says it should work.

    $today = date("Y-d-m");  
echo "<h3> Today is: ". $today . "</h3>"; // gives the correct date in the proper format
mysql_query("DELETE FROM table WHERE date < '$today'")or die(mysql_error());
    PHP:


    The date field is in date format in the DB and the seperators match (-).

    It deletes all the entries in the database, two of which are older than today and three are in the future.
     
    Colbyt, Oct 23, 2009 IP
  2. LOD

    LOD Member

    Messages:
    319
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    35
    #2
    this should work...
    
$today = date("Y-d-m");  
echo "<h3> Today is: ". $today . "</h3>";
mysql_query("DELETE FROM table WHERE date < {$today}")or die(mysql_error());
    Code (markup):


    the mistake ur making is $today variable isnt generating any date.. it is being passed as a text "today"... to pass the value of a variable within double quote u need to use {$varaibleName} instead of using '$variableName'.. because second one is just wrong...

    single quotation is used only to use quotation inside of quotation not to pass the value of variable...
    hope that helps...
     
    Last edited: Oct 23, 2009
    LOD, Oct 23, 2009 IP
    Colbyt likes this.
  3. DollaradeConner

    DollaradeConner Active Member Affiliate Manager

    Messages:
    200
    Likes Received:
    14
    Best Answers:
    0
    Trophy Points:
    58
    #3
    I'm not 100% sure, but I think in order to do date comparison via SQL queries in the format you want, you have to use the 'DATE' data type for storing the date, which will be in the form of YYYY-MM-DD, not YYYY-DD-MM.

    Are you storing the date as a VARCHAR or TINYTEXT or something like that? If so, I think that may be causing the problem, along with the improper date structure. I'm not positive though, where's munj? :)
     
    DollaradeConner, Oct 23, 2009 IP
    Colbyt likes this.
  4. heavydev

    heavydev Peon

    Messages:
    33
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #4
    $today = date("Y-d-m");

    you might also want to try

    $today = date("Y-m-d");
     
    heavydev, Oct 23, 2009 IP
  5. Colbyt

    Colbyt Notable Member

    Messages:
    3,224
    Likes Received:
    185
    Best Answers:
    0
    Trophy Points:
    210
    #5

    Winner!

    I screwed up the format on the date(). Y-m-d is correct as the data is stored in the database in that format.

    Greenies added for the help.
     
    Colbyt, Oct 23, 2009 IP
  6. mastermunj

    mastermunj Well-Known Member

    Messages:
    687
    Likes Received:
    13
    Best Answers:
    0
    Trophy Points:
    110
    #6
    did I miss anything? ;)

    you certainly gave a correct answer.

    If deleting will always be based on date less than today then we need not even pick date from php, mysql has built in function / variables representing today's date.

    query would be something like this..

    
mysql_query("DELETE FROM table WHERE date < current_date") or die(mysql_error());
    PHP:
     
    mastermunj, Oct 23, 2009 IP
    Colbyt likes this.
  7. Colbyt

    Colbyt Notable Member

    Messages:
    3,224
    Likes Received:
    185
    Best Answers:
    0
    Trophy Points:
    210
    #7
    I also want the current date displayed on the page but your method is nice to know.

    Thanks for the post.
     
    Colbyt, Oct 24, 2009 IP