MySql date query not returning expected results

Question

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'));

Ram Sharma · Accepted Answer · 2013-10-30 11:53:29Z

1

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;

answered Oct 30, 2013 at 11:53

Ram Sharma

8,8157 gold badges48 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1532468 Over a year ago

This is piggin confusing :-) I am not sure if it is an issue in the db or the php. I shall try. Thanks

user1532468 Over a year ago

The best way to describe it. Imagine you have an account that expires on 30/10/2013 a company would tell you 1 month previous that your account expires on that date. Thanks

Collectives™ on Stack Overflow

MySql date query not returning expected results

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related