i need to track opened,unopened and delivered emails by date. suppose i sent 5 emails yesterday but nothing opened yesterday and today again i sent another 5 emails so the total emails sent today =5. opened today =3(including yesterday's 2 and today's 1 email opened .still unopened 7 which is correct). But i need to display
today's unopened mails count = today's sent mails- today's sent emails opened
Here is my db structure
ID ||| Email ||| Event ||| date**
1 ||| AAA ||| Sent ||| 04-11-2015
2 ||| BBB ||| Sent ||| 04-11-2015
3 ||| CCC ||| Sent ||| 04-11-2015
4 ||| DDD ||| Sent ||| 04-11-2015
5 ||| EEE ||| Sent||| 04-11-2015
6 ||| FFF ||| Sent ||| 05-11-2015
7 ||| GGG ||| Sent ||| 05-11-2015
8 ||| HHH ||| Sent ||| 05-11-2015
9 ||| III ||| Sent ||| 05-11-2015
I0 ||| JJJ ||| Sent ||| 05-11-2015
11 ||| AAA ||| Open ||| 05-11-2015
12 ||| BBB ||| Open ||| 05-11-2015
13 ||| FFF ||| Open ||| 05-11-2015
Use MySQL str_to_date or curdate :-
Select Email,
((select count(*) from table
where str_to_date(date,'%d-%m-%Y')=curdate() and Event='Sent')
-
(select count(*) from table
where str_to_date(date,'%d-%m-%Y')=curdate() and Event='Open')) as unopened
From table limit 1
Today i sent these emails
FFF ||| Sent ||| 05-11-2015
GGG ||| Sent ||| 05-11-2015
HHH ||| Sent ||| 05-11-2015
III ||| Sent ||| 05-11-2015
JJJ ||| Sent ||| 05-11-2015
On these emails opened only 1
FFF ||| Open ||| 05-11-2015
So unopened 4 emails.
ofcourse these are opened today but these mails sent yesterday.
AAA ||| Open ||| 05-11-2015
BBB ||| Open ||| 05-11-2015
I want todays unopened= (today's sent)- (today's sent opened)
unopened = 5-1(today's sent email opened)
so total i will get 4.
To fetch todays records using MySQL, you can try this:
SELECT * FROM tbl_name WHERE date > DATE_SUB(NOW(), INTERVAL 1 DAY);