CODEWITHSUNDEEP
php
user1532468
user1532468

Reputation: 1751

MySql date query not returning expected results

I have a piece of code that queries a MySql db and return results based on the date. What is supposed to happen is that if there is a date in the destroy_date field that contains todays date - 1 month, there is another piece of code that should add 1 month to the displayed results to warn a user that this file must be destroyed on that date. For some reason this isn't happening and is displaying older dates and not displaying the expected results.

As an example, let us say that a file has a destroy date of 30/10/2013. I need to display 1 month advance notice that this file will be destroyed on that date. So I need to display on the 30/09/2013 that this file will be destroyed on the 30/10/2013.

I would be grateful if someone could point out my error or if there is more info needed, then I shall be happy to include such. Many thanks

MySql query

$query = "SELECT * FROM boxes WHERE customer = '$_SESSION[kt_idcode_usr]' AND destroy_date <= DATE(NOW() - INTERVAL 1 MONTH) AND destroy_date != '0000-00-00' AND status = 1 order by destroy_date desc";

php code to add 1 month

$rowdate = date("d/m/Y", strtotime($row['destroy_date'] . ' + 1 MONTH'));

Upvotes: 0

Views: 59

Answers (1)

Ram Sharma
Ram Sharma

Reputation: 8819

if you have issue in fetching of record than add the below condition in you sql.

date(expiry_date) BETWEEN date(NOW()) AND date(NOW()) + INTERVAL 1 MONTH

if you have issue in displaying date than try the code below.

$newdate = strtotime ( '+ 1 MONTH' , strtotime ( $row['destroy_date'] ) ) ;
//assuming your date is in yyyy/mm/dd format
$newdate = date ( 'd/m/Y' , $newdate );
echo $newdate;

Upvotes: 1

Related Questions