0

I have a query that successfully returns results using the sequel pro app on OSX:

WHERE `LastReported` BETWEEN "24/06/2014" AND "31/07/2014";

My date field in the database isn't a proper date field, it's a VARCHAR field with dates (populated from a Bash script) in the UK format: 05/08/2014 21:46:43

Sequel Pro returns the query successfully, but when attempting in PHP it just seems to return all results, as though the query isn't being applied/filtered.

My PHP is identical to the below link (it's DataTables) and works with other non-date based queries. I have just set the variable like this:

 $sWhere = "WHERE `LastReported` BETWEEN "24/06/2014" AND "31/07/2014"";

I've tried changing the " to \" and enclosing the dates within ' instead of " all to no avail.

http://datatables.net/development/server-side/php_mysql

BSUK
  • 692
  • 1
  • 12
  • 28

1 Answers1

0

Use single quotes in MySQL, and the correct form would be YYYY-MM-DD:

$sWhere = "WHERE `LastReported` BETWEEN '2014-06-24' AND '2014-07-31'";
Tibor B.
  • 1,680
  • 1
  • 10
  • 12
  • Thanks, though that seems to return no results at all. Also, when typing into the DataTables search field, you then get all results as you type..? Very odd. Maybe the DataTables PHP code is doing something odd, though I have had success previously with non date related queries. – BSUK Aug 05 '14 at 21:38