Project Management: The Application

The main project management application that we will examine deals with maintaining user state (tracking users) and all major events that make the application useful, such as adding, removing, and editing projects. It will also enable users to assign tasks and add or remove project staff, as well as maintain files for a particular project.

The Database Tables

The application relies on four database tables and their fields:


projects


 

  • pid – sets the project ID

  • title – name of the project

  • project_description – describes the project in some detail

  • status – status of a project; can be ‘completed,’ ‘pending,’ or ‘overdue’

  • create_dt – date the project was created

  • due_dt – date by which the project must be completed

  • u_id – ID of the user that created the project


tasks



 

  • tid - task id number

  • task_description – describes the task

  • complete_by – date field that takes the date on which the task should be completed

  • p_id - the ID of the project that requires the task


files




  • fid - file ID

  • p_id – ID of the project to which the file belongs

  • filename – name of the file

 staff

 


 


  • sid - staff member ID

  • name – name of staff member

  • p_id – ID of the project to which the staff member belongs

{mospagebreak title=The SQL}

Below is the SQL for each of the tables with sample data included. Just copy and paste it into your MySQL admin client application:

– Table structure for table `files`


CREATE TABLE `files` (

`fid` int(4) NOT NULL auto_increment,

`filename` varchar(100) NOT NULL default ”,

`p_id` int(4) NOT NULL default ’0′,

PRIMARY KEY (`fid`)

) TYPE=MyISAM AUTO_INCREMENT=7 ;


– Dumping data for table `files`


INSERT INTO `files` VALUES (1, ‘projectX.png’, 1);

INSERT INTO `files` VALUES (2, ‘projectY.png’, 2);

INSERT INTO `files` VALUES (3, ‘anotherfile.png’, 1);

INSERT INTO `files` VALUES (4, ‘file.png’, 2);

INSERT INTO `files` VALUES (5, ‘maintop-satin.jpg’, 3);

INSERT INTO `files` VALUES (6, ‘Blue hills.jpg’, 3);


– ——————————————————–


– Table structure for table `projects`


CREATE TABLE `projects` (

`pid` int(4) NOT NULL auto_increment,

`title` varchar(100) NOT NULL default ”,

`project_description` text NOT NULL,

`status` enum(‘overdue’,'completed’,'pending’) NOT NULL default ‘overdue’,

`due_dt` date NOT NULL default ’0000-00-00′,

`create_dt` date NOT NULL default ’0000-00-00′,

`u_id` int(4) NOT NULL default ’0′,

PRIMARY KEY (`pid`)

) TYPE=MyISAM AUTO_INCREMENT=4 ;


– Dumping data for table `projects`


INSERT INTO `projects` VALUES (1, ‘Project X’, ‘Secret project6′, ‘overdue’, ’2007-03-22′, ’2007-02-16′, 1);

INSERT INTO `projects` VALUES (2, ‘Project Y’, ‘Secret project Y’, ‘completed’, ’2007-02-18′, ’2007-02-18′, 2);

INSERT INTO `projects` VALUES (3, ‘New1′, ‘gufgjkk’, ‘pending’, ’2007-05-25′, ’2007-02-19′, 1);


– ——————————————————–


– Table structure for table `staff`


CREATE TABLE `staff` (

`sid` int(4) NOT NULL auto_increment,

`p_id` int(4) NOT NULL default ’0′,

`name` varchar(100) NOT NULL default ”,

PRIMARY KEY (`sid`)

) TYPE=MyISAM AUTO_INCREMENT=6 ;


– Dumping data for table `staff`


INSERT INTO `staff` VALUES (1, 1, ‘Jack Dee’);

INSERT INTO `staff` VALUES (2, 2, ‘maria.garises’);

INSERT INTO `staff` VALUES (3, 1, ‘nico.brand’);

INSERT INTO `staff` VALUES (4, 2, ‘john.doe’);

INSERT INTO `staff` VALUES (5, 3, ‘john dean’);


– ——————————————————–


– Table structure for table `tasks`


CREATE TABLE `tasks` (

`tid` int(4) NOT NULL auto_increment,

`p_id` int(4) NOT NULL default ’0′,

`task_description` text NOT NULL,

`complete_by` date NOT NULL default ’0000-00-00′,

PRIMARY KEY (`tid`)

) TYPE=MyISAM AUTO_INCREMENT=3 ;


– Dumping data for table `tasks`


INSERT INTO `tasks` VALUES (1, 1, ‘some secret stuff, hush hush’, ’0000-00-00′);

INSERT INTO `tasks` VALUES (2, 2, ‘hush, hush ‘, ’0000-00-00′);


{mospagebreak title=The main.php script}

The first script we are going to look at is the main.php script. The script is, in a sense, the point of access for the entire application. It is the page to which the login script sends a user that has been granted access. It lists all the projects in the database that are connected to the logged-in user’s name.

The script basically checks to see if the user that is logged in has the ’admin’ or ‘normal’ level of access. If the user is an admin, then all the projects in the database are listed; otherwise, only the projects registered in the name of the logged-in user are listed. Below is the code for the entire script:


<?php

include "dbcon.php";

include "functions.php";

//initialize variables


// retrieve information based on the user id, that we set in the login page:


if(isset($_SESSION['uid'])){

//here you could check if the session var is indeed numeric, just as a extra security precaution

$uid=mysql_escape_string($_SESSION['uid']);

//echo $uid;

$level = $_SESSION['level'];


//if the access level is admin, then you need to retrieve all the projects in the database

if($level == "admin"){

$getprojects = "SELECT * from projects ORDER by pid";

$results=mysql_query($getprojects);

if(!$results){

echo mysql_error();

}else{

$num_admin = mysql_num_rows($results);

}


}else{//level does not contain admin


//otherwise extract only the projects belonging to the currently logged in user

$getprojects = "SELECT * FROM projects WHERE u_id = ‘".$uid."’";

$result=mysql_query($getprojects);

if(!$result){

echo mysql_error();

}else{

$num_normal = mysql_num_rows($result);


}

}




}else{

//user did not login and should not be on this page

//redirect to login page

header("location:login.php");

}//end session check

?>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml"><!– InstanceBegin template="/Templates/PM_Main.dwt.php" codeOutsideHTMLIsLocked="false" –>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />

<!– InstanceBeginEditable name="doctitle" –>

<title>Untitled Document</title>

<!– InstanceEndEditable –>

<!– InstanceBeginEditable name="head" –><!– InstanceEndEditable –>

<link href="Templates/main.css" rel="stylesheet" type="text/css" />

</head>


<body>

<table width="100%" border="0">

<tr>

<td width="33%">&nbsp;</td>

<td width="28%">&nbsp;</td>

<td width="39%">Logged in: <!– InstanceBeginEditable name="login" –><? echo $_SESSION['name'];?> | <a href="logout.php">Logout</a><!– InstanceEndEditable –></td>

</tr>

<tr>

<td colspan="3" bgcolor="#6699CC" class="headertxt">Project Management Software </td>

</tr>

<tr>

<td colspan="3"><!– InstanceBeginEditable name="main" –>

<table width="100%" border="0">

<tr>

<td width="37%"><strong>Project Name </strong></td>

<td width="34%"><strong>Status</strong></td>

<td width="29%"><strong>Date Created</strong> </td>

</tr>

 

<?php if($level =="admin"){?>

 

<?php

if($num_admin > 0){

while($rowadmin = mysql_fetch_assoc($results)){

?>

<tr>

<td><a href="view_project.php?pid=<?php echo $rowadmin['pid']?>"><?php echo $rowadmin['title'];?></a></td>

<td><?php echo $rowadmin['status'];?></td>

<td><?php echo $rowadmin['create_dt'];?>

</td>

</tr>

<?php

}

}else{ ?>

<tr>

<td colspan="3"><p>There does not seem to be any projects registered in your name. Click on the "Create New Project" link to create a project.</p></td>

</tr>

<?php

}?>

 

<?php }else{?>

  

<?php

if($num_normal > 0){

while($rownormal = mysql_fetch_assoc($result)){

?>

<tr>

<td><a href="view_project.php?pid=<?php echo $rownormal['pid']?>"><?php echo $rownormal['title'];?></a></td>

<td><?php echo $rownormal['status'];?></td>

<td><?php echo $rownormal['create_dt'];?>

</td>

</tr>

<?php

}

}else{ ?>

<tr>

<td colspan="3"><p>There does not seem to be any projects registered in your name. Click on the "Create New Project" link to create a project.</p></td>

</tr>

<?php

}?>

 

<?php } ?>

</table>

<!– InstanceEndEditable –></td>

</tr>

<tr>

<td colspan="3"><!– InstanceBeginEditable name="nav" –><table width="100%" border="0">

<tr>

<td><a href="add_project.php">Create New Project</a> | <a href="admin/login.php">Administrators Corner </a></td>

</tr>

</table>

<!– InstanceEndEditable –></td>

</tr>

<tr>

<td align="right" class="cright" colspan="3">copyright &copy; 2007 PM </td>

</tr>

</table>

</body>

<!– InstanceEnd –></html>


{mospagebreak title=Code Explained}

The script itself is not so complicated; it merely retrieves the project’s name, status, and date of creation. So let’s take a closer look. The very first lines of the php portion of the script should be familiar to you by now; they include the database connection file and the functions file. The script also initializes variables, if needed. I initialize the variables because in some versions of PHP, you get an error message saying ‘variable not defined.’ Initializing variables avoids this problem. 

<?php

include "dbcon.php";

include "functions.php";

//initialize variables


The next bit of code is at the heart of the main script. First of all, it checks to see whether the user ID session variable is set. If it is set, then we know the user is properly logged in and should be on this page:


// retrieve information based on the user id, that we set in the login page:

if(isset($_SESSION['uid'])){


Since our intention is to retrieve the list of projects belonging to this user, we have to escape the variables that we are going to use in the query concerned. At the same time, we also give the level session variable a shorter name. This will make it easy for us to check the value of the $level variable for comparison later on:

//here you could check if the session var is indeed numeric, just as a extra security precaution

$uid=mysql_escape_string($_SESSION['uid']);

//echo $uid;

$level = $_SESSION['level'];

Now we check whether the value contained in the $level variable is ‘admin’ or ‘normal’ and based on the outcome, we run specific queries. If the value turns out to be ‘admin,’ then we need to retrieve ALL projects in the database. At this point, you can also set conditions on the query by retrieving only those projects that are not overdue or only those projects that are pending. It is entirely up to you and your situation as to what you want the administrator to see at this point:


//if the access level is admin, then you need to retrieve all the projects in the database

if($level == "admin"){

$getprojects = "SELECT * from projects ORDER by pid";

$results=mysql_query($getprojects);

if(!$results){

echo mysql_error();

}else{

$num_admin = mysql_num_rows($results);

}


The result of the query is stored in the $num_admin variable. It will hold the number of projects that are returned by the query. The value in that variable ($num_admin) is of type integer. If the value in the $level variable contains ‘normal,’ then we run a query to retrieve only projects that are registered in the logged-in user’s name:


}else{//level does not contain admin


//otherwise extract only the projects belonging to the currently logged in user

$getprojects = "SELECT * FROM projects WHERE u_id = ‘".$uid."’";

$result=mysql_query($getprojects);

if(!$result){

echo mysql_error();

}else{

$num_normal = mysql_num_rows($result);


}

}


The first line of the query specifies that only projects that have the logged-in user’s user id should be retrieved:


$getprojects = "SELECT * FROM projects WHERE u_id = ‘". $uid ."’";

If the user id session variable is not set, then the user should not be on this page, since this means that he or she was not logged in. We redirect the user to the login page:


}else{

//user did not login and should not be on this page

//redirect to login page

header("location:login.php");

}//end session check


Conclusion

In the next article, we will look at the HTML portion of the main.php application. The HTML part will consist of a dynamic table that will be mixed in with PHP variables.

Google+ Comments

Google+ Comments