SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Addict
    Join Date
    Jan 2005
    Location
    North America
    Posts
    336
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question 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.

  2. #2
    SitePoint Enthusiast
    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.

  3. #3
    SitePoint Addict
    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.

  4. #4
    SitePoint Wizard spence_noodle's Avatar
    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' " ); 
    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.
    "Don't you just love it when you solve a programming bug only to create another."

  5. #5
    SitePoint Addict
    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!??

  6. #6
    SitePoint Enthusiast
    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.

  7. #7
    SitePoint Enthusiast
    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?

  8. #8
    SitePoint Zealot iamkoa's Avatar
    Join Date
    Mar 2005
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 //


    This will see if anything exists in table "table" where the "date" ("date" is also a table) is not more then seven days old.
    Iamkoa, LLC
    Podlodge - Software, tools, and hacks for the iPod

  9. #9
    SitePoint Addict
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •