CODEWITHSUNDEEP
phpmysql
alkokarko
alkokarko

Reputation: 85

Mysql Comparing dates stored to today's date

I want to go through my database datesandtime and compare them to the current date and time, and say which one is bigger.

<?php
include_once("db.php");
$date = new DateTime();
//echo $date->format('Y-m-d H:i:s')

$query = "SELECT timedate, email  FROM mailer";
$result = mysql_query($query);
echo "<table>";
while($row = mysql_fetch_array($result)){
       $tot = $row['timedate'];
       $ema = $row['email'];
    if($tot > $date) {
    //echo "<tr><td>" .$row['timedate']."</td><td>";
    echo"database dates higher then now dates" . "<br>";
    echo "<tr><td>" .$row['email']."</td><td>" ."<br>";
   echo "<tr><td>" .$row['timedate']."</td><td>" ."<br>";
}
else {
    echo"database dates lower then now dates" . "<br>";
    echo "<tr><td>" .$row['email']."</td><td>". "<br>";
       echo "<tr><td>" .$row['timedate']."</td><td>" ."<br>";
}

}
mysql_close();
?>

Here is the test output that I get:

database dates lower then now dates
[email protected]  
2015-05-04 08:00:00 
database dates lower then now dates
[email protected]  
2015-05-04 00:00:00 
database dates lower then now dates
[email protected] 
2015-05-30 00:00:00

As you can see the last entry is wrong, its set to may 30 so it should be higher then the now date. My guess is that I'm not using the current date correctly and I need to format it.

Upvotes: 4

Views: 868

Answers (5)

Michael - sqlbot
Michael - sqlbot

Reputation: 179074

It's arguably less error-prone and potentially more efficient to let the database do this.

SELECT timedate, (timedate < NOW()) AS earlier, email  FROM mailer;

This will return a new "column" called "earlier," generated on the fly, telling you which dates are earlier with a '1' (true) or not, with a '0' (false), or NULL if timedate were null.

You can also get snazzy with a CASE expression, combining multiple logical tests to derive the new information. These operations are typically extremely fast in MySQL.

Upvotes: 0

Yu Yenkan
Yu Yenkan

Reputation: 765

if($tot > $date) you cannot directly compare datatime value, you have to convert both of them time value first.

$dateTick = strtotime($date); $totTick = strtotime($tot);

After that only compare

if($dateTick < $totTick)

Upvotes: 0

Svdb
Svdb

Reputation: 367

You should first format the database result as a date before comparing them.

<?php
include_once("db.php");
$date = new DateTime();
//echo $date->format('Y-m-d H:i:s')

$query = "SELECT timedate, email  FROM mailer";
$result = mysql_query($query);
echo "<table>";
while($row = mysql_fetch_array($result)){
       $tot = DateTime::createFromFormat("Y-m-d H:i:s",$row['timedate']);
       $ema = $row['email'];
    if($tot > $date) {
       echo"database dates higher then now dates" . "<br>";
       echo "<tr><td>" .$row['email']."</td><td>" ."<br>";
       echo "<tr><td>" .$row['timedate']."</td><td>" ."<br>";
    } else {
       echo"database dates lower then now dates" . "<br>";
       echo "<tr><td>" .$row['email']."</td><td>". "<br>";
       echo "<tr><td>" .$row['timedate']."</td><td>" ."<br>";
    }

}
mysql_close();
?>

Upvotes: 1

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

Since the timedate is datetime in PHP you can simply use strtotime

if(strtotime($tot) > time()){

}else{

}

Upvotes: 5

Sougata Bose
Sougata Bose

Reputation: 31749

You cant compare dates this way. You have to convert them to timestamp first.Try with - 

if(strtotime($tot) > strtotime($date))

Upvotes: 0

Related Questions