Date Comparisons

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?

 

They are VARCHARs in the database

 

Can you not change that to the Unix Timestamp?

 

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.

 

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?

 

Hmmm, I'll give that a go. It would probably make creating the expiry date easier as well.

 

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.

 

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?

 

I've never been one for elegance.

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,

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

 

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.

 

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