1

I've got the code listed below. For some reason it is only listing events of 2016. The moment I put in an event ending in 2017, it doesn't show it.

Any idea why?

    $currentdate = date('m-d-Y');

    $events_call = $eventsdb->query("
SELECT * FROM events WHERE start <= '". $currentdate ."' 
AND (end >= '". $currentdate ."' OR end = 0) ORDER BY end ASC");

I am formatting dates in a mm-dd-yyyy format. Ex: 11-18-2016

KDJ
  • 309
  • 2
  • 14
  • What's the data type of `start` and `end`? If it's `DATE`, their format is `YYYY-MM-DD` and not `MM-DD-YYYY` – simon Nov 18 '16 at 13:56
  • its just a varchar(11) data type. And the start date is 11-17-2016 and the end date is 01-01-2017 – KDJ Nov 18 '16 at 13:58
  • Don't use `VARCHAR` for dates, the string comparison fails in this case. Use `DATE` or `DATETIME` – simon Nov 18 '16 at 14:08

2 Answers2

1

I am formatting dates in a mm-dd-yyyy format. Ex: 11-18-2016

PHP, or MySQL for that matter, doesn't automatically know these are dates, it just interprets them as numerical strings. So 11-17-2016 is greater than 01-01-2017 (11,172,016 vs 01,012,017)... You can change the format to YYYY-MM-DD before doing the comparison to solve the problem, or convert the dates to timestamps, etc...

mister martin
  • 6,197
  • 4
  • 30
  • 63
1

What do you want exactly? do you want the results BETWEEN the 2 dates? if so, take a look for the BETWEEN sql command, like:

How do I query between two dates using MySQL?

But i dont think that you can store date information in mysql in mm-dd-yyyy format. Mysql accepts yyyy-mm-dd format. Check your table structure please is it in date format?

Community
  • 1
  • 1
Mike Aron
  • 550
  • 5
  • 13