Comparing date from variable to MySQL date

Question

I'm comparing date specified in PHP with dates in MySQL table and then echoing all rows with dates after the date in PHP. It works OK when I specify the date in the SELECT command, but when I make it a variable it doesn't work.

My PHP is:

  $sql = "SELECT event FROM LifeEvents WHERE event_date > '1995-02-27'";
  $result = $conn->query($sql);

  if ($result->num_rows > 0) {
      // output data of each row
      while($row = $result->fetch_assoc()) {
          echo $row["event"] . "<br>";
      }
  } else {
      echo "0 results";
  }
  $conn->close();

This works ok. But when I replace the SELECT with:

SELECT event FROM LifeEvents WHERE event_date > $date

It no longer works. For the variable I've tried working with both:

$date = new DateTime('1995-02-27')

and

$date = '1995-02-27'

Neither of them works. The data type in MySQL is DATE.

is that variable dynamic ? I mean the script changes it or you just put a date in it as you mentioned above ? — ThS, Aug 19 '18 at 21:13
@ths Right now, I just put a date in it, but in final version of the code the date in the variable will be taken from another db table — user1207524, Aug 19 '18 at 21:16

score 3 · Answer 1 · answered Aug 19 '18 at 21:37

For the string variable

$date = '1995-02-27'
$sql = "SELECT event FROM LifeEvents WHERE event_date > '$date'";
// this should work exactly as
$sql = "SELECT event FROM LifeEvents WHERE event_date > '1995-02-27'";

For the DateTime object, this should work

$date = new DateTime('1995-02-27')
$sql = "SELECT event FROM LifeEvents WHERE event_date > '" . $date->format(Y-m-d). "'";

I suggest you always set the timezone when you are dealing with dates and times because underestimate timezones can result in really bad bugs specially if your applications is dealing with many timezones and you have timestamp data type in your database.

make it a habit to do this

define('DEFAULT_TIMEZONE','UTC');//set this in your global config.php file
$date = new DateTime('2000-01-01', new DateTimeZone(DEFAULT_TIMEZONE));

Personally I thought this was a much better answer than the accepted one. — Nick, Aug 20 '18 at 02:48

score 1 · Answer 2 · answered Aug 19 '18 at 21:25

That's because '1995-02-27' and DateTime('1995-02-27') are both not valid in that case :

'1995-02-27' would work if you'd have put extra quotes around $date in your SQL query :
"SELECT event FROM LifeEvents WHERE event_date > '$date'"
DateTime('1995-02-27') doesn't have a __toString() magic method so PHP is unable to represent it as a string.
You have to use the format method : $date = $date->format('Y-m-d') (and it will still miss the quotes)

One or another solution, you need to protect yourself against SQL injections using prepared statements! And you will actually no longer need any quote.

score 1 · Accepted Answer · answered Aug 19 '18 at 21:27

Lets create a variable that holds a date and then convert it to date format that MySQL can understand.

$dt = date('Y-m-d', strtotime('1995-02-27')); // maybe filled dynamically
 $sql = "SELECT event FROM LifeEvents WHERE event_date > '$dt'";
  $result = $conn->query($sql);

  if ($result->num_rows > 0) {
      // output data of each row
      while($row = $result->fetch_assoc()) {
          echo $row["event"] . "<br>";
      }
  } else {
      echo "0 results";
  }
  $conn->close();

Then change what you want in order to serve your needs. Hope I pushed you further.

score -2 · Answer 4 · edited Aug 20 '18 at 03:15

-2

Try using the Carbon library for PHP.

Store a date with PHP:

$current_time = Carbon::now()->toDateTimeString();

Then just compare it the same way.

edited Aug 20 '18 at 03:15

Grant Miller

27,532
16
147
165

answered Aug 19 '18 at 21:41

Tomi Jurukovski

60
6

PHP already has functions to deal with date and time there is no reason to import a new library. – Nick Aug 20 '18 at 00:47

Comparing date from variable to MySQL date

4 Answers4