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.