CODEWITHSUNDEEP
phpmysqldate
Brandin Arsenault
Brandin Arsenault

Reputation: 152

PHP Count Between Date and Todays Date

In my dates database I have a table of dates that have two followups to be completed, one after 30 days, one after 60 days. I need to build a page that uses the MySQL query to pull all dates from the dates table that have a 30day value of No (which I can do). Now the tricky part is, I need it to only output the dates that meet that criteria, and are 30 days from the current date.

For example: August 4 & 6 have a 30day value of No, August 5 has a 30day value of Yes. Today's date is September 4. 30-days prior would be August 5.

I need the query to only display August 4 in this case, since it hasn't been 30 days since August 6 and August 5 has already been done.

I am unsure what kind of function to use to do this counting. I appreciate your help

EDIT:

Date - 30day Value
July 1 - Yes
July 5 - No
August 1 - No
August 5 - No
August 6 - Yes

Today's Date is September 2.

The table would display July 5 and August 1, as their 30day values are No, and they are more than 30 days from todays date.

Upvotes: 1

Views: 778

Answers (3)

Chetan Naik
Chetan Naik

Reputation: 151

you can fetch both the dates and use the php function

$prevdate = date_create("2013-03-15");
$currdate = date_create("2013-12-12");
$diff = date_diff($prevdate,$currdate);
echo $diff->format("%R%a days");

Output

272 days

Upvotes: 1

Clay
Clay

Reputation: 4760

MySQL's DATEDIFF function allows you to subtract 2 dates in a query.

http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_datediff

DATEDIFF() returns expr1 − expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.

For example:

SELECT some_id, date_column
FROM date_table
WHERE DATEDIFF(CURDATE(), date_column) = 30

You could also select both 30 and 60 days like this and also have a cutoff date of 60 days so it's not searching the whole table:

SELECT some_id, date_column
FROM date_table
WHERE date_column>=DATE_SUB(CURDATE(), INTERVAL 60 DAY)
AND DATEDIFF(CURDATE(), date_column) IN (30, 60)

And since I'm making some assumptions with my understanding of what you're asking, you may also want to do this which will return the results as 'Yes' or 'No' in your result set:

SELECT some_id, date_column,
CASE DATEDIFF(CURDATE(), date_column)
    WHEN 60 THEN 'Yes'
    WHEN 30 THEN 'Yes'
    ELSE 'No'
END CASE AS is_3060_day
FROM date_table
WHERE date_column>=DATE_SUB(CURDATE(), INTERVAL 60 DAY)

Alternatively if you want to accomplish this on the PHP side, you could use PHP's date_diff function:

http://php.net/manual/en/function.date-diff.php

function dateDifference($date_1 , $date_2 , $differenceFormat = '%a' )
{
    $datetime1 = date_create($date_1);
    $datetime2 = date_create($date_2);

    $interval = date_diff($datetime1, $datetime2);

    return $interval->format($differenceFormat);

}
$result = dateDifference($date1, $date2)
if ($result==30 || $result==60) {
    // Do something
}

Upvotes: 1

dy'sback
dy'sback

Reputation: 124

You should use DATEDIFF function:

SELECT ....
FROM your_table
WHERE DATEDIFF(CURDATE(), event_date) = 30

Where event_date is example of your date column.

Upvotes: 2

Related Questions