Ever wonder how many posts were made over the past few days or months? This is a very simple data visualization tool that shows you exactly how many.

To set it up you need to add your tables to the $table array. This will count the number of rows in each table matching against the "date" field. You may need to adjust this according to whether or not you named this field "date". It can be a date or datetime field.

Note: This is using the now-deprecated Google Image Charts API which was used for simplicity.

<?php
/****************************************************************************/
// Connect to the database.

$server = "localhost";
$user = "root";
$pass = '';
$database = "my_table";

$db = mysql_connect($server, $user, $pass);
if(!$db) die('Could not connect: ' . mysql_error());
mysql_query("use $database", $db);

/****************************************************************************/
// Configuration

// How far to go back

$days_back = 60;
$months_back = 12;

// Colors

$days_color = '2652A3';
$months_color = '76A4FB';

// Define the tables to report on.

$tables = array(
    array('table_name' => 'activities', 'table_title' => 'Table 1'),
    array('table_name' => 'comments', 'table_title' => 'Table 2')
);

/****************************************************************************/

function get_stats($table, $date, $string) {
    global $db;

    $q = "select count(*) as total from $table where date like '$date'";

    $res = mysql_query($q, $db);
    $total = mysql_fetch_assoc($res);

    if(!is_null($string)) $string .= ',';
    $string .= $total['total'];

    return $string;
}

/********************************************************************************/
// Displays the chart as an image using Google charts.

function print_chart($string, $x, $title) {
    global $days_back;
    global $months_back;
    global $days_color;
    global $months_color;

    // Find the max value and add 10% to it for the y axis

    $max = 0;
    $s = explode(',', $string);
    foreach($s as $v) {
        if($v > $max) $max = round($v + ($v * .1));
    }

    // Determine the color and the title

    if($x == $days_back) {
        $color = $days_color;
        $title .= " days back";
    }   
    else {
        $color = $months_color;
        $title .= " months back";
    }   

    print "<img src='http://chart.apis.google.com/chart?chm=N,000000,0,-1,10&chxr=0,$x,0|1,0,$max&chxt=x,y&chs=1000x270&cht=bvg&chco=$color&chbh=r,1,.1&chds=0,$max&chd=t:$string&chtt=$title'><br><br>";
}

/********************************************************************************/
// Calculate the number of rows $months_back months.

for($x = $months_back; $x >= 0; $x--) {
    $date = date("Y-m".'%', strtotime(date("Y-m-15")." -$x month"));

    foreach($tables as $table) {
        $table_name = $table['table_name'];
        $table_title = $table['table_title'];
        $append = $monthly[$table_title];
        $monthly[$table_title] = get_stats($table_name, $date, $append);
    }
}

/********************************************************************************/
// Calculate the number of rows $days_back days.

for($x = $days_back; $x >= 0; $x--) {
    $date = date("Y-m-d".'%', strtotime(date("Y-m-d")." -$x day"));

    foreach($tables as $table) {
        $table_name = $table['table_name'];
        $table_title = $table['table_title'];
        $append = $daily[$table_title];
        $daily[$table_title] = get_stats($table_name, $date, $append);
    }
}

/********************************************************************************/
// Display the data as Google Charts images.

$border = "<div style='border-bottom: 20px solid #CCC; margin-bottom: 20px;'></div>";
print $border;

foreach($monthly as $key => $month) {
    $day = $daily[$key];
    print_chart($day, $days_back, $key);
    print_chart($month, $months_back, $key);
    print $border;
}

mysql_close($db);
?>