Generate daily basis by month report in table format
Posted: Tue Oct 13, 2009 11:00 am
I would like to generate a daily basis by month report in table format. The report will show the sum of user for each category in daily basis based on the selected month from drop-down list.
Table layout is as below:

In the query result that I get is as below:

This is mysql query select statement:
SELECT DATE(DateCreated) AS sub_date, categoryID, COUNT(UserID) AS sub_users FROM Magazine WHERE YEAR(DateCreated)=YEAR(CURDATE()) AND MONTH(DateCreated)=$m GROUP BY DATE(DateCreated), CategoryID
I would like know how to show a table layout as above based on the query result that I got and show 'Null' record as '0'. Can we use the mysql select statement to put the sum of users for each category into different column?
As for the information, CategoryID field consists of record where 1=Sport, 2=Entertainment, 3=Business.
Hope someone could help me out. Thanks in advance.
Table layout is as below:

In the query result that I get is as below:

This is mysql query select statement:
SELECT DATE(DateCreated) AS sub_date, categoryID, COUNT(UserID) AS sub_users FROM Magazine WHERE YEAR(DateCreated)=YEAR(CURDATE()) AND MONTH(DateCreated)=$m GROUP BY DATE(DateCreated), CategoryID
I would like know how to show a table layout as above based on the query result that I got and show 'Null' record as '0'. Can we use the mysql select statement to put the sum of users for each category into different column?
As for the information, CategoryID field consists of record where 1=Sport, 2=Entertainment, 3=Business.
Hope someone could help me out. Thanks in advance.