Skip to main content
A
Anonymous
November 2, 2009
Answered

PHP filter by Date

  • November 2, 2009
  • 1 reply
  • 484 views

Hi Chaps,

I have a bit of PHP code, that at the moment doesn't work.
I'm trying to show all the 'overdue' projects in a different style:

PHP Code:

<?php if ($row_rsProjects['projdue'] == '= DATE(NOW())') { ?>

      <tr class="overdue">

      <?php } else if ($row_rsProjects['projdue'] == '< DATE(NOW())') { ?>

      <tr class="duetoday">

      <?php } else { ?>

      <tr class="within">

      <?php }?>

The

Quote:
'= DATE(NOW())'

works with MySQL, but I don't know how to get it to work with PHP. Any ideas?

This topic has been closed for replies.
Correct answer David_Powers

DATE(NOW()) is MySQL code. As you have discovered, it doesn't work in PHP.

It's not just a question of using the wrong code, MySQL and PHP handle dates in totally incompatible ways. PHP uses Unix timestamps, which count the number of seconds since the beginning of January 1970. MySQL stores dates in the ISO recommended YYYY-MM-DD HH:MM:SS format.

There are several ways you could do this, but the following should work:

// get the date parts from the MySQL date
$dateparts = preg_split('/[-\s]/', $row_rsProjects['projdue']);
// get the Unix timestamp for midnight at the beginning of the due date
$projdue = mktime(0,0,0, $dateparts[1], $dateparts[2], $dateparts[0]);
// get the Unix timestamp for midnight at the beginning of today
$today = mktime(0,0,0, date('n'), date('j'), date('Y'));
if ($today == $projdue) {
  // the project is due today
} elseif ($today > $projdue) {
  // the project is overdue
} else {
  // the project is not yet due
}

1 reply

David_Powers
David_PowersCorrect answer
Inspiring
November 2, 2009

DATE(NOW()) is MySQL code. As you have discovered, it doesn't work in PHP.

It's not just a question of using the wrong code, MySQL and PHP handle dates in totally incompatible ways. PHP uses Unix timestamps, which count the number of seconds since the beginning of January 1970. MySQL stores dates in the ISO recommended YYYY-MM-DD HH:MM:SS format.

There are several ways you could do this, but the following should work:

// get the date parts from the MySQL date
$dateparts = preg_split('/[-\s]/', $row_rsProjects['projdue']);
// get the Unix timestamp for midnight at the beginning of the due date
$projdue = mktime(0,0,0, $dateparts[1], $dateparts[2], $dateparts[0]);
// get the Unix timestamp for midnight at the beginning of today
$today = mktime(0,0,0, date('n'), date('j'), date('Y'));
if ($today == $projdue) {
  // the project is due today
} elseif ($today > $projdue) {
  // the project is overdue
} else {
  // the project is not yet due
}
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices