Contents


Track time spent on projects with IBM Cloud, Part 1

Build a custom web application with IBM Cloud, PHP, and MySQL

Browser-based time-tracking tool enables users to record time spent on customer projects

Comments

Content series:

This content is part # of # in the series: Track time spent on projects with IBM Cloud, Part 1

Stay tuned for additional content in this series.

This content is part of the series:Track time spent on projects with IBM Cloud, Part 1

Stay tuned for additional content in this series.

For professionals who bill on an hourly basis—lawyers, accountants, and so on—time tracking is a critical part of the billing process. The adage time is money is almost literally true in these cases: The hours these professionals work correlate directly to the revenue they earn. And so a time-tracking tool that allows them to easily enter work hours and obtain summary reports at the end of the week or month is of critical importance.

In this tutorial, I walk you through the process of building a simple time-tracking tool and deploying it on IBM Cloud. Along the way, I'll introduce you to the ClearDB MySQL database service on IBM Cloud, which gives you a scalable and secure database in the cloud for your data.

The goal of this two-part article is to build a useful database-backed application with PHP and MySQL and leverage the security, scalability, and flexibility of IBM Cloud to deploy and host it.

Get the sample code on GitHubTry the demo

What you will need

The example application described in this article allows a user to define one or more projects, each representing a customer job. The user can then enter, on an ongoing basis, the hours worked on each project, together with a brief description.

The entered data is saved to an online database, and a web-based dashboard allows the user to view a report of hours worked per project. Alternatively, the user can download a CSV-formatted file with the same data. The entire application is mobile friendly, enabling users to enter data and view reports even when on the move (perfect for professionals who don't have a fixed office or place of work).

Behind the scenes, the application uses IBM Cloud's ClearDB MySQL database service, which provides a MySQL database in the cloud. The application also employs Bootstrap to create a mobile-optimized interface, and the Slim PHP micro-framework to process requests.

Before starting, make sure you have everything that you'll need:

Note: Any application that uses the ClearDB service must comply with the ClearDB Terms of Service. Similarly, any application that uses the IBM Cloud Platform must comply with IBM Cloud's terms of use. Before beginning your project, spend a few minutes reading these requirements and ensuring that your application complies with them.

1

Install dependencies

The first step is to initialize a basic application with the Slim PHP micro-framework. This can be downloaded and installed using Composer, the PHP dependency manager. Use the following Composer configuration file, which should be saved to $APP_ROOT/composer.json ($APP_ROOT refers to your project directory.)

{
    "require": {
       "php": ">=7.0.0",
       "slim/slim": "^3.9",
       "slim/php-view": "^2.2"
    }
}
  1. Install using Composer with this command:
    shell> composer install
  2. Once the necessary components have been downloaded via Composer, create the directories $APP_ROOT/public for all web-accessible files and $APP_ROOT/views for all views, where $APP_ROOT refers to the application directory.
    shell> cd myapp
    shell> mkdir public views
  3. Then, create the file $APP_ROOT/config.php file with the following information (you'll fill in the placeholders in step 3):
    <?php
    $config = [
      'settings' => [
        'displayErrorDetails' => true,
        'db' => [
          'hostname' => 'DB-HOST-NAME',
          'username' => 'DB-USER-NAME',
          'password' => 'DB-USER-PASSWORD',
          'name' => 'DB-NAME',
        ]
      ]
    ];
  4. To make it easier to access the application, you can also define a new virtual host in your development environment named myapp and point its document root to $APP_ROOT/public. You should also add an .htaccess file to the $APP_ROOT/public directory with the following settings:
    <IfModule mod_rewrite.c>
      RewriteEngine On
      RewriteCond %{REQUEST_FILENAME} !-f
      RewriteCond %{REQUEST_FILENAME} !-d
      RewriteRule ^ index.php [QSA,L]
    </IfModule>

Using a virtual host mapped to the $APP_ROOT/public directory allows you to access application routes directly using the virtual host name. For example, use http://myapp.localhost/projects/save instead of http://localhost/public/projects/save. See "Related topics" below to learn more about virtual hosts and web server configuration for Slim framework applications.

2

Create a basic route handler and template

The next step is to initialize the Slim framework. Do this with a script containing callbacks for the application's routes, with each callback defining the code to be executed when the route is matched to an incoming request. Create this script at $APP_ROOT/public/index.php with the following content:

<?php

use \Psr\Http\Message\ServerRequestInterface as Request;
use \Psr\Http\Message\ResponseInterface as Response;

// autoload files
require '../vendor/autoload.php';
require '../config.php';

// configure Slim application instance
// initialize application
$app = new \Slim\App($config);

// initialize dependency injection container
$container = $app->getContainer();

// add view renderer to DI container
$container['view'] = function ($container) {
  return new \Slim\Views\PhpRenderer("../views/");
};

// welcome page controller
$app->get('/', function (Request $request, Response $response) {
  return $response->withHeader('Location', $this->router->pathFor('home'));
});

$app->get('/home', function (Request $request, Response $response) {
  $response = $this->view->render($response, 'home.phtml', [
    'router' => $this->router
  ]);
  return $response;
})->setName('home');

$app->run();

Slim works by defining callback functions for HTTP methods and endpoints. This is done by defining the corresponding Slim method (get() for GET requests, post() for POST requests, and so on) and passing the route pattern to be matched as the first argument to the method. The second argument to the method is an anonymous function, which specifies the actions to be taken when the route is matched to an incoming request.

The script above sets up two handlers (with more to be added shortly). The first is a simple redirection, which redirects all requests for the / route to the /home route. The second is the /home route itself, which renders the content of the $APP_ROOT/views/home.phtml template. Create this template with the content below:

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Home</title>
    <link rel="stylesheet" 
      href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-beta.2/css/bootstrap.min.css">
    <link rel="stylesheet"
      href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-beta.2/css/bootstrap-theme.min.css">
    <!-- HTML5 shim and Respond.js for IE8 support of HTML5 elements and media queries -->
    <!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
    <!--[if lt IE 9]>
      <script src="https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js"></script>
      <script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
    <![endif]-->  

  </head>
  <body style="padding-top: 95px">

    <div class="container">
      <div class="row">
        <nav class="navbar navbar-expand-md navbar-dark 
          bg-dark fixed-top justify-content-between">
          <a class="navbar-brand" href="#">Home</a>
        </nav>
      </div>
    </div>
    
    <!-- content area -->
    <div class="container" style="text-align: center">
      <p>
        <a role="button" class="btn btn-success" href="">Add new project</a>
      </p>
      <p>
        <a role="button" class="btn btn-success" href="">View current projects</a>
      </p>
    </div>        
    <!-- content area ends-->
      
    <!-- footer -->
    <div class="container">
    </div> 
    <!-- footer ends -->

  </body>
</html>

This file contains a simple Bootstrap-based user interface with a navigation bar, footer, and content area. This basic template is used for all subsequent pages as well, with only the content area changing.

To see this in action, browse to the /home route (either http://localhost/public/home or http://myapp.localhost/home, depending on whether you are using a virtual host or not) and you should see the rendered version of the template above:

Home page
Home page
3

Initialize a ClearDB MySQL database instance

IBM Cloud offers a number of database-as-a-service options, one of which is the ClearDB MySQL database. As you might guess, this service provisions an empty MySQL database instance that can be bound to your application. The default plan offers only a limited quota of free storage.

  1. To see how this works, initialize a new ClearDB MySQL database service instance on IBM Cloud by logging in to your IBM Cloud account and selecting the "Data and Analytics" category from the navigation menu. Click the Create resource button and in the resulting list of services, select the ClearDB MySQL Database service and the CB5 free plan. Click Create to proceed. Database initialization
    Database initialization
  2. The ClearDB database service instance will now be initialized. From the service details page, open the ClearDB dashboard, select the new database instance and visit the System Information tab to view the credentials for the instance. Add these values to the application's $APP_DIR/config.php file so that the application can connect to the database instance. Database credentials
    Database credentials
  3. Use these credentials to connect to the remote MySQL database (using a tool such as the MySQL CLI or phpMyAdmin) and create tables to hold project and time entry information using the SQL below. The two tables are connected to each other via the entries.pid foreign key, which references the projects.id field.
    CREATE TABLE `projects` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB;
    
    CREATE TABLE `entries` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `pid` int(11) NOT NULL,
      `hours` float NOT NULL,
      `comment` varchar(255) NOT NULL,
      `date` date NOT NULL,
      PRIMARY KEY (`id`),
      KEY `pid` (`pid`),
      CONSTRAINT `entries_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `projects` (`id`)
    ) ENGINE=InnoDB;
4

Add projects

Now that you have all the infrastructure in place, you can begin adding real functionality to the application.

  1. Begin by creating a form to add new projects at $APP_ROOT/views/projects/save.phtml by using the base template shown in step 2 above and placing the code below in the content area:
    <?php if (!isset($_POST['submit'])): ?>
      <form method="post" 
        action="<?php echo $data['router']->pathFor('projects-save'); ?>">
        <div class="form-group">
          <label for="fname">Project name</label>
          <input type="text" class="form-control" id="name" name="name">
        </div>
        <div class="form-group">
          <button type="submit" name="submit" class="btn btn-success">Save</button>
        </div>
      </form>  
    <?php else: ?>
      <div class="alert alert-success" role="alert">
        <strong>Success!</strong> The project was successfully created or updated.
      </div>
    <?php endif; ?>

    This is a very simple form, consisting of a single input field for the project name. Next, add a route handler to render it, in the $APP_ROOT/public/index.php file:

    <?php
    use \Psr\Http\Message\ServerRequestInterface as Request;
    use \Psr\Http\Message\ResponseInterface as Response;
    
    // ...
    
    // project form renderer
    $app->get('/projects/save', function (Request $request, Response $response) {
      $response = $this->view->render($response, 'projects/save.phtml', [
        'router' => $this->router
      ]);
      return $response;
    })->setName('projects-save');
    
    // ...
    
    $app->run();
  2. When the application receives a request for the /projects/save URL, it renders the form code from the previous listing and produces a result like the one below: Adding a new project
    Adding a new project

    When a user enters data into the form and submits it, the application needs to validate the data. If the data is valid, the application creates a new record in the projects table. However, before it can do this, it needs a connection to the database.

  3. Add the lines of code below to initialize a new database connection for the application using PHP's MySQLi extension. Add the connection to the Slim dependency injection container so that it can be easily used by all the route handlers in the application:
    <?php
    use \Psr\Http\Message\ServerRequestInterface as Request;
    use \Psr\Http\Message\ResponseInterface as Response;
    
    // ...
    
    // configure MySQL client to DI container
    $container['db'] = function ($container) {
      $config = $container->get('settings');
      return new mysqli(
        $config['db']['hostname'], 
        $config['db']['username'], 
        $config['db']['password'], 
        $config['db']['name']
      );
    };
    
    // route handlers here
    // ...
    
    $app->run();

    Then, define a separate handler for POST requests, also in the $APP_ROOT/public/index.php file, as below:

    <?php
    use \Psr\Http\Message\ServerRequestInterface as Request;
    use \Psr\Http\Message\ResponseInterface as Response;
    
    // ...
    
    // project modification controller
    $app->post('/projects/save', function (Request $request, Response $response) {
      // get configuration
      $config = $this->get('settings');
      // get input values
      $params = $request->getParams();
      // check input
      if (!($name = filter_var($params['name'], FILTER_SANITIZE_STRING))) {
        throw new Exception('ERROR: Project name is not a valid string');
      }
      if (!$this->db->query("INSERT INTO projects (name) VALUES ('$name')")) {
        throw new Exception('Failed to save record: ' . $this->db->error);
      }
      $response = $this->view->render($response, 'projects/save.phtml', [
        'router' => $this->router
      ]);
      return $response;
    });
    
    // ...
    
    $app->run();

    This handler receives POST requests and uses the getParams() method to extract input parameters from the request. The handler sanitizes the value provided by the user to ensure that it doesn't contain any dangerous characters, and then formulates and executes an INSERT query to create a new record in the project's MySQL database. You will notice that it uses the database connection initialized earlier ($this->db) from the Slim dependency injection container to perform the query.

  4. Once the query has been successfully executed, the handler then renders the form template again; this time, since the $_POST['submit'] variable exists, the template renders a notification message instead of an empty form. Here's what you should see: Result of adding a new project
    Result of adding a new project
5

List and edit projects

Now you have a way to add new projects, but what about viewing and modifying them? It's easy to add a handler that simply lists available projects by executing a SELECT query on the database, as shown below:

<?php
use \Psr\Http\Message\ServerRequestInterface as Request;
use \Psr\Http\Message\ResponseInterface as Response;

// ...

// project list controller
$app->get('/projects[/]', function (Request $request, Response $response) {
  // query for all project records
  $projects = $this->db->query("SELECT * FROM projects");
  $response = $this->view->render($response, 'projects/list.phtml', [
    'router' => $this->router, 'projects' => $projects
  ]);
  return $response;
})->setName('projects-list');

// ...

$app->run();

The list of projects retrieved by the query is passed to a template, which iterates over the list and displays each project in a table. Here's the code for the template, at $APP_ROOT/views/projects/list.phtml:

<?php if (count($data['projects'])): ?>
<table class="table table-border table-responsive-md">
  <thead>
    <tr>
      <th>#</th>
      <th>Project</th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <?php foreach ($data['projects'] as $project): ?>
  <tr>
    <td><?php echo htmlspecialchars($project['id'], ENT_COMPAT, 'UTF-8'); ?></td>
    <td><?php echo htmlspecialchars($project['name'], ENT_COMPAT, 'UTF-8'); ?></td>
    <td><a role="button" class="btn btn-primary" 
      href="<?php echo $data['router']->pathFor('projects-save', 
      array('id' => htmlspecialchars($project['id'], ENT_COMPAT, 'UTF-8'))); ?>">
      Modify project</a></td>
    <td><a role="button" class="btn btn-danger" 
      href="<?php echo $data['router']->pathFor('projects-delete', 
      array('id' => htmlspecialchars($project['id'], ENT_COMPAT, 'UTF-8'))); ?>">
      Delete project</a></td>
  </tr>
  <?php endforeach; ?>
</table>
<?php endif; ?>

And here's what the output looks like:

Project list
Project list

Notice from the template code that each project includes links to edit and delete the corresponding record, and the project ID is included in the link as a URL parameter. Requests for these operations are handled by the /projects/save and /projects/delete handlers, respectively.

Editing a project involves displaying the same form as that used for creating a new project, except that the values are pre-filled. On submission, the input entered by the user is validated as before. This time, however, instead of an INSERT query, an UPDATE query is performed to update the project record in the database.

These tasks can be accomplished by modifying the /projects/save handlers seen in step 4 to include some additional business logic:

<?php
use \Psr\Http\Message\ServerRequestInterface as Request;
use \Psr\Http\Message\ResponseInterface as Response;

// ...

// project modification form renderer
$app->get('/projects/save[/{id}]', function (Request $request, Response $response, $args) {
  $project = null;
  if (isset($args['id'])) {
    $id = filter_var($args['id'], FILTER_SANITIZE_NUMBER_INT);
    $projectResult = $this->db->query("SELECT * FROM projects WHERE id = '$id'");
    $project = $projectResult->fetch_object();    
  }
  $response = $this->view->render($response, 'projects/save.phtml', [
    'router' => $this->router, 'project' => $project
  ]);
  return $response;
})->setName('projects-save');

// project modification controller
$app->post('/projects/save[/{id}]', function (Request $request, Response $response, $args) {
  // get configuration
  $config = $this->get('settings');
  // get input values
  $params = $request->getParams();
  // check input
  if (!($name = filter_var($params['name'], FILTER_SANITIZE_STRING))) {
    throw new Exception('ERROR: Project name is not a valid string');
  }
  // if project id included
  // check input and update record
  // if not, create new record
  if (!empty($params['id'])) {
    $id = filter_var($params['id'], FILTER_SANITIZE_NUMBER_INT);
    if (!(filter_var($id, FILTER_VALIDATE_INT))) {
      throw new Exception('ERROR: Project is not valid');
    }
    if (!$this->db->query("UPDATE projects SET name='$name' WHERE id='$id'")) {
      throw new Exception('Failed to save record: ' . $this->db->error);
    }
  } else {
    if (!$this->db->query("INSERT INTO projects (name) VALUES ('$name')")) {
      throw new Exception('Failed to save record: ' . $this->db->error);
    }
  }
  $response = $this->view->render($response, 'projects/save.phtml', [
    'router' => $this->router
  ]);
  return $response;
});

// ...

$app->run();

These revised handlers now accept an optional project ID as part of the request URL. If the project is found, the handlers search the database for the corresponding record to pre-fill the project creation form. On submission, if the project ID is included in the input parameters, the handler executes an UPDATE query with the revised project name instead of the usual INSERT query.

These changes to the handlers also require corresponding changes in the form template, at $APP_ROOT/views/projects/save.phtml. Most notably the changes include adding a hidden field for the project ID and pre-filling the input field with the current project name, if available. For security, output escaping is performed with htmlspecialchars() on the data being interpolated into the template. Here's the revised form template:

<?php if (!isset($_POST['submit'])): ?>
  <form method="post" action="<?php echo $data['router']->pathFor('projects-save'); ?>">
    <input name="id" type="hidden" 
      value="<?php echo (isset($data['project']->id)) ? 
      htmlspecialchars($data['project']->id, ENT_COMPAT, 'UTF-8') : ''; ?>" />
    <div class="form-group">
      <label for="fname">Project name</label>
      <input type="text" class="form-control" id="name" name="name" 
        value="<?php echo (isset($data['project']->name)) ? 
        htmlspecialchars($data['project']->name, ENT_COMPAT, 'UTF-8') : ''; ?>">
    </div>
    <div class="form-group">
      <button type="submit" name="submit" class="btn btn-success">Save</button>
    </div>
  </form>  
<?php else: ?>
  <div class="alert alert-success" role="alert">
    <strong>Success!</strong> The project was successfully created or updated.
  </div>
<?php endif; ?>

Note: The final screenshot above also displays links to delete projects. For brevity, the code for the project deletion handler is not included in this tutorial, but it can be found in the source code repository for the application. A similar approach will be used for deletion of time entries, therefore record deletion will be explained in detail in Part 2 of this series.

Conclusion

The goal of this two-part series is to build a useful database-backed application with PHP and MySQL and leverage the security, scalability, and flexibility of IBM Cloud to deploy and host it. Part 1 has covered the basics, showing you how to initialize a cloud-based database instance and connect your local PHP application to it. It has also walked you through the basics of adding and modifying records on the cloud database instance using the Slim framework.

Part 2 will build on these topics by showing you how to link time entries with projects and generate summary reports. It will also show you how to transfer your final PHP application from your local development environment to IBM Cloud, so that both application and database are running in the cloud. Make sure you come back for that!


Downloadable resources


Related topics


Comments

Sign in or register to add and subscribe to comments.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Cloud computing
ArticleID=1060035
ArticleTitle=Track time spent on projects with IBM Cloud, Part 1: Build a custom web application with IBM Cloud, PHP, and MySQL
publish-date=04162018