Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 387
  • Last Modified:

MySQL/php date question ~~~ 500pts

Heeey! So wonderful to see you here!

I'm using MySQL & PHP

I want to get the dates from the database, and compare them to the current date using php. (not an SQL query) I'd like to leave the database how it is; i.e. dates formatted as YYYY-MM-DD, and do the conversions in PHP.

So, basically I want to do this: 'if variableDate is more recent than currentDate, do somehthing'

Also, I will want to insert dates back into the database, formatted correctly.

Thanks in advance for your help!

bc :~)
0
bitter_chicken
Asked:
bitter_chicken
  • 4
  • 2
1 Solution
 
DarixCommented:
you need to get dates from database using UNIX_TIMESTAMP(date_field) and in PHP you can compare dates with ex. time function will return current timestamp.
0
 
crackyCommented:
To get into more detail:

Your query would look something like this:

<?php
$q = "SELECT UNIX_TIMESTAMP(`datefield`) AS `uts` FROM `table`";
$rs = mysql_query($q);
?>

You would then be able to compare the date in PHP like so:

<?php
while ($r = mysql_fetch_object($rs)) {

  if ($r->uts > time()) {
    $mysqltime = strftime("%Y-%m-%d %H:%M:%S",$yourtimestamp);
  } else {
    // Do nothing
  }

}
?>

Note that you can use PHP to convert a timestamp into a MySQL formatted time using strftime("%Y-%m-%d %H:%M:%S",$yourtime).

Please note that if variableDate is more recent than currentDate NOW(), then you are talking about a date in the future.

Does this all make sense?
0
 
bitter_chickenAuthor Commented:
please elaborate!
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
crackyCommented:
Another thing bitter_chicken. If your questions are exclusively about PHP / MySQL, you would probably find the PHP & Databases section will get you a more rapid response from more specialised experts:

http://www.experts-exchange.com/Web/Web_Languages/PHP/PHP_Databases/
0
 
bitter_chickenAuthor Commented:
Ok thanks cracky - that should do the trick

bc :-)
0
 
crackyCommented:
Maybe it's easier just to do it all in PHP:

If you extract a date from MySQL as YYYY-MM-DD, you can get the unix timestamp for that date by using the PHP function strtotime("$mysqltime")

You can then compare it to the current timestamp by using the PHP function time() to give you the current timestamp.

E.G:

$mysqltime = "2004-02-01";
$uts_mysqltime = strtotime($mysqltime);

Thus, $mysqltime < time() will evaluate to true, since 2004-02-01 is less than the current date.

You can then use strftime("%Y-%m-%d %H:%M:%S",$timestamp) to re-format any timestamp back to MySQL formatted time. If you only need the date portion and not the time, use strftime("%Y-%m-%d",$yourtime).

For more information on all of these functions, see:

http://www.php.net/manual/en/function.time.php
http://www.php.net/manual/en/function.strtotime.php
http://www.php.net/manual/en/function.strftime.php
0
 
crackyCommented:
FYI, if you didn't know, the Unix Timestamp is the number of seconds since January 1 1970 00:00:00 GMT. Many *nix based programs use it and in many cases it's easier to use timestamps to compare dates, then just use strftime() to convert it back to MySQL format.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now