I have a table like:
CREATE TABLE `user_details` (
`id` bigint(6) unsigned NOT NULL auto_increment,
`user_name` varchar(100) NOT NULL default '',
`user_email` varchar(50) NOT NULL default '',
`user_ref_from` varchar(100) default 'Not Given',
`other_ref` varchar(255) default 'Not Given',
`products` enum('a','b','c') NOT NULL default 'c',
`last_download` mediumint(3) NOT NULL default '40',
`want_notify` enum('Yes','No') default 'Yes',
`dateofdown` date NOT NULL default '0000-00-00',
`dn_from_email` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `user_email` (`user_email`),
KEY `NotifyIndex` (`want_notify`),
KEY `dateofdown` (`dateofdown`),
KEY `products` (`products` )
) TYPE=MyISAM;
Whenever a user downloads a trial version I keep his
info in the table. I also send a mail to the user with
the download instrcutions. When the user clicks on the
link - i increment dn_from_email by one to know that
he actually downloaded the app by cliciking on the
link.
I get report for my downloads by executing multiple
queries like:
SELECT dayofmonth(dateofdown),
COUNT(IF(products="a",1,NULL)),
COUNT(IF(products="b",1,NULL)),
COUNT(IF(products="c",1,NULL)) from user_details where
month(dateofdown) = month(curdate()) and
year(dateofdown) = year(curdate()) group by 1;
SELECT dayofmonth(dateofdown),
COUNT(IF(products="a",1,NULL)),
COUNT(IF(products="b",1,NULL)),
COUNT(IF(products="c",1,NULL)) from user_details where
dn_from_email > 0 and month(dateofdown) =
month(curdate()) and year(dateofdown) =
year(curdate()) group by 1;
Is this method optimized enough?
Also, if for some day dn_from_email = 0 for all the
products then the report goes awry as the number of
rows returned from first query != number of queries
returned from second query.
Am I on the correct path?
Regards,
Karam
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
