SitePoint Sponsor

User Tag List

Results 1 to 20 of 20
  1. #1
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to compare a date from mysql with current date and send a notification email

    I have database entrys that have a date column ( mySQL date type ) in this format(2011-5-30). I want to compare that date with the current server date and with 3 days before that day, to automatically send an email to a specified address.
    Last edited by AnthonySterling; Jun 1, 2011 at 03:01. Reason: Reduced ridiculously size font :-/

  2. #2
    SitePoint Addict Kokos's Avatar
    Join Date
    Nov 2005
    Location
    The Netherlands
    Posts
    205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You are looking for strtotime(). If you put the date from MySQL through strtotime() it will return a timestamp which you can then compare to time() or strtotime('-3 days').

    Taking over the web one pixel at a time.
    Currently working @ CodeCreators

  3. #3
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    or you could use mySQL's query to return only the rows you care about...

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,249
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by StarLion View Post
    or you could use mySQL's query to return only the rows you care about...
    that's correct

    this is also far more efficient

    not to mention simpler

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    but how it'll send email automatic ?without open the site

  6. #6
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    You'll need to use a cron job for that.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  7. #7
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    <?php
    error_reporting
    (-1);
    ini_set('display_errors'true);

    #connect to database

    $sql sprintf(
      
    "SELECT email FROM table WHERE date > DATE_SUB('%s', , INTERVAL 3 DAY);",
      
    date(DATE_ISO8601)
    );

    $res mysql_query($sql);

    while(
    $record mysql_fetch_assoc($res)){
      
    mail(
        
    $record['email'],
        
    'Subject',
        
    'Yo dawg, I herd you like emails so we put an email in your email so you can mail as you mail'
      
    );
    }
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  8. #8
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Actually, you'll probably get better performance with the following query.
    PHP Code:
    <?php
    error_reporting
    (-1);
    ini_set('display_errors'true);

    #connect to database

    $sql sprintf(
      
    "SELECT email FROM table WHERE date > '%s';",
      
    date('Y-m-d'strtotime('-3 days'))
    );

    $res mysql_query($sql);

    while(
    $record mysql_fetch_assoc($res)){
      
    mail(
        
    $record['email'],
        
    'Subject',
        
    'Yo dawg, I herd you like emails so we put an email in your email so you can mail as you mail'
      
    );
    }
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  9. #9
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by AnthonySterling View Post
    Actually, you'll probably get better performance with the following query.
    PHP Code:
    <?php
    error_reporting
    (-1);
    ini_set('display_errors'true);

    #connect to database

    $sql sprintf(
      
    "SELECT email FROM table WHERE date > '%s';",
      
    date('Y-m-d'strtotime('-3 days'))
    );

    $res mysql_query($sql);

    while(
    $record mysql_fetch_assoc($res)){
      
    mail(
        
    $record['email'],
        
    'Subject',
        
    'Yo dawg, I herd you like emails so we put an email in your email so you can mail as you mail'
      
    );
    }
    this code selected all rows of table and sent it to mail with out compare date

  10. #10
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    You may have to convert the string to a date with STR_TO_DATE, I'm not entirely sure. Work on getting the query right, then pop it in.

    Good luck.

    PHP Code:
    <?php
    error_reporting
    (-1);
    ini_set('display_errors'true);

    #connect to database

    $sql sprintf(
      
    "SELECT email FROM table WHERE date > STR_TO_DATE('%s','%%Y-%%m-%%d');",
      
    date('Y-m-d'strtotime('-3 days'))
    );

    $res mysql_query($sql);

    while(
    $record mysql_fetch_assoc($res)){
      
    mail(
        
    $record['email'],
        
    'Subject',
        
    'Yo dawg, I herd you like emails so we put an email in your email so you can mail as you mail'
      
    );
    }
    Actually, a quick test on my local box says that the original format should work OK. Are you sure the column has a datatype of DATE ?
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  11. #11
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    (Psst. Are you sure that > shouldnt be a < , Anthony? I cant tell from the OP what sort of 'compare' he meant)

  12. #12
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the same problem the date at the db_table at this format '2011-10-01'

  13. #13
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StarLion View Post
    (Psst. Are you sure that > shouldnt be a < , Anthony? I cant tell from the OP what sort of 'compare' he meant)
    I'm assuming the OP meant everything between now and 3 days ago.

    Can you post the output from the MySQL command, SHOW CREATE TABLE tablename please?
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  14. #14
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code SQL:
    CREATE TABLE `stats` (
     `id` INT(20) NOT NULL AUTO_INCREMENT,
     `con_id` INT(20) NOT NULL,
     `year` VARCHAR(20) COLLATE utf8_unicode_ci NOT NULL,
     `years` DATE NOT NULL,
     `file` mediumblob,
     `yearlast` DATE NOT NULL,
     `cstatus` VARCHAR(50) COLLATE utf8_unicode_ci NOT NULL,
     PRIMARY KEY  (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=98 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

    compare with column yearlast

  15. #15
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    I'm all out of ideas, can you change the date on the following query and see if it returns the rows you're expecting?
    Code:
    SELECT id FROM stats WHERE yearlast > '2011-01-31'
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  16. #16
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if used last code work good

  17. #17
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Let's pop this over to the MySQL forum and see what those folk suggest.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  18. #18
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry,it gave me all rows great than today i want the one after 3 days

  19. #19
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code MySQL:
    select email from stats
    where yearLast <= CURRENT_DATE
    and yearLast >= ( CURRENT_DATE - INTERVAL 3 day )

    OR

    Code MySQL:

    bazz

  20. #20
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks this Query work good


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
  •