Count from multiple tables and group output by date

[xodus1]

Hi,

I am trying to build an SQL statement for a reporting tool, the idea is to count from multiple statements and group my output by date, I've also learnt that GROUP is an intensive statement - 'not sure' again. I'm a newbie to PHP so please help.

I just need to know what the best way to achieve this is,

table 1 - Users

dateregistered, users

8/1/2008  John
8/1/2008  Derrick
8/2/2008  Mary
8/3/2008  Gary
8/3/2008  Truman
8/9/2008  Arnold

table 2 - characters

date created, character

8/1/2008  Axe
8/2/2008  hunter
8/2/2008  druid
8/3/2008  druid
8/3/2008  hunter
8/4/2008  mage

Notice the table dates dont match

What I need is to count the users and characters between form input dates and then group them for display by date the output/result should be as follows -

date search form input - 

$start = 7/1/2008
$end = 9/1/2008


output = 

Date     No.of users registered     No. of characterscreated

8/1/2008  	2    			1
8/2/2008  	1    			2
8/3/2008  	2    			2
8/4/2008  	0    			1
8/9/2008  	1    			0

Here's the code I'm trying which does not work -

$query = mysql_query("CREATE ALGORITHM = TEMPTABLE VIEW myView
     (playerdate, racedate)  AS
     SELECT a.date_reg_acclaim, b.date_raceend
     FROM tbl_players a, tbl_races b 
     WHERE a.date_reg_acclaim >= '$start' 
        AND a.date_reg_acclaim <='$end' 
        AND b.date_raceend >= '$start' 
        AND b.date_raceend <='$end'");
     	
$result = mysql_query("SELECT `playerdate`, 
	COUNT(playerdate) AS players WHERE playerdate >= '$start' AND playerdate <='$end',
                COUNT(racedate) AS races WHERE racedate >= '$start' AND racedate <='$end', 
	FROM myView WHERE playerdate >= '$start' AND playerdate  <='$end' 
	GROUP BY `playerdate`");		 
$numrows = mysql_num_rows($result) or die ('No records found');

I've tried various other methods and I'm not even sure if using a views db is the best way to achieve this. Please advise.

[digital-ether]

Hi,

I am trying to build an SQL statement for a reporting tool, the idea is to count from multiple statements and group my output by date, I've also learnt that GROUP is an intensive statement - 'not sure' again. I'm a newbie to PHP so please help.

I just need to know what the best way to achieve this is,

table 1 - Users

dateregistered, users

8/1/2008  John
8/1/2008  Derrick
8/2/2008  Mary
8/3/2008  Gary
8/3/2008  Truman
8/9/2008  Arnold

table 2 - characters

date created, character

8/1/2008  Axe
8/2/2008  hunter
8/2/2008  druid
8/3/2008  druid
8/3/2008  hunter
8/4/2008  mage

Notice the table dates dont match

What I need is to count the users and characters between form input dates and then group them for display by date the output/result should be as follows -

date search form input - 

$start = 7/1/2008
$end = 9/1/2008


output = 

Date     No.of users registered     No. of characterscreated

8/1/2008  	2    			1
8/2/2008  	1    			2
8/3/2008  	2    			2
8/4/2008  	0    			1
8/9/2008  	1    			0

Here's the code I'm trying which does not work -

$query = mysql_query("CREATE ALGORITHM = TEMPTABLE VIEW myView
     (playerdate, racedate)  AS
     SELECT a.date_reg_acclaim, b.date_raceend
     FROM tbl_players a, tbl_races b 
     WHERE a.date_reg_acclaim >= '$start' 
        AND a.date_reg_acclaim <='$end' 
        AND b.date_raceend >= '$start' 
        AND b.date_raceend <='$end'");
     	
$result = mysql_query("SELECT `playerdate`, 
	COUNT(playerdate) AS players WHERE playerdate >= '$start' AND playerdate <='$end',
                COUNT(racedate) AS races WHERE racedate >= '$start' AND racedate <='$end', 
	FROM myView WHERE playerdate >= '$start' AND playerdate  <='$end' 
	GROUP BY `playerdate`");		 
$numrows = mysql_num_rows($result) or die ('No records found');

I've tried various other methods and I'm not even sure if using a views db is the best way to achieve this. Please advise.

What you explained to be the table structure and what you currently have as queries do not match. I assume there is more to the tables and you're just showing a simplified version in the table structure?

Going with what you explained to be the table structure, and not your queries.

SELECT count(users) as users from users where datecreated >= '$start' and datecreated <= '$end';

And do the same for characters.

Or you can join them into a single query with a JOIN, or just a select from both the tables.