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

Databases
1

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.

2

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’).

3

or you could use mySQL’s query to return only the rows you care about…

4

that’s correct

this is also far more efficient

not to mention simpler

:slight_smile:

5

but how it’ll send email automatic ?without open the site

6

You’ll need to use a cron job for that.

7 

<?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'
  );
}
8

Actually, you’ll probably get better performance with the following query.


<?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'
  );
}
9

this code selected all rows of table and sent it to mail with out compare date

10

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
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 ?

11

(Psst. Are you sure that > shouldnt be a < , Anthony? I cant tell from the OP what sort of ‘compare’ he meant)

12

the same problem the date at the db_table at this format ‘2011-10-01’

13

I’m assuming the OP meant everything between now and 3 days ago. :confused:

Can you post the output from the MySQL command, SHOW CREATE TABLE tablename please?

14 
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

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?


SELECT id FROM stats WHERE yearlast > '2011-01-31'
16

if used last code work good

17

Let’s pop this over to the MySQL forum and see what those folk suggest.

18

sorry,it gave me all rows great than today i want the one after 3 days

19 

select email from stats
where yearLast <= CURRENT_DATE
and yearLast >= ( CURRENT_DATE - INTERVAL 3 day )

OR


select email
  from stats
where yearlast BETWEEN CURRENT_DATE
                        AND CURRENT_DATE - INTERVAL 3 DAY

bazz

20

thanks this Query work good :slight_smile: