What I’m listening to atm:
Sasha - Live @ Creamfields 2003
At the beginning of this semester, a couple of friends and myself set out on a mission. A professor of one of our classes has an uncanny knack for inadvertently saying “k” multiple times per sentence.
We vowed to record every instance of this phenomenon, and keep crude records on which we could reflect, as well as use these counts of k’s to forewarn future generations of the undeniable and unavoidable distraction.
We started off with the basics; simply keeping tallies on spare pieces of paper, or in back covers of notebooks, summing and averaging along the way. Eventually, my fingers got tired of repeatedly hammering numbers into a calculator, and I began thinking of alternatives to this madness.
Learning quite a bit of .NET/SQL at a summer internship, I decided that we (I) could make this into a data-driven web app, but opted for a PHP front-end to all the craziness. I felt that we also needed some sort of graphical representation of our meticulous record-keeping (y’know.. for science).
And so it begins…
First, we need to decide what exactly we need in order for this vision to materialize.
- A database to store all the records.
- An interface to record the “k”s.
- A reports page to show our findings (graphs and stats).
Lets begin–
1. A database to store all the records.
For the sake of simplicity, the database I created contained only one table:
CREATE TABLE `Records` ( `RecordId` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `k` INT( 1 ) NOT NULL DEFAULT '1', `CreatedDateTime` DATETIME NOT NULL )
You’ll notice that i created a ‘RecordId’ column to keep track of individual records. This column is not necessarily needed, but I thought that it may be useful if I ever expand the functionality.
The ‘k’ column is where we’ll actually be specifying a counted “k”. The Int only needs to have a size of 1 because there will theoretically be no 2 “k”s at any given moment in time (depending on the next field’s granularity).
The ‘CreatedDateTime’ column, which is DateTime format, will have the capability of recording to the thousandths of a second, but as you’ll see further down, in our PHP code, I will only be recording it down to seconds.
Now that the database table is set up, we can start at the PHP code!
2. An interface to record “k”s.
This part is fairly straightforward, in my opinion. I designed it to be a simple page with just a single button in the middle; when clicked, the form would POST (to itself), and on page load, record a “k” in the DB.
// Save this file as index.php
<html>
<head>
<?
// The DateTime when this page is loaded, will be use w/ the DB insert
$now = date("Y-m-d H:i:s"); // ex. 2009-10-28 22:37:14
// Database connection info
$user = "dbUsername";
$pass = "dbPassword";
$db = "dbName";
$server = "localhost";
// Since the page is posting to itself, check to see
// if our specified page function is passed in. This is
// a very inefficient way of doing this, but it works.
if ((isset($_GET['func'])) && (strlen($_GET['func']) == 2))
{
// The above 'strlen' is a quick and dirty way to sanitize
// input. Usually doesnt work to well w/ anything above 2.
$func = $_GET['func'];
if ($func =='sk')
{
//if the parameter passed in = "sk" (or, save k"), write to db
mysql_connect($server, $user, $pass);
@mysql_select_db($db) or die();
$query = "INSERT INTO Records (k, CreatedDateTime) VALUES (1, $now)";
mysql_query($query);
}
}
?>
</head>
<body>
<form action='index.php?func=sk' method='post'>
<input type='Submit' value='k?'>
</form>
</body>
</html>Now, keep in mind that this is a very very simple example, and was coded in about 10 minutes, and is lacking styling and code efficiency/finesse.
All that’s left to do now is to show all those lovely “k”s in a fancy, flashy graph.
3. A reports page to show our findings (graphs and stats).
There are plenty of flash and javascript based graphing code-ins out there, but I chose to use FusionCharts because there are flexible and amazing. There is a free version on their site called FusionCharts FREE, but for these examples, I am using FusionCharts v3. The syntax of some things are different between the two versions, so if you use the free version, my code may not work as expected. All the installation and customization instructions are included in the download packages.
For the graph I was creating, i wanted to specify certain times on a certain date to show our recorded “k”s, so there was some fancy PHP work involved to manipulate strings and DateTimes, which is shown below.
// Save this file as graph.php
<html>
<head>
<?
// Include FusionCharts/FusionCharts.php which contains functions to help w/ chart embedding
include("FusionCharts/FusionCharts.php");
// Also include some javascript to help w/ chart embedding
?>
<SCRIPT LANGUAGE="Javascript" SRC="FusionCharts/FusionCharts.js"></SCRIPT>
<?
// Database connection info
$user = "dbUsername";
$pass = "dbPassword";
$db = "dbName";
$server = "localhost";
// The date we would like to see the records of
$date = "2009-10-28";
// The start and end times on the above date to see the records of
$startDateTime = $date . " 18:00:00";
$endDateTime = $date . " 20:45:00";
// We're going to need a loop to go through each minute of the above time slot
// and get the sum of all records for that minute from the DB. Then record that
// sum to an array, along with the corresponding DateTime
// this will be our incrementing variable for the loop
$incrementingDateTime = strtotime($startDateTime);
mysql_connect($server, $user, $password);
@mysql_select_db($db) or die();
$i = 0;
while ($incrementingDateTime <= strtotime($endDateTime))
{
$queryDateTime = date("Y-m-d H:i", $incrementingDateTime);
$query = "
SELECT SUM(k)
FROM Records
WHERE CreatedDateTime LIKE '%$queryDateTime%'
";
$queryResult = mysql_query($query);
$resultArray = mysql_fetch_row($queryResult);
$count = $row[0]; // Number of "k"s in that given minute.
if (!strlen($count) >= 1) // If no records returned, returns empty string,
{ // so we turn that empty string into a "0".
$count = "0";
}
$graphData[$i][1] = date("H:i", $incrementingDateTime);
$graphData[$i][2] = $count;
$incrementDateTime = #incrementDateTime + 60; // Add a minute
$i++;
}
mysql_close();
// Lets build the XML for the graph!
// Graph type, labels, etc. Here's the one i created. XML API is in the FusionCharts docs
$strXML = "<chart caption='Ks Per Minute' subcaption='" . $date . "' showValues='0' showLabels='0' labelDisplay='ROTATE' slantLabels='1' showAlternateVGridColor='1' alternateVGridColor='D9E5F1' alternateVGridAlpha='100' animation='1' >";
// Graph data. This if/else statement will make it so the x-axis labels are only visible every quarter hour.
foreach ($arrData as $arrSubData)
{
if (substr($arrSubData[1], -2) == '00' || substr($arrSubData[1], -2) == '15' || substr($arrSubData[1], -2) == '30' || substr($arrSubData[1], -2) == '45')
{
$strXML = $strXML . "<set label='" . $arrSubData[1] . "' value='" . $arrSubData[2] . "' showName='1' />";
}
else
{
$strXML = $strXML . "<set label='" . $arrSubData[1] . "' value='" . $arrSubData[2] . "' />";
}
}
// Close out chart data
$strXML = $strXML . "</chart>";
?>
</head>
<body
// Embedding the chart!
<?
echo renderChart("FusionCharts/Line.swf", "", $strXML, "ksPerMinute", 900, 300, false, false);
?>
</body>
</html>So, that’s pretty much it.
The entire project only took me a day or so to put together. Most of that time was styling the actual graph. Feel free to re-use this code and make it your own.
FusionCharts has an incredible number of different graphs and configurations, so if you’re looking to do any type of web-app charting or graphing, head on over to www.FusionCharts.com and see what you can create.
enjoy!
Photo by Verena Vianna