PHP date comparison from MySQL

[wbmdan]

Hello,

I'm retreiving dates in this format from MySQL:

yyyy-mm-dd - For example: 2005-11-24

When I retrive this date from MySQL I need PHP to check to see if date is 7 days old or not.

If it's 7 days old then the script will execute a block of commands.

So...in short..

I need to be able to compare the MySQL date against it being 7 days or not. This is part I'm not sure how to do.

All help appreciated.

Thanks.

[cmircea]

Here is a solution
You can use MySQL function - TO_DAYS

SELECT TO_DAYS(now())-TO_DAYS(date) as nrdays
...

edit: or use MySQL - date_diff() function.
Check mysql manual if you have problems using it.

[wbmdan]

Thank you cmirea.

How do you create the 'now()' function?

Would this work - SELECT TO_DAYS(7) - TO_DAYS(date)?

Sorry, I'm a little confused

Thanks.

[spence_noodle]

I'm after the samething for this project I'm working on.

to set the date and post it into the database is easy:

for the date (today, now): $date = date("y-m-d");

then for the database side it will be something like this:

mysql( "UPDATE section_name SET date_now='$date' " ); 

then I think to check the date in seven days time to see if it's seven days old a cron job maybe required.

a cron job is a little program that runs on the server constantlly processing the commands you have given to it to do.

I don't know how to do it yet as I'm not that far, but maybe someone here might know or do a search in google for Cron Jobs.

[wbmdan]

Yes, thanks for the post. Yes, I will need a cron job. Which I can get setup.

I guess I'm just after the *exact* SQL required to check the date.

In my own words it would be something like this...

select date field from table check to see if date is 7 days old

if the date is ... do 'xxx'

else if the date isn't ... do 'xxx'

...but I need the syntax!??

[cmircea]

Sorry for the brief explanation.

now() is a mysql function, that returns current time.
Just try this query, of course adapted to your code.

SELECT TO_DAYS( now() ) - TO_DAYS(mydate) as nrdays
FROM mytable

You should change mydate with your date field and mytable with your table name

Hope it's clear now.

[cmircea]

so, you have the mysql connection

you have the query

$q = "SELECT TO_DAYS( now() ) - TO_DAYS(mydate) as nrdays FROM mytable";

$result = mysql_query( $q );

$nr_days = mysql_result( $result, 2 );

if( $nr_days > 7 )
{
//....do smth
}
else
{
//...do smth
}


Get the picture?

[iamkoa]

This might help you out:

$query = mysql_query("SELECT * FROM table WHERE `date` <= NOW() AND `date` >= DATE_SUB(NOW(), INTERVAL 7 DAY) LIMIT 5");

if (mysql_num_rows($query)){

// RUN YOUR CODE //

} else {

// DO SOMETHING ELSE //

} 

This will see if anything exists in table "table" where the "date" ("date" is also a table) is not more then seven days old.

[wbmdan]

Thank you cmircea for explanation. That makes much more sense to me know.

I will give it a try.