by Richard

How to build a Project Management Application in PHP & MySQL from scratch

Trying to find a project management application is a daunting task: you want a system with powerful features and you must get a buy-in from your colleagues. Most of the time, you wind up with a bloated system filled with features you’ll never use.

The good news is that it turns out it is not that difficult to build one from scratch yourself. After reading this tutorial, you should have a clean, well-designed project management application up and running in less than an hour.

What IS a Project Management Application?

A project management application is a software system used for project planning, resource allocation, tracking of project components, and change management.

In this tutorial, we are going to build a simple project management system in PHP which employees and managers can use for collaboration and communication between project stakeholders.

What is IN a Project Management Application?

We will be creating a simple and easily customizable PM system to tracks projects, including milestones, tasks, hours, costs, and more. Since every project is unique, this tutorial merely means to build an application foundation; you should be able to easily extend it based on your requirements.

In our project management application, employees will be able to:

  • View their tasks
  • Log in hours worked

Managers will be able to:

  • Manage projects
  • Manage milestones
  • Manage tasks
  • Manage costs and hours
  • Manage employee resources

The Building Blocks of a Project Management System

Building blocks of a typical project management system

Here are the primary functions of a project management system:

  • Project planning: To define a project schedule, a project manager may use the software to map project tasks and visually describe task interactions.
  • Task management: Allows the project manager to create and assign tasks, establish deadlines, and produce status reports.
  • Resource management: Defines responsibilities — who is supposed to do what.
  • Budgeting and cost tracking: A good project management application facilitates budget reporting as well as viewing, notifying, and updating costs for stakeholders.
  • Time tracking: The software must have the ability to track time spent on all tasks and maintain records for third-party consultants.

System Requirements

  • PHP 5.3+
  • MySQL or MariaDB
  • phpGrid

Creating a Project Management Database

We will start by creating our project management database. The main tables we will use are:

  • Clients — customer company data
  • Contacts — client contact data. A client can have one or more contacts.
  • Projects — project information
  • Milestones — project milestone
  • Tasks — project tasks
  • Hours — time spent on each task
  • Costs — cost of a task
  • Users — user data; one can be either an employee or a manager

Other tables (lookup tables):

  • ms_status
  • proj_status
  • task_status

Complete Database Schema Diagram

A database schema is the structure that represents the logical view of the entire database: tables, views, and primary and foreign keys. A database schema includes all entities and the relationships between them.

Below is the database diagram of our simple project management application. The key symbol in each table represents the table’s primary key while the magnifying glass indicates a foreign key linking it to another table (lookup table) in the database.

simple_pm_install.sql

Once you have an understanding of the database’s table structure, obtain the simple_pm_install.sql sql script from this tutorial’s GitHub repo, and then execute the sql script using a MySQL tool such as MySQL Workbench or Sequel Pro. This will create a new database named simple_pm and tables we need in this tutorial.

Setup phpGrid

Our simple project management contains many DataGrids. The DataGrid is a spreadsheet-like data table which displays rows and columns representing records and fields from the database table. The DataGrid provides the end-user with the ability to read and write to the database tables on a web page.

To create the DataGrid, we use a dataGrid tool from phpGrid . The reason why we use a tool instead of building our grids from scratch is that developing a DataGrid in php is usually extremely tedious and prone to errors. The phpGrid DataGrid library will handle all internal database CRUD (Create, Remove, Update, and Delete) operations for us offering faster and better results with minimal coding.

To install phpGrid, follow these steps:

  1. Unzip the phpGrid download file.
  2. Upload the phpGrid folder to the phpGrid folder.
  3. Complete the installation by configuring the conf.php file.

Before we begin coding, we must include the following information in `conf.php` the phpGrid configuration file. Here is an example of the database connection settings:

define(‘PHPGRID_DB_HOSTNAME’, ‘localhost’); define(‘PHPGRID_DB_USERNAME’, ‘root’); define(‘PHPGRID_DB_PASSWORD’, ‘’); define(‘PHPGRID_DB_NAME’, ‘custom_pm’); define(‘PHPGRID_DB_TYPE’, ‘mysql’);
  • PHPGRID_DB_HOSTNAME — web server IP or host name
  • PHPGRID_DB_USERNAME — database user name
  • PHPGRID_DB_PASSWORD — database password
  • PHPGRID_DB_NAME — database name
  • PHPGRID_DB_TYPE — type of database
  • PHPGRID_DB_CHARSET — always ‘utf8’ in MySQL

Page Template

Our page will be comprised of a header, menu, body, and footer. Instead of creating the same page elements repeatedly, we will start by creating a reusable page template.

head.php

This is a basic HTML5 template header; it includes a link to a custom stylesheet that will be created in a later step.

Notice the usage of $_GET['currentPage']. Each page will set a value that will highlight the name of the current page on the top menu bar.

Include the following code in style.css for menu styling; it will transform the above unordered list into a menu.

Simply includes the closing tags for the elements we opened in the header:

Our Complete Reusable Page Template

The main content will go after the section title.

Project Management Main Pages

Our project management application for managers has four pages:

  • Clients
  • Client Details
  • Projects
  • Project Details

The Clients page displays a list of clients with links to individual client details (Client Details page).

The Projects page displays a list of projects being managed with links to project details (Project Details page).

Design Mockup

Here is our project management application design mockup for project managers who manage one or more projects and assign tasks to employees.

Clients

When a manager logs in to the project management system, the first page he sees is the Clients page which contains a complete list of companies.

The following code will give us a list of clients.

  • The first line creates a phpGrid object by passing the SELECT SQL statement with its primary key id followed by the name of the database table – clients.
  • The second line creates a dynamic URL from the primary key “id”; it uses a function called set_col_dynalink(). This function sets a specific column-to-display HTML hyperlink URL based on dynamic values. If the primary key “id” has the value 100, it will display a URL like this client-details.php?id=100 which drills down the client detail page.
  • The third line, enable_edit(), makes the DataGrid editable and all CRUD (Create, Read, Update, Delete) operations are now accessible.
  • The last line calls the display() function to render the DataGrid on the screen.

You can find out more demos below using those functions:

Display Dynamic URL | phpGrid
From example above, we learn that phpGrid can display simple, static URL using set_col_link() method. However, it is…phpgrid.comCRUD PHP Datagrid (Editable Datagrid) * | phpGrid
The PHP datagrid is not editable by default. You can enable edit by simply calling enable_edit(). Whala!phpgrid.com

Client Details

From the Clients page, the client name has a hyperlink that redirects to the Client Details page for that client when clicked.

From the Client Details page, we need to obtain the Client ID that is passed as the URL parameter.

In our application, the Client ID should always be an integer. Thus, we use the PHP intval() function to ensure the Client ID is returned as an integer.

The following code displays projects associated with the current $clientId using the filter function set_query_filter(). In addition, we make the DataGrid editable with the enable_edit() function; phpGrid will take care of any CRUD operations for us.

Client Details > Projects

As you may notice, we again use the same function, set_col_dynalink(), to create hyperlinks to the Project Details table using the Project ID. We will get into the project-details.php page next.

<h4>Projects</h4>

Client Details > Contacts

Under the Projects DataGrid, a list of contacts associated with the $clientid is displayed using the same functions set_query_filter() and enable_edit().

Contacts

Projects

Now, let’s build the Projects page.

The Projects page displays a list of managed projects. It is very similar to the Clients page except that the database table is “Projects,” and the hyperlinks have the URL project-details.php instead of client-details.php.

Project Details

From the Projects page, each project name has a hyperlink which redirects to each individual Project Details page when clicked.

And from the Project Details page, we retrieve the Project ID for the URL parameter.

Look familiar? Because it is! It should because both Projects and Project Details pretty much follow the same coding pattern used in the Clients and Client Details pages; there are not really that many surprises.

The Project Details page is made of the following grids, all filtered by the $projectId obtained from the URL parameter.

  • Milestones
  • Tasks
  • Hours
  • Costs

Milestones

A milestone marks a major event in a project timeline. Here, we can easily display all the milestones of a project by filtering the value $projectId. Project managers have the necessary access rights to modify the milestones.

Likewise, we can easily filter and display a list of tasks for the current project.

I think you are probably getting the hang of it now. Here’s the code for the two remaining datagrids.

Employees Page

We can now move on to the final part of the tutorial, the Employees page. Employees can login to view active project tasks assigned to them, track task hours, and costs. Their responsibility is simple: to monitor the tasks and log hours worked on any specific project task.

Design Mockup

The Employees page has only one menu item: Tasks.

My Active Tasks

The first part of the page shows a list of active tasks assigned to the current employee. Each task will have the hours reported by the current employee; this is a perfect situation in which to use a phpGrid subgrid.

We also need to use set_query_filter() to display only active tasks that have Status value "2", and only for the current employee.

For demo purposes, we hard-coded the Employee ID to 2. In a real-world application, the Employee ID value should be stored and retrieved using PHP SESSION.

We then create the active Tasks DataGrid for the current employee. Notice the filter function.

Once we’ve defined the grid for the active tasks, we create a DataGrid to log the hours reported by the current employee.

Lastly, set_subgrid() causes the Hours DataGrid to become a subgrid of the Tasks DataGrid. The linking field in the Hours subgrid is ‘TaskID’, which is the second parameter, and in the main grid Tasks, it is “id,” the third parameter.

My Hours History

Lastly, we would like to display a read-only DataGrid using data from the Hours table for the current employee for reviewing purposes.

Notice we used a function called set_jq_gridName(). You can find more documentation here. This function sets a unique object name for the DataGrid. By default, phpGrid uses the database table name as its internal object name. Since we already created a DataGrid from the Hours table in the last part, we must set a unique name for our second Hours DataGrid.

Application Screenshots

Managers

Employee

Live Demo

Login as manager | Login as employee

Download Source Code

phpcontrols/phpgrid-project-management
phpgrid-project-management — phpGrid Complete Project Management Demo Applicationgithub.com

Thanks for reading. If you enjoyed this article, please hit that clap button ? to help others find it and follow me on Twitter.