How to compare two dates from MYSQL using PHP

Question

I'm trying to do a simple thing in PHP, but not run properly. So, I need to compare two dates from MYSQL using PHP and show the oldest date, but the result in PHP is not correct.

I have this table in MYSQL DB:

CREATE TABLE users (
    id int,
    name varchar(255),
    date1 date,
    date2 date,
);

+---+---------------+------------+------------+
|id |name           |date1       |date2       |
+---+---------------+-------------------------+
|1  |ANA            |2005-02-27  |2004-03-27  |
+---+---------------+------------+------------+
|2  |MARIA          |2010-03-08  |2014-11-01  |
+---+---------------+-------------------------+

I'm using this PHP code:

$conn =  mysqli_connect($host, $user, $pwd, $db);
$sql = "SELECT name, UNIX_TIMESTAMP(date1), UNIX_TIMESTAMP(date2) FROM users ";
$result = mysqli_query($conn, $sql);

echo "<html>";
echo "<head>";
echo "</head>";
echo "<body>";

echo "<table border=1>";
echo "<tr>";
echo "<td><b>Name</b></td>";
echo "<td><b>Date1</b></td>";
echo "<td><b>Date2</b></td>";
echo "<td><b>Oldest Date</b></td>";
echo "<tr>";
while ($row = mysqli_fetch_array($result)) {
   echo "<tr>";
      echo "<td>" . $row[0] . "</td>";
      echo "<td>" . $row[1] . "</td>";
      echo "<td>" . $row[2] . "</td>";
      if (intval($row[1]) < intval($row[2])){
         echo "<td>" . $row[1] . "</td>";
      } else {
         echo "<td>" . $row[2] . "</td>";
      };
   };
   echo "</tr>";
};
echo "</table>";

It seems easy, but this statement of comparison do not return correctly. I just tried this ways below:

1. Using operator < (no correct return):

if ($row[1] < $row[2]) {...}

2. Using intval to force unix_timestamp to be Integer (no correct return):

if (intval($row[1]) < intval($row[2])) {...}

Any idea to resolv this question? It is a simple comparison of dates (from fields of type DATE in MYSQL).

Thanks

Michel · Accepted Answer · 2020-06-16 05:56:17Z

2

Just let de database handle it for you:

$sql = "SELECT name, date1, date2, IF(date1 < date2, date1, date2) FROM users";

Now $row[3] always contains the smallest date.

[Edit]

As OP only later mentioned the dates could also have a NULL value, the query needs adjusting. It now always returns the other date if one of the dates is empty, or NULL if both dates are empty.

SELECT id, date1, date2, 
  // test if one of the dates (or both) is NULL
  // if so return the one date, or NULL if both are empty
IF(date1 IS NULL OR date2 IS NULL, COALESCE(date1, date2),
  // else compare dates
IF(date1 < date2, date1, date2)
) FROM test

edited Jun 16, 2020 at 5:56

answered Jun 5, 2020 at 12:37

Michel

4,1554 gold badges39 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Adriano Over a year ago

It is a good way, but I have NULL in some dates. In this case it always returns NULL. Is it possible to exclude NULL dates?

Michel Over a year ago

As you didn't make any mention of it in your original question, that should be a new question. But I'll edit the answer.

GrumpyCrouton · Accepted Answer · 2020-06-05 12:29:52Z

0

You could return your dates the way they are stored (instead of converting to unix) and use a DateTime object to check the dates.

$sql = "SELECT name, date1, date2 FROM users";

...

while ($row = mysqli_fetch_array($result)) {

    //convert date1 string to DateTime object.
    $date1 = new \DateTime($row[1]);

    //convert date1 string to DateTime object.
    $date2 = new \DateTime($row[2]);

    //create a variable (using a Ternary) to hold the biggest date
    //Ternary means = (Expression) ? true : false
    $oldest_date = ($date1 > $date2) ? $date2 : $date1;

    ?>
        <tr>
            <td><?= $row[0] ?></td>
            <td><?= $row[1] ?></td>
            <td><?= $row[2] ?></td>
            <td><?= $oldest_date ?></td>
        </tr>
    <?php
}

edited Jun 5, 2020 at 12:29

answered Jun 5, 2020 at 12:24

GrumpyCrouton

8,6367 gold badges37 silver badges81 bronze badges

3 Comments

Dharman Over a year ago

DateTime is redundant here. OP seems to fetching the dates as UNIX timestamps anyway

Dharman Over a year ago

Why do you need an object? You should compare ISO8601 date strings as strings. Besides as I said OP is fetching integers.

Dharman Over a year ago

So this makes the question lacking debugging details

Collectives™ on Stack Overflow

How to compare two dates from MYSQL using PHP

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related