CODEWITHSUNDEEP
phpmysql
user2710234
user2710234

Reputation: 3225

PHP date minus X days

I have this code in PHP:

date('Y-m-d', strtotime("-7 days"))

which i am using in an SQL Query:

$sql="SELECT * from billing_invoices WHERE due_date <= '".date('Y-m-d', strtotime("-7 days"))."' AND (status = 'Unpaid' or status = 'Part Paid') AND statement = '0000-00-00 00:00:00' group by customer_sequence ";

so if the date is 2014-12-16 it will show 2014-12-09

i want to be able to run this Query too:

$sql="SELECT * from billing_invoices WHERE due_date <= '".date($_POST["date"], strtotime("-7 days"))."' AND (status = 'Unpaid' or status = 'Part Paid') AND statement = '0000-00-00 00:00:00' group by customer_sequence ";

but the date being returned is the current day rather than -7 days from the POSTED date

Upvotes: 4

Views: 14784

Answers (4)

llanato
llanato

Reputation: 2491

You can use the DATE_SUB / INTERVAL function within MySQL to perform this check.

*NB. - Make sure you escape the value being input into your SQL.

 $sql="SELECT * 
   FROM billing_invoices 
   WHERE due_date <= DATE(DATE_SUB(".$_POST['date'].", INTERVAL -7 DAY))
   AND (status = 'Unpaid' OR status = 'Part Paid') 
   AND statement = '0000-00-00 00:00:00' 
   GROUP BY customer_sequence;";

You can find more information on using DATE_SUB / INTERVAL here: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-sub

Upvotes: 0

annam priyatam
annam priyatam

Reputation: 101

SQL doesn't know what's date($_POST["date"], strtotime("-7 days")).... So you can use this...

$sql="SELECT * from billing_invoices WHERE due_date <= '".DATE_SUB(CURDATE(),INTERVAL 30 DAY)."' AND (status = 'Unpaid' or status = 'Part Paid') AND statement = '0000-00-00 00:00:00' group by customer_sequence ";

Upvotes: 0

Tokk
Tokk

Reputation: 4502

According to the PHP Manual for strtotime there is a second parameter where you can specify a timestamp wich is then used instead of the current time

int strtotime ( string $time [, int $now ] )

So your code should look like this:

date("Y-m-d", strtotime("-7 days", $_POST["date"]))

Perhaps you have to convert your date to a timestamp before. Dependent on your date format in $_POST["date"] this may work:

date("Y-m-d", strtotime("-7 days", strtotime($_POST["date"])))

Upvotes: 9

Arun Kumar
Arun Kumar

Reputation: 1667

use

INTERVEL

$sql="SELECT * from billing_invoices WHERE DATE_SUB(due_date , INTERVAL -7 DAY)' AND (status = 'Unpaid' or status = 'Part Paid') AND statement = '0000-00-00 00:00:00' group by customer_sequence ";

OR

$sql="SELECT * from billing_invoices WHERE DATE_SUB(now(), INTERVAL -7 DAY)' AND (status = 'Unpaid' or status = 'Part Paid') AND statement = '0000-00-00 00:00:00' group by customer_sequence ";

Upvotes: 0

Related Questions