XML and Web Services Reference Guide

JavaScript TimeTracker: JSON and PHP

Last updated Feb 2, 2006.

When you have a lot of things going on in your life, it's easy to wonder: where does the time go? I've tried various time tracking applications, but I need something that's not just simple, but also fast. It has to be available all the time (without getting in my way) and I have to be able to click it and move on rather than filling out forms.

So last week's entry on JavaScript Object Notation (JSON) got me thinking: had technology finally advanced to the point where I could make a time tracker I would actually use? I think the answer is "yes", and I'm willing to spend a couple of entries proving it.

Here's what we're going to do: taking the concept of JSON one step further, we're going to take advantage of a fact even many seasoned web developers don't realize: JavaScript is a real Object Oriented language. Oh, sure, you knew you could use predefined objects such as document and even Image, but did you know you can create your own classes and objects, complete with methods? (Well, where did you think those JavaScript libraries came from?) In other words, you can write a real, honest-to-goodness application for your browser.

And that's just what we're going to do.

Then we're going to take it one step further. JavaScript is now available outside the browser, in environments such as OpenLaszlo and Yahoo! Widgets (formerly Konfabulator). We're going to take our object-oriented JavaScript application and turn it into a widget, increasing our graphical options and providing "always on" capability.

Overall, the application is pretty straightforward: we have a list of projects that are retrieved over the Web via JSON. They're displayed in the window, and when you click on one, it starts accruing time. To change projects, just click the new project. Time data is then sent back to the server to save it.

Will start by creating the basic application, which will give you a good grounding in using existing objects (such as those you receive via JSON). It will also give you a chance to create a simple "Web services interface" for your application, as we'll be sending information back and forth to and from PHP pages. We'll then add functionality and refactor the application to use custom classes and objects, and finally use the application as the basis for a widget.

We'll start by creating the basic service interface.

The skeleton application

The first step is to create any necessary database tables.

The actual database structure is pretty straightforward. Eventually, I would expand this to work for multiple users, but for now, all we need is a table to track the actual projects. I'm using MySQL for this project, but you can use whatever you like. (Change the code accordingly, of course.) Create the projects table:

CREATE TABLE `projects` (
  `id` int(11) NOT NULL auto_increment,
  `project_name` varchar(50) NOT NULL default '',
  `total_time` int(11) NOT NULL default '0',
  `time_this_period` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

As in our previous example, we are tracking the name of the project, total time spent on it, and the time spent during the current period. For example, a system might work on the basis of a weekly timesheet, but track the total time spent on a project over several weeks.

You'll also want to create a couple of projects to start with:

INSERT INTO `projects` VALUES (1, 'InformIT column', 0, 0);
INSERT INTO `projects` VALUES (3, 'Web Service Proposals', 0, 0);
INSERT INTO `projects` VALUES (4, 'Personal', 0, 0);
INSERT INTO `projects` VALUES (5, 'Miscellaneous', 0, 0);
INSERT INTO `projects` VALUES (6, 'Cleaning desk', 0, 0);

The contents of these projects is, of course, up to you. Ultimately, we will make it possible to add new projects through the interface, but for now you'll have to do it using SQL.

Once we have the database settled, the next step is to retrieve the information from it and turn it into an object in JSON form. We'll start by creating a PHP page called timesheet.php that pulls the information out of the database:

<?php

$link = mysql_connect('localhost', 'myuser', 'mypassword')
   or die('Could not connect: ' . mysql_error());
mysql_select_db('workflow') or die('Could not select database');

$query = 'SELECT * FROM projects';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
   echo "<br />";
   echo $line["id"];
   echo "<br />";
   echo $line["project_name"];
   echo "<br />";
   echo $line["total_time"];
   echo "<br />";
   echo $line["time_this_period"];
}
?>

This is standard PHP database code; we make a connection to the database, execute a query, and loop through each of the results, extracting the columns from the associative array returned by mySQL_fetch_array.

Ultimately, we want to send the data to the browser as an object using JSON, so the next step is to convert it to PHP object.

The desired object structure is an object that has two members: the user (hardcoded for now) and an array of "project" objects, which have a member for each column in the database table. In other words, we want a JSON of:

{"user":"Nick","projects":[
{"id":"1","project_name":"InformIT type column","total_time":"0","time_this_period":"0"},
{"id":"3","project_name":"Web Service Proposals","total_time":"0","time_this_period":"0"},
{"id":"4","project_name":"Personal","total_time":"0","time_this_period":"0"},
{"id":"5","project_name":"Miscellaneous","total_time":"0","time_this_period":"0"},
{"id":"6","project_name":"Cleaning desk","total_time":"0","time_this_period":"0"}
]}

Start by creating the PHP objects:

<?php

$link = mysql_connect('localhost', 'myuser', 'mypassword')
   or die('Could not connect: ' . mysql_error());
mysql_select_db('workflow') or die('Could not select database');

$query = 'SELECT * FROM projects';
$projects = array();
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
   $thisProject->id = $line["id"];
   $thisProject->project_name = $line["project_name"];
   $thisProject->total_time = $line["total_time"];
   $thisProject->time_this_period = $line["time_this_period"];
   array_push($projects, $thisProject);

}

$timeSheet->user = "Nick";
$timeSheet->projects = $projects;

?>

We're not adding any methods, using inheritance, and so on, so we don't need to create classes for these objects; we're solely interested in their structure and data. Once we have the objects, we can handle the actual conversion to JSON in one of two ways. The first is to build the text using "brute force", but why do that when JSON libraries are available for virtually any language? In fact, PHP has at least two. In a completely arbitrary choice, I'm using JSON-PHP, but you can also use PHP-JSON, or any other language/library combination listed at JSON.org.

To use it, add it to the page:

<?php

require_once("JSON.phps");

$link = mysql_connect('localhost', 'myuser', 'mypassword')
...
$timeSheet->projects = $projects;

$json = new Services_JSON();
$returnString = $json->encode($timeSheet);

echo "var timesheet = ".$returnString;

?>

If you point your browser to this page (http://www.nicholaschase.com/timesheet/timesheet.php, for example) you'll see output similar to:

var timesheet =
{"user":"Nick","projects":[{"id":"1","project_name":"InformIT
column","total_time":"0","time_this_period":"0"},{"id":"3","project_name":"Web
Service Proposals","total_time":"0","time_this_period":"0"},{"id":"4","project_name":"Personal","total_time":"0","time_this_period":"0"},{"id":"5","project_name":"Miscellaneous","total_time":"0","time_this_period":"0"},{"id":"6","project_name":"Cleaning
desk","total_time":"0","time_this_period":"0"}]}

Notice that this is a JavaScript command assigning the output to the timesheet variable. This is because we'll add it directly to the page using a script element. Make a new file called timesheet.html and add the following:

<html>

<head>
   <title>Timesheet Tracker</title>

   <script type="text/javascript" src="http://www.nicholaschase.com/jsondemo/timesheet.php"></script>

</head>
<body>

   <h1>TimeTracker</h1>

   <script type="text/javascript">
       document.write ("Timesheet user is "+
                              timesheet.user+"<br />");
       document.write ("The second project is "+
               timesheet.projects[1].project_name+".");
   </script>

</body>

</html>

Notice that we were able to access the object as an object from the JavaScript without having to do anything to it; because of the way the browser receives the data, it's interpreted as an object.

Once we have the object, we can access its members as we would with any JavaScript object, via the "dot" notation. Here we retrieve the user member and display it, and then retrieve the projects as an array, reference a single project object, and display its name.

We can also access these members directly to change their values:

<html>
<head>
   <title>Timesheet Tracker</title>

   <script type="text/javascript" src="http://www.nicholaschase.com/jsondemo/timesheet.php"></script>

</head>
<body>

   <h1>TimeTracker</h1>

   <script type="text/javascript">

       timesheet.projects[1].total_time = 120;

       document.write ("Timesheet user is "+
                              timesheet.user+"<br />");
       document.write ("The second project is "+
               timesheet.projects[1].project_name+".");
       document.write ("The second total time "+
                 timesheet.projects[1].total_time+".");
   </script>


</body>
</html>

Ultimately, will want to send the altered information back to the database, so let's look at how we create a JSON string and send it to the server:

<html>
<head>
   <title>Timesheet Tracker</title>

   <script type="text/javascript" src="http://www.nicholaschase.com/jsondemo/timesheet.php"></script>

   <script type="text/javascript" src="./json.js" ></script>

   <script type="text/javascript">

       function prepareSaveForm(){
           var returnObjectStr = JSON.stringify(timesheet);
           //saveForm.elements["timesheet"].value = URLencode(returnObjectStr);
           document.getElementById("timesheetValue").setAttribute("value", 
                                             URLencode(returnObjectStr));
       }

       function URLencode(sStr) {
           return escape(sStr).replace(/\+/g, '%2B').replace(/\"/g,'%22').replace(/\'/g, '%27').replace(/\//g,'%2F');
       }

   </script>

</head>
<body>

   <h1>TimeTracker</h1>

   <form name="saveForm" id="saveForm" action="savetimesheet.php" 
                    onsubmit="prepareSaveForm();return true;" method="post">
       <input type="hidden" name="timesheet" id="timesheetValue" value="" />

       <input type="submit" value="Save Time Information" />
   </form>

</body>
</html>

Starting at the bottom of the pitch, in the body, we're using the easiest possible way of sending data from the browser to a server: a form. The form itself has only two elements. The first is the actual data being sent to the server, which will add in a moment, and the second is the submit button.

When the user submits the form, the browser executes the prepareSaveForm() function, and then submits the data to savetimesheet.php. We'll use the post method because we'll be making changes to the database, and that's a no-no with the get method.

The prepareSaveForm() method uses the JSON library as before, but this time we're using the stringify() method to turn a modified timesheet object into a JSON string.

In order to pass the string in a form, we need to URL encod it, which we'll do with the URLencode() function, courtesy of Real Gagnon.

Finally, we add the value to the form. You can either do this the traditional way, referencing the name of the form in the element, or you can use the DOM and access the element using getElementById().

Now that we're sending the data, we'd better retrieve it. Create another PHP page, savetimesheet.php, and add code to display the received information:

<?php

echo $_POST["timesheet"];
echo "<br />";
echo urldecode($_POST['timesheet']);

?>

Now we can use the library to convert the string back into an object:

<?php

  require_once("JSON.phps");
  $json = new Services_JSON();

  $timesheet = $json->decode(urldecode($_POST["timesheet"]));

?>

Finally, we save the data back to the database:

<?php
 require_once("JSON.phps");
 $json = new Services_JSON();

 $timesheet = $json->decode(urldecode($_POST["timesheet"]));

 $link = mysql_connect('localhost', 'myuser', 'mypassword')
   or die('Could not connect: ' . mysql_error()); 
 mysql_select_db('workflow') or die('Could not select database');


 $projects = $timesheet->projects;

 for ($i=0; $i<count($projects); $i++){
   $thisProject = $projects[$i];
   $id = $thisProject->id;
   $project_name = $thisProject->project_name;
   $total_time = $thisProject->total_time;
   $time_this_period = $thisProject->time_this_period;

   $sql = "update projects set total_time = ".$total_time.", time_this_period = ".$time_this_period." where id = ".$id;
   $result = mysql_query($sql) or die('Query failed: ' . mysql_error());
  if ($result){
    echo "Project '".$project_name."' updated.<br />";
  } else {
    echo "Nothing updated.";
  }

 }
?>

<html><head><title>Save time information</title></head>
<body>
<p>Time information saved.  Please <a href="timesheet.html">continue</a>.</p>

</body>
</html>

You should see the changes in a query of the database.