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
    New Coder
    Join Date
    Apr 2008
    Posts
    46
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Automatically delete advertisements with today's date

    I would need to automatically delete advertisements with today's date.

    In the field '$till' there are the dates (DD-MON-YYYY) which have to be compared to the today's date.

    PHP Code:
    $today time(DD-MON-YYYY);
    $rime mysql_query("SELECT * FROM classified WHERE '$till'>'$today' ORDER BY add_date DESC LIMIT 3") or die(mysql_error());
    while(
    $row mysql_fetch_array($rime))... 
    But the query is ignored as you can see clicking on the link. The second date in the advertisements is the expiry date '$till' http://www.veximchemicals.com/sunnyisland/index.php

    What can I do? Thanks in advance.
    Last edited by niko-cola; 08-04-2008 at 02:48 PM.

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Didn't look that hard at your query, but comparing two constants isn't going to do anything for you... one of your operators needs to be a column in the table.

  • #3
    New Coder
    Join Date
    Apr 2008
    Posts
    46
    Thanks
    1
    Thanked 0 Times in 0 Posts
    $till is the operator in the column. (it's the field to be compared with $today.

    When I had $expiry instead of $till it was everything ok.
    The difference between them is that $expiry is generated by time(), it's numeric.
    $till is DD-MON-YYYY.

    As you can see in the code, I changed $today=time(); with $today=time(DD-MON-YYYY);
    but it still doesn't work.
    Last edited by niko-cola; 08-04-2008 at 03:43 PM.

  • #4
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,638
    Thanks
    2
    Thanked 404 Times in 396 Posts
    Quote Originally Posted by niko-cola View Post
    $till is the operator in the column. (it's the field to be compared with $today.
    Really? You named your table column "$till"? If that's the case.. with the dollar sign, escape it in your query, and use backticks instead of single quotes around it so you can make sure MySQL will treat it as a column and not a string.

  • #5
    Senior Coder Len Whistler's Avatar
    Join Date
    Jul 2002
    Location
    Vancouver, BC Canada
    Posts
    1,323
    Thanks
    26
    Thanked 100 Times in 100 Posts
    How about

    PHP Code:
    $today time(DD-MON-YYYY);
    mysql_query("DELETE FROM classified WHERE till = '$today' LIMIT 3"); 

    --------------
    Leonard Whistler

  • #6
    New Coder
    Join Date
    Apr 2008
    Posts
    46
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Inigoesdr View Post
    Really? You named your table column "$till"?
    My column is named 'till'. And it is dd-mm-yyyy.
    $today instead, is a numeric date, like 12932393291
    I would need to change the current time, $today, from numeric to dd-mm-yyyy.
    How can I do it?

    PHP Code:
    $rime mysql_query("SELECT * FROM classified WHERE till>$todayformat ORDER BY add_date DESC LIMIT 3") or die(mysql_error()); 
    Last edited by niko-cola; 08-05-2008 at 09:29 AM.

  • #7
    New Coder
    Join Date
    Apr 2008
    Posts
    46
    Thanks
    1
    Thanked 0 Times in 0 Posts

    resolved, new problem

    PHP Code:
    $today time();
    $todaya date('d-m-Y');
    $rime mysql_query("SELECT * FROM classified WHERE till>'$todaya' ORDER BY link_id DESC LIMIT 3") or die(mysql_error());
    while(
    $row mysql_fetch_array($rime)) {.... 

    This is the resolution, almost:
    The order would have to be by day, month, year.
    Instead it is by month, day, year. (till is also formatted as d-m-Y).

    You can see the evolution of the problem here
    Last edited by niko-cola; 08-05-2008 at 11:34 AM.

  • #8
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    I have the perfect solution for you:

    Make your "till" column a DATE data type.

    It's impossible to do comparisons or sorts on a date in the format of dd-mm-yyyy. But MySQL takes care of it for you, you simply have to use the DATE data type.


  •  

    Posting Permissions

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