1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Date Comparisons

Discussion in 'PHP' started by Weirfire, Oct 20, 2004.

  1. #1
    I'm trying to compare 2 dates in my PHP code so that I can delete old entries in the database. The problem I have is that the string dates are not comparing the way I am expecting them to. Any ideas?


    SEMrush
     
    Weirfire, Oct 20, 2004 IP
    SEMrush
  2. sarahk

    sarahk iTamer Staff

    Messages:
    25,260
    Likes Received:
    3,438
    Best Answers:
    100
    Trophy Points:
    665
    #2
    what format are the dates in the database?
     
    sarahk, Oct 21, 2004 IP
  3. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,975
    Likes Received:
    364
    Best Answers:
    0
    Trophy Points:
    280
    #3
    They are VARCHARs in the database
     
    Weirfire, Oct 21, 2004 IP
  4. T0PS3O

    T0PS3O Feel Good PLC

    Messages:
    13,220
    Likes Received:
    778
    Best Answers:
    0
    Trophy Points:
    0
    #4
    Can you not change that to the Unix Timestamp?
     
    T0PS3O, Oct 21, 2004 IP
  5. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,975
    Likes Received:
    364
    Best Answers:
    0
    Trophy Points:
    280
    #5
    I thought $ExpDate=Strtotime($field); was changing it to the UNIX timestamp?

    I'm not sure, all I know is that they're not comparing the way they should be. I'm tempted to just store each part of the date singularly in the database which is pretty messy but it'll get the job done.
     
    Weirfire, Oct 21, 2004 IP
  6. T0PS3O

    T0PS3O Feel Good PLC

    Messages:
    13,220
    Likes Received:
    778
    Best Answers:
    0
    Trophy Points:
    0
    #6
    There's a function to check whether it's a string or integer etc. Prolly is_string and is_integer. See if they are both the same type. If not you need to convert.

    I meant the MySql field type. Instead of being VarChar, why not use the designated timestamp types?
     
    T0PS3O, Oct 21, 2004 IP
  7. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,975
    Likes Received:
    364
    Best Answers:
    0
    Trophy Points:
    280
    #7
    Hmmm, I'll give that a go. It would probably make creating the expiry date easier as well. :)
     
    Weirfire, Oct 21, 2004 IP
  8. T0PS3O

    T0PS3O Feel Good PLC

    Messages:
    13,220
    Likes Received:
    778
    Best Answers:
    0
    Trophy Points:
    0
    #8
    I always found database date & time stuff a right b*tch, takes way too long to figure out what's best but there's more than one approach to it.

    Storing it as a string is not really the best option.
     
    T0PS3O, Oct 21, 2004 IP
  9. daboss

    daboss Guest

    Messages:
    2,249
    Likes Received:
    151
    Best Answers:
    0
    Trophy Points:
    0
    #9
    Your code seems to be correct (although it's not the most elegant way of doing it).

    The only thing I can think of is maybe the contents of your 3rd field (which you are using to compare) cannot be interpreted by strtotime. If this is true, I believe strtotime will return a -1.

    Can you post a sample value of what you have in the 3rd field of you table?
     
    daboss, Oct 21, 2004 IP
  10. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,975
    Likes Received:
    364
    Best Answers:
    0
    Trophy Points:
    280
    #10
    I've never been one for elegance. :eek:

    Here's a sample of the data in the 3rd field

    22/10/2004 - 12:06 pm

    I reckon I should change it to 24hour time but the expiry is set to 1 day after login.
     
    Weirfire, Oct 21, 2004 IP
  11. daboss

    daboss Guest

    Messages:
    2,249
    Likes Received:
    151
    Best Answers:
    0
    Trophy Points:
    0
    #11
    Weirfire,

    Since I have Apache, PHP and MySQL installed on my laptop, I created a simple script based on what you have posted and tested it out - successful as a breeezzzzeeeee....

    This is what I used (which is basically what you posted with some additional DB connecting commands):

    <?php
    
    $Date = Strtotime(date("j/n/Y - g:i a"));
    
    mysql_connect("localhost", "root", "password") or die("Cannot connect to DB!");
    mysql_select_db("test") or die("Cannot select DB!");
    $resulta = mysql_query("SELECT * FROM test_tbl") or die("Cannot retrieve from Table!");
    
    while ($get_info = mysql_fetch_row($resulta)){ 
    
       $i=0;
       foreach($get_info as $field){
          if($i==0){
             $MID=$field;
          }
    
          if($i==2){
             $ExpDate=Strtotime($field); 
    
             if($ExpDate<$Date){
                mysql_query("DELETE FROM test_tbl WHERE id='$MID'") or die("Cannot delete!");
             }
          }
    
          $i++;
       }
    }
    
    ?> 
    Code (markup):

    I created a "test" database with a table called "test_tbl". "test_tbl" contains only 3 fields - the third being a varchar that stores date in the format you are using (e.g. 22/10/2004 - 12:06 pm)

    Worked perfectly - so I'm not sure why you are not getting the expected results. You can post a more comprehensive script if you want - I don't mind having a look at it :)
     
    daboss, Oct 22, 2004 IP
    T0PS3O likes this.
  12. mushroom

    mushroom Peon

    Messages:
    369
    Likes Received:
    15
    Best Answers:
    0
    Trophy Points:
    0
    #12
    If you store them as timestamps you have some every elegant options where MySql dose all the work for you.

    mysql_query("delete from LoggedIn where  your_timestamp_field< date_sub(now(),interval 1 day)");
    PHP:
    or
    mysql_query("delete from LoggedIn where  your_timestamp_field< date_sub(now(),interval 24 hour)");
    PHP:
    The way timestamps work in mysql you can just add one to your table with out changing any of your scripts and use one of the above lines for house keeping. Note "day or hour" no "s" do not run on live site till interval expires after creating timestamp field.
     
    mushroom, Oct 22, 2004 IP
  13. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,975
    Likes Received:
    364
    Best Answers:
    0
    Trophy Points:
    280
    #13
    That's great Mushroom.

    Thanks for all your help guys, I'll try that one now.


    Oh by the way mushroom go to

    www.badgerbadgerbadger.com


    Your name reminded me of this site ;)
     
    Weirfire, Oct 22, 2004 IP