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.

[PFMaBiSmAd]

Since you cannot do greater or less than comparisons or sorts with dates in that format, the first thing you need to do is change to use a DATE data type. This will also lessen your storage requirements because your current format takes up to 11 bytes while a DATE data type only takes 3 bytes.

[xodus1]

Hi, I am able to get an output with the timestamp, I can use the date () function too if needed. My dilemma is to use a views db with join and output all the information, GROUP by date. Please advise.