bitter_chicken
asked on
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 :~)
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 :~)
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.
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?
Your query would look something like this:
<?php
$q = "SELECT UNIX_TIMESTAMP(`datefield`
$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?
ASKER
please elaborate!
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:
https://www.experts-exchange.com/Web/Web_Languages/PHP/PHP_Databases/
https://www.experts-exchange.com/Web/Web_Languages/PHP/PHP_Databases/
ASKER
Ok thanks cracky - that should do the trick
bc :-)
bc :-)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.