在计算日期时正确使用PHP和MySQLi查询

Question

I am new to PHP / MySQLi and having a little problem and wondering if someone can see what i am doing wrong.

I have 2 fields in question, invoice_date and invoice_due_date which are formatted within the database like "15/03/2017 and 12/04/2017"

What i am trying to do is check invoice_due_date in the query and if date is less than current date to return those items but cannot seem to crack it... here are the few queries i have tried.

$query = "SELECT SUM(`total`) as `total` FROM invoices WHERE invoice_due_date < DATE_FORMAT(now(),'%d/%m/%Y') AND invoice_type = 'invoice' AND status = 'open'";
$query = "SELECT SUM(`total`) as `total` FROM invoices WHERE invoice_due_date < DATE_FORMAT(NOW(),'%d/%m/%Y') AND status = 'open'";

Was assuming would need to convert NOW() to format it needs to compare from to work correctly but clearly im missing something haha

DATE_FORMAT(now(),'%d/%m/%Y')

Answer 1

Set your dates to be ISO-8601 dates, YYYY-MM-DD in a DATE column. You cannot compare values like this. It's total nonsense because the fields are not in the right order.

Look at how these sort:

2011-02-01
2012-01-01
2015-01-02

Versus this which may as well be random even though "ordered":

01/01/2012
01/02/2011
02/01/2015

Keep your data in the most neutral format possible and apply any necessary date formatting in your display logic.