CODEWITHSUNDEEP
phpmysql
John Siniger
John Siniger

Reputation: 885

Mysql query result not bigger than one month from the current date

Hello I have a database which has a field date inside it which has the following format: 2013-09-03 for example. I am making a mysql query which is displaying results from this database but I would like to modify it this way so it will display only the results which data is not more than 31 days from today or one month , where today must me the date of today get from the server, here is the query so far: if 

(!$result = mysqli_query($con,
 "SELECT id, title, date, date_until, content FROM dashboard WHERE city = 'VC' AND type = 'class' AND  status = '1'"))

for example if today is first of january the results which the query should show are which have date no bigger than 1 oF february.

any help will be appreciated. Thanks

Upvotes: 0

Views: 794

Answers (2)

KGolding
KGolding

Reputation: 380

Try

(!$result = mysqli_query($con,"SELECT id, title, date, date_until, content FROM dashboard WHERE city = 'VC' AND type = 'class' AND status = '1' AND DATEDIFF(date, NOW()) <= 31"));

However, it would be better to pre-calculate the cutoff date to avoid MYSQL having to do the DATEDIFF for all rows, so something like:

$cutoff = date("Y-m-d", strtotime("+31 day"); (!$result = mysqli_query($con,"SELECT id, title, date, date_until, content FROM dashboard WHERE city = 'VC' AND type = 'class' AND status = '1' AND date <= '$cutoff'"));

Upvotes: 0

O. Jones
O. Jones

Reputation: 108686

Try using date arithmetic

 WHERE ...
   AND date <= TODAY() + INTERVAL 1 MONTH

Upvotes: 1

Related Questions