Statistics based on MySQL and PHP

Question

Hi

I'm about to generate some statistics based on the values of a MySQL table. I would like to generate some numbers foreach month of the year and foreach day of the month.

I could of course do all this manually but that doesn't seem like a good approach :) So anybody who has some ideas on how i generate these statistics.

OBS. I would like to get all month of the year even if there isn't any MySQL record for a given month.

BONUS: I got a little bonus question. The table which provides the data for the stats will get about 1000 records per week. I my head that seems like a bad approach over time. Anyone who has a suggestion for a better approach is welcomed. I've thought about creating CSV files instead.

In advance thanks a lot. It's appreciated!

EDIT: As asked for

+---------------+------------+------+-----+-------------------+----------------+
| Field         | Type       | Null | Key | Default           | Extra          |
+---------------+------------+------+-----+-------------------+----------------+
| id            | int(11)    | NO   | PRI | NULL              | auto_increment |
| member_id     | int(4)     | NO   |     | 0                 |                |
| status        | tinyint(1) | NO   |     | 0                 |                |
| timestamp     | timestamp  | NO   |     | CURRENT_TIMESTAMP |                |
+---------------+------------+------+-----+-------------------+----------------+

Answer 1

Something like this?

select count(status) as total, year(timestamp) as yr, month(timestamp) as mnth  from mytable  group by yr,mnth

As to your bonus question, 1000 records a week is not that much. How would switching to a CSV file help? You would still be getting 1000 records per week.

edit

select count(status) as total, year(timestamp) as yr, month(timestamp) as mnth, day(timestamp) as dy  from mytable  group by yr,mnth,dy

Edit 2

select count(status) as total, year(timestamp) as yr, month(timestamp) as mnth, day(timestamp) as dy, to_days(timestamp) daynum  from mytable  group by yr,mnth,dy

I've added a to_days field that would help you spot missing days as you scan through the results, daynum should be sequential.

Edit 3

OK I've had a go at it but it is untested and bear in mind PHP is my 4th or 5th language. I'm pretty sure some of the gurus round here could do it a lot more elegantly.

<?php

$con = mysql_connect("myhost","myusername","mypassword");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("mydatabase", $con);

$result = mysql_query("select count(status) as total, year(timestamp) as yr, month(timestamp) as mnth, day(timestamp) as dy, to_days(timestamp) as daynum  from mytable  group by yr,mnth,dy");

$row = mysql_fetch_array($result);
$counter=$row['daynum']-$row['day']+1; // set up the daynum counter an initiaise to the first day of the month "-$row['day']+1"

//print out any blank rows at the beginning of the month
for ($i = $counter; $i <=$row['daynum'] ; $i++) {
    echo "A blank row";
}

// start to loop through the result set
$finished=false;
do {

if($counter=$row['daynum']){  // if the daynumber of the row matches the counter then  print the row and get the next row 

    echo "an output row from db".$row('dy')."-".$row('mnth')."-".$row('yr')."-----".$row('total');
    $lastday=$row['dy'];
    $lastmonth=$row['mnth'];
    $lastyear=$row['yr'];   

    $row = mysql_fetch_array($result);
    if (!$row) finished=true;

} else { // if the counter if not equal it must be less than $row['daynum'] so print blank rows and increment counter until it matches the current row.

    $mytime = $counter*24*60*60;   //convert days to seconds, because PHP doesn't seem to have a from_days function
    $mydate = strftime("%Y-%m-%d", $mytime); //convert seconds to date
    echo $mydate."a blank row"

    $counter=$counter+1;
    }

} while ( ! finished);


// print out any blank days at the end of the month
$daysinmonth = cal_days_in_month(CAL_GREGORIAN, $lastmnth, $lastyear);

for ($i = ($lastday+1); $i <=$daysinmonth; $i++) {
    echo $i."-".$lastmonth."-".$lastyear." ---  A blank row";
}



mysql_close($con);

?>