SitePoint Sponsor |
|
User Tag List
Results 1 to 9 of 9
Thread: PHP date comparison from MySQL
-
Nov 24, 2005, 12:30 #1
- Join Date
- Jan 2005
- Location
- North America
- Posts
- 336
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
PHP date comparison from MySQL
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.
-
Nov 24, 2005, 12:55 #2
- Join Date
- Nov 2005
- Posts
- 85
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Nov 24, 2005, 13:01 #3
- Join Date
- Jan 2005
- Location
- North America
- Posts
- 336
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Nov 24, 2005, 13:01 #4
- Join Date
- Jan 2004
- Location
- uk, Leeds (area)
- Posts
- 1,264
- Mentioned
- 2 Post(s)
- Tagged
- 1 Thread(s)
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:
PHP Code:mysql( "UPDATE section_name SET date_now='$date' " );
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."Don't you just love it when you solve a programming bug only to create another."
-
Nov 24, 2005, 13:40 #5
- Join Date
- Jan 2005
- Location
- North America
- Posts
- 336
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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!??
-
Nov 24, 2005, 13:41 #6
- Join Date
- Nov 2005
- Posts
- 85
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Nov 24, 2005, 13:45 #7
- Join Date
- Nov 2005
- Posts
- 85
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?
-
Nov 24, 2005, 13:51 #8
This might help you out:
PHP Code:$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 //
}
-
Nov 24, 2005, 13:59 #9
- Join Date
- Jan 2005
- Location
- North America
- Posts
- 336
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thank you cmircea for explanation. That makes much more sense to me know.
I will give it a try.
Bookmarks