Cracking The Vault (part 1)

Save the paperless office from extinction with this electronic document management system.

Setting The Stage

Now, I don’t know about you guys, but I don’t think the paperless office is a feasible idea. At least not without a lot more thinking, and a lot more work.

I’m not being a pessimist here. I love the concept, and, just like everyone else, my eyes light up at the thought of replacing the mounds of paper piled up around my workstation with something a little more attractive (a Mr. Potato Head, maybe?). And so, a while back, a few like-minded colleagues and myself got together and decided to try and make a go of using only electronic documents in our daily workday.

Obviously, we already use internal email extensively, and swap documents over the network - however, since we are a content production company, our work involves accepting and editing both digital and printed material, and most people still prefer printing out an article or report, as opposed to reading the digital version off a computer screen.

After a very trying eight weeks, a couple of problems with this approach became immediately visible in our weekly after-hours gripe session. And so, a skunkworks project was born, a software development effort designed to make it easier to manage collections of electronic documents in a networked environment.

All this is, of course, by way of background. Over the next few pages, I will be describing our unique requirements and the problems we faced in greater detail, together with our proposed solution. And, after putting in some thought, I will be guiding you through the process of building a document management system, with the help of powerful open-source tools like PHP and mySQL.

The goal here is two-fold: to introduce novice and intermediate programmers to the process of designing and implementing a Web-based application, and to PHP’s session handling, file upload and database capabilities; and to offer developers, administrators, corporate efficiency experts and people with messy desks a possible solution to their woes.

Lofty goals, you scoff? Well, let’s see…

Just Another Day At The Office

Before we get into the nitty-gritty of PHP scripting, it is important to understand the problems we faced, so that the functional requirements of the solution become clear.

The primary problem we faced with electronic documents was one of version control. Here are three scenarios which illustrate the problem:

  1. The company has just been contracted for a software development project, and John is identified as the team lead. He is responsible for publishing a schedule describing milestones and resource allocation. Before committing delivery dates to the customer, John must communicate with various department heads and obtain their agreement to deliver work product on specific dates.

Typically, John would create a schedule, outlining the tasks as he sees them (leaving dates and resource names empty). He would then send this schedule to different department heads, asking each to fill in the missing data in the areas that concern them. For example, Tim (Design Manager) would need to assign a resource to the interface design aspects of the project and state how long the task would take, while Roberta (Publications) would need to assign a writer to the task of putting together a manual once the code is released.

The problem here is that if John emails the document to each person, he has to integrate the different versions he receives back into a single picture, and again send the final draft out for approval to all design heads. Alternatively, he could place it on the network - but how is he to know when the various departments have finished adding to it, and the changes made to the document by each?

  1. A corollary of the problem above - once the schedule is decided, John stores the document in a project folder on the network, accessible to all. However, as per Murphy’s Law, the project is plagued with delays, necessitating frequent updates of the schedule…and consequently, different versions of the same schedule. Team members often find themselves working off an outdated version of the schedule (“Oh, didn’t you know, we need the widget tomorrow - look, it says so right here, in this new schedule!”).

John also needs to assign permissions to the schedule (and other group documents) carefully, to ensure that only authorized people are viewing or making changes to it (this is particularly important for billing and invoice information). If John is technically qualified, he can easily use built-in Netware, Linux or Windows NT capabilities to assign document permissions…but what if he is (horror of horrors!) a manager?!

  1. The third scenario is one unique to our particular area of business. As a content development company, we have a small but talented team of freelancers developing content for us. These guys (and gals) email material to us from home, sometimes dropping in to the office to perform edits or corrections. Now, if they email material to Harish, and drop in the next day expecting to edit it, they’re going to be deeply wounded by the fact that Harish decided to go to the beach today, and no one else can get through the BIOS password on his workstation.

A similar problems arises on collaborative writing projects; if two or more writers are working on different aspects of the same piece, an editor has to sit down at the end and make sure that each writer’s changes and additions are integrated into a final, workable article. And here’s another twist: what happens if a new writer is brought in to work on the piece (these guys like to call themselves “consultants”), and the work is spread piecemeal among the other three members of the team? Obviously, there needs to be a way for new team members to quickly obtain the most current version of a document, so as to get up to speed quicker…

You may have faced similar situations at your own workplace - if so, you’ll be glad you decided to read this article. If you’ve never, ever, encountered one of these situations or variants thereof - is your company hiring?

An Evil Plan Is Born

Having understood the problems, it becomes easier to decide on the requirements of the solution. An analysis of the problems above reveals that most of them would be resolved if we had a system which:

  1. offered a central storage area for group documents;

  2. used some manner of secure access to ensure that only users with appropriate permissions viewed or edited sensitive information;

  3. made it possible to identify which users were working on which documents at any point in time;

  4. tracked the changes made to each document;

  5. offered a quick and easy way to locate documents in a large repository, and categorize them according to type;

  6. provided a framework for multiple users to collaborate on a document, without imposing any special software requirements or constraints on them.

I initially debated restricting the system to text documents only; however, since we use a variety of different applications for internal communication (spreadsheets, documents, project schedules, schematics), I decided to also allow users to upload binary files of any type (administrators should, of course, have the option to restrict certain file types if necessary.)

This, therefore, constitutes the initial feature set for our application. If I am able to meet these basic requirements, it will make a lot of people very happy, and more features will be requested. Once I have enough requests for more features, I plan to go to the guy in the corner office and ask him to make me a manager, double my salary, give me my own office and assign me to the project full-time. Ahhhh haa haa ha ha!

Setting The Ground Rules

At this point, I have also come up with a few ideas as to how this system is likely to work. I plan to model it closely on the check-in/check-out adopted by many popular source-control system, most notably CVS [http://www.cvshome.org/]. Incidentally, we did consider using CVS - the developers are already quite comfortable with it - but the managers had a “problem” with the command-line interface.

Here is an initial draft of the basic rules that I plan to build into this application:

  1. Any user can log in to the system and add a file to the repository. This user can also assign permissions to allow other users to view (download) copies of the file, and modify (upload) new versions of the file. Users can only view/modify files if they have appropriate permissions.

  2. Users with “view” permissions can download the most recent copy of a file for viewing on their local systems.

  3. Users with “modify” permissions can “check out” a file from the storage area, make changes to it, and then “check in” the new version of the file. Other users cannot access a file while it is checked out.

  4. A revision tracking system will enable users to enter comments at the time of checking in a file; these comments serve as both a reminder of the changes made to the document, and a notice of which users have made the changes. However, unlike most source-control systems, I do not currently plan to archive previous copies of a document (because it is not a requirement at present - though this can easily be added), or provide a method to “roll back” changes (because I have no idea how to track and revert changes made to binary files like JPEG images or Microsoft Word DOCuments).

  5. All this activity will take place via a Web browser, thus freeing the user from having to install any special software on their workstation.

And while we’re deciding on the features and rules, let’s also decide on a name. I’m going to call it The Vault, simply because it functions much like a bank vault (secure access; user identification and tracking; sounds hip).

Design Time

Now that the functional requirements have been decided, it’s time to find someone I don’t particularly like and shift the whole job over to him while I enjoy a long lunch and a comfortable nap…

Just kidding. The next step is actually to begin thinking about the database design, in accordance with the feature set above.

This is a good time for you to download the source code, so that you can refer to it throughout this article (you will need a Web server capable of running PHP and a mySQL database).

Download: vault.zip

After much thought and a few abortive experiments, I came up with the following database schema - five tables, one for each of the hours I won’t get to sleep tonight.

# --------------------------------------------------------
#
# Table structure for table 'user'
#

DROP TABLE IF EXISTS user;
CREATE TABLE user (
   id tinyint(4) unsigned NOT NULL auto_increment,
   username varchar(25) NOT NULL,
   password varchar(50) NOT NULL,
   PRIMARY KEY (id)
);

#
# id - user ID
# username - account username
# password - account password
#

This table contains a list of users allowed to enter The Vault - each user is assigned a unique ID, which will be used throughout the application. Here are some dummy entries.

#
# Dumping data for table 'user'
#

INSERT INTO user (id, username, password) VALUES ( '1', 'john', '2ca0ede551581d29');
INSERT INTO user (id, username, password) VALUES ( '2', 'joe', '7b57f28428847751');
INSERT INTO user (id, username, password) VALUES ( '3', 'tom', '675bd1463e544441');
INSERT INTO user (id, username, password) VALUES ( '4', 'bill', '656d52cb5d0c13cb');

Note that the passwords here are the same as the usernames - I’ve simply encrypted them with mySQL’s PASSWORD() function before inserting them into the database.

Next,

# --------------------------------------------------------
#
# Table structure for table 'category'
#

DROP TABLE IF EXISTS category;
CREATE TABLE category (
   id tinyint(4) unsigned NOT NULL auto_increment,
   name varchar(255) NOT NULL,
   PRIMARY KEY (id)
);

#
# id - category ID
# name - category name
#

I plan to allow users to categorize the documents stored in The Vault - this table contains a list of available categories. Here are some dummy entries.

#
# Dumping data for table 'category'
#

INSERT INTO category (id, name) VALUES ( '1', 'Billing');
INSERT INTO category (id, name) VALUES ( '2', 'Company Policies');
INSERT INTO category (id, name) VALUES ( '3', 'Administrivia');
INSERT INTO category (id, name) VALUES ( '4', 'Document Templates');
INSERT INTO category (id, name) VALUES ( '5', 'Process Flows');

It should be noted that both these tables would typically need to be controlled by an administrator, who would be in charge of adding new users and categories to the system.

# --------------------------------------------------------
#
# Table structure for table 'data'
#

DROP TABLE IF EXISTS data;
CREATE TABLE data (
   id tinyint(4) unsigned NOT NULL auto_increment,
   category tinyint(4) unsigned DEFAULT '0' NOT NULL,
   owner tinyint(4) unsigned DEFAULT '0' NOT NULL,
   realname varchar(255) NOT NULL,
   created datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
   description varchar(255),
   comment text,
   status tinyint(4) unsigned DEFAULT '0' NOT NULL,
   PRIMARY KEY (id)
);

#
# id - file ID
# category - category ID
# owner - owner's user ID
# realname - the original name of the file
# created - date on which file was first checked in
# description - one-line description of contents
# comment - author's note
# status - check in/out status; either 0 (available) or userID of user document is checked out to
#

This table contains information on the documents stored in the system. Each document is assigned a unique ID, and the table also stores information on the document category, the creation date, the document “owner” (the user who initially added the file), and a user-specified description and comment. The last field stores the current status of the document (checked in/out).

# --------------------------------------------------------

#
# Table structure for table 'perms'
#

DROP TABLE IF EXISTS perms;
CREATE TABLE perms (
   fid tinyint(4) DEFAULT '0' NOT NULL,
   uid tinyint(4) DEFAULT '0' NOT NULL,
   rights tinyint(4) DEFAULT '0' NOT NULL
);

#
# fid - file ID from "data" table
# uid - user ID from "user" table
# rights - rights user "uid" possesses for file "fid"; 1 = view, 2 = modify
#

This table stores permissions on a per-file basis; permissions may be defined as “view” (1) or “modify” (2). The unique combination of these three columns makes it possible to identify any user’s rights for any file in the system.

# --------------------------------------------------------
#
# Table structure for table 'log'
#

DROP TABLE IF EXISTS log;
CREATE TABLE log (
   id tinyint(4) unsigned DEFAULT '0' NOT NULL,
   modified_on datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
   modified_by tinyint(4) unsigned DEFAULT '0' NOT NULL,
   note text
);

#
# id - file ID
# modified_on - date of revision
# modified_by - ID of user modifying file
# note - description of modification
#

Finally, this table has been created specifically to store revision information when documents are checked back into the system. Don’t worry about this one for the moment, I’ll be discussing it in detail at a later stage.

At this point, I should say that the schema above constitutes an initial draft only. As the software evolves over time, I plan to modify this to add support for new features, and to optimize existing code.

Start Me Up

With the database design out of the way, it’s time to actually start creating the scripts. We’ll begin at the top, with the scripts which verify the user’s password. Here’s the initial login form, “start.html”.

<table border="0" cellspacing="5" cellpadding="5">
<form action="login.php" method="post">
<tr>
<td>Username</td>
<td><input type="Text" name="frmuser" size="15"></td>
</tr>
<tr>
<td>Password</td>
<td><input type="password" name="frmpass" size="15"></td>
</tr>
<tr>
<td colspan="2" align="center"><input type="Submit" name="submit" value="Enter"></td>
</tr>
</form>
</table>

Here’s what it looks like:

Once the form is submitted, the data is processed by “login.php”, which connects to the database to verify the username and password against the “user” table.

<?php
// includes
include("config.php");

// check login and password
// connect and execute query
$connection = mysql_connect($hostname, $user, $pass) or die ("Unable to connect!");
$query = "SELECT id, username, password from user WHERE username = '$frmuser' AND password = PASSWORD('$frmpass')";
$result = mysql_db_query($database, $query, $connection) or die ("Error in query: $query. " . mysql_error());

// if row exists - login/pass is correct
if (mysql_num_rows($result) == 1)
	{
	// initiate a session
	session_start();

	// register the user's ID
	session_register("SESSION_UID");
	list($id, $username, $password) = mysql_fetch_row($result);
	$SESSION_UID = $id;

	// redirect to main page
	header("Location:out.php");
	mysql_free_result ($result);

	// close connection
	mysql_close($connection);
	}
else
	// login/pass check failed
	{
	mysql_free_result ($result);
	mysql_close($connection);
	// redirect to error page
	header("Location: error.php?ec=0");
	exit;
	}
?>

Assuming the username and password is correct, the script initiates a session, and registers a session variable named $SESSION_UID, containing the user’s ID; this variable will remain available throughout the session, and will be used in many of the subsequent scripts. The script then redirects the browser to “out.php”, which forms the main interface to the system, via an HTTP header.

A login failure will redirect the browser to the generic error handler, “error.php”, with an error code indicating the type of error. I’ll be using this error handler extensively, to handle the different types of errors possible.

It is important to note that calls to header() and session_start() must take place before any output is sent to the browser. Even something as minor as whitespace or a carriage return outside the PHP tags can cause these calls to barf all over your script.

Finally, the include()d file, “config.php”, contains some useful variables - the database name, user name and password, together with the location of the data storage area and a list of allowed file types.

<?php

// database parameters
// alter this as per your configuration
$database="db35378";
$user = "mark347";
$pass = "h23590f2";
$hostname = "localhost";

// location of file repository
// this should ideally be outside the Web server root
// make sure the server has permissions to read/write files!
$dataDir = "/data/";

// list of allowed file types
$allowedFileTypes = array("image/gif", "text/html", "text/plain", "image/jpeg", "image/pjpeg", "image/png");
}
?>

Entry Points

Once the user is successfully logged in, “out.php” takes over and generates a list of documents available to the user; this list includes documents the user may have uploaded, as well as documents the user has “view” rights for. If this is the first time you are logging in, you will probably see nothing; once you add documents, the screen will fill up.

Before we look at this script, however, I want to draw your attention to the script “add.php”, accessible from the top right corner of the page and used to add new documents to the system. I’ll explain this first, since it offers a logical starting point.

The first thing “add.php” (and every other script) does is to verify the existence of a valid session - this is necessary to prevent unauthorized users from viewing the pages. If a session doesn’t exist, the browser is immediately redirected to the error handler.

<?php
// check to ensure valid session, else redirect
session_start();
if (!session_is_registered("SESSION_UID"))
{
header("Location:error.php?ec=1");
exit;
}

// includes
include("config.php");

?>

Assuming a session exists, a basic HTML page is built.

<html>
<head>
<basefont face="Verdana">
</head>

<body bgcolor="White">

<?php include("menu.inc");?>

<table width="100%" border="0" cellspacing="0" cellpadding="3">
<tr>
<td bgcolor="#0000A0">
<b><font face="Arial" color="White">Add New Document</font></b>
</td>
</tr>
</table>

<!-- lots of code -->

</body>
</html>

I’m not going to go into details here - it’s essentially a bunch of HTML tables, all dressed up to look pretty - but I will draw your attention to the file “menu.inc”, which contains the main menu for the page and is include()d on every page.

Seeding The System

Since “add.php” is a form, the script is divided into two sections; the first section displays the form, while the second section processes the form data.

if(!$submit)
{
// form has not been submitted yet -> display form
?>
	<table border="0" cellspacing="5" cellpadding="5">
	<form action="<?php echo $PHP_SELF; ?>" method="POST" enctype="multipart/form-data">

	<tr>
	<td><b>Location</b></td>
	<td colspan=3><input name="file" type="file"></td>
	</tr>

	<!-- snip! -->

	<tr>
	<td valign="top"><b><i>View</i> rights</b></td>
	<td><select name="view[]" multiple>
	<?php

	// query to get a list of available users
	$query = "SELECT id, username FROM user ORDER BY username";
	$result = mysql_db_query($database, $query, $connection) or die ("Error in query: $query. " . mysql_error());

		while(list($id, $username) = mysql_fetch_row($result))
		{
		$str = "<option value=\"$id\"";
		// pre-select logged-in user's name
		if ($id == $SESSION_UID) { $str .= " selected"; }
		$str .= ">$username</option>";
		echo $str;
		}
	?>
	</select></td>
	</tr>

	<!-- snip! -->

	</form>
	</table>
<?php
}
else
{
// form processing code
}
?>

A couple of points to be noted about this form. First, since I plan to use this to upload files, I’ve specified the form encoding type to be “multipart/form-data” and added a form field of type “file”. And a little further down, I’ve queried the database to generate a list of users so that rights can be assigned appropriately. Note how I’m checking each user’s ID against the current $SESSION_UID in order to pre-select the current user’s name.

Although I’m going to use the results of the SELECT query in two places - to generate a list for both “view” and “modify” rights - it isn’t necessary to run the query twice. This is because the mysql_data_seek() function takes you back to the top of the current resultset, allowing you to reuse query results more than once.

	<!-- code for "modify" rights user list -->
<td><select name="modify[]" multiple>
	<?php
	mysql_data_seek($result, 0);

		while(list($id, $username) = mysql_fetch_row($result))
		{
		$str = "<option value=\"$id\"";
		if ($id == $SESSION_UID) { $str .= " selected"; }
		$str .= ">$username</option>";
		echo $str;
		}
	mysql_free_result ($result);
	mysql_close($connection);
	?>
	</select></td>

Once the form is submitted, the same script is called; however, since the $submit variable will now exist, the second half of the script springs into action.

<?php
if (!$submit)
{
// form
}
else
{
// form has been submitted -> process data

	// checks
	// no file!
	if ($file_size <= 0) { header("Location:error.php?ec=11"); exit; }

	// no users with view rights!
	if (sizeof($view) <= 0) { header("Location:error.php?ec=12"); exit; }

	// no users with modify rights!
	if (sizeof($modify) <= 0) { header("Location:error.php?ec=12"); exit; }

	// check file type
	foreach($allowedFileTypes as $this)
	{
		if ($file_type == $this)
		{
		$allowedFile = 1;
		break;
		}
	}

	// illegal file type!
	if ($allowedFile != 1) { header("Location:error.php?ec=13"); exit; }

	// all checks completed, proceed!

	// all checks completed, proceed!

	// INSERT into db
	$query = "INSERT INTO data (category, owner, realname, created, description, comment) VALUES('$category', '$SESSION_UID', '$file_name', NOW(), '$description', '$comment')";
	$result = mysql_db_query($database, $query, $connection) or die ("Error in query: $query. " . mysql_error());

	// get id from INSERT operation
	$fileId = mysql_insert_id($connection);

	// INSERT user permissions - view
	for($x=0; $x<sizeof($view); $x++)
	{
	$query = "INSERT INTO perms (fid, uid, rights) VALUES('$fileId', '$view[$x]', '1')";
	$result = mysql_db_query($database, $query, $connection) or die ("Error in query: $query. " . mysql_error());
	}

	// INSERT user permissions - modify
	for($x=0; $x<sizeof($modify); $x++)
	{
	$query = "INSERT INTO perms (fid, uid, rights) VALUES('$fileId', '$modify[$x]', '2')";
	$result = mysql_db_query($database, $query, $connection) or die ("Error in query: $query. " . mysql_error());
	}

	// use id to generate a file name
	// save uploaded file with new name
	$newFileName = $fileId . ".dat";
	copy($file, $dataDir . $newFileName);

	// back to main page
	$message = "Document successfully added";
	header("Location: out.php?message=$message");
	mysql_close($connection); }
?>

The first thing to be done is to verify certain file properties - for example, the file size must be greater than zero bytes, and the file must be of an allowed file type. In order to perform these checks, I’m using the four variables created by PHP whenever a file is uploaded - $file is the temporary file name assigned by PHP, $file_size is the size of the uploaded file, $file_type returns the MIME type, and $file_name is the original name of the file.

Assuming everything checks out OK, I then process the descriptive data entered by the user, INSERT it into the “data” table, process the list of users with “view” and modify” rights, INSERT this data into the “perms” table, rename the uploaded file and copy it to the storage area, and redirect the browser back to “out.php” with a status message indicating success.

There is an interesting chicken-and-egg situation here that you may be familiar with. I need to rename the newly-uploaded file to “fileID.dat”; however, I can only do this once the record has been inserted into the table and an ID generated for it. Once the ID is generated, I would normally need to query the table again to obtain the ID. However, the mysql_insert_id() function stores the ID generated by the last INSERT operation, and can conveniently be used here.

Red And Green Clouds

Let’s now take a look at “out.php”, the PHP code used to generate the file list.

<table border="0" cellspacing="0" cellpadding="0">
<?php
// get a list of documents the user has "view" permission for
$connection = mysql_connect($hostname, $user, $pass) or die ("Unable to connect!");
$query = "SELECT data.id, user.username, data.realname, data.created, data.description, data.comment, data.status FROM data, user, perms WHERE data.id = perms.fid AND user.id = data.owner AND perms.uid = '$SESSION_UID' AND perms.rights = '1'";

$result = mysql_db_query($database, $query, $connection) or die ("Error in query: $query. " . mysql_error());

// how many records returned?
$count = mysql_num_rows($result);
?>

<tr>
<td><?php echo $count; ?> document(s) found<p></td>
</tr>

<?php

// iterate through resultset
while(list($id, $owner, $realname, $created, $description, $comment, $status) = mysql_fetch_row($result))
	{

	// correction for empty description
	if ($description == "") { $description = "No description available"; }

	// set filename for filesize() call below
	$filename = $dataDir . $id . ".dat";

	// begin displaying file list with basic information
	?>
	<tr>
	<td><b><?php echo "<a href=\"details.php?id=$id\">$realname</a>"; ?></b></td>
	</tr>

	<tr>
	<td><font size="-1"><?php echo $description; ?></font></td>
	</tr>

	<tr>
	<td><font size="-1">Document created on <?php echo fixDate($created); ?> by <b><?php echo $owner; ?></b> | <?php echo filesize($filename); ?> bytes</font></td>
	</tr>

	<?php
	// code to display status message and icon for each file - snipped
	?>

	<tr>
	<td>
	&nbsp;
	</td>
	</tr>
	<?php
	}
// clean up
mysql_free_result ($result);
mysql_close($connection);
?>
</table>

Pay attention to the query - I’m joining the “user”, “data” and “perms” tables together to obtain detailed information (description, creation date, owner) for those files which contain the logged-in user’s ID and a permission value of 1 (view). Once I have a dataset, I use a “while” loop to iterate through it and display the file list.

Next, the $filename variable. As I’ve already mentioned, once a document is entered into the repository, I don’t plan to retain the original file name, but rather hope to rename it in the form “fileID.dat”. So I’ve generated a filename on the basis of each file ID, and am using the filesize() function to display the size of the file as part of the description.

The fixDate() function is used to turn the mySQL timestamp into something a little more readable.

// function to format mySQL DATETIME values
function fixDate($val)
{
//split it up into components
$arr = explode(" ", $val);
$timearr = explode(":", $arr[1]);
$datearr = explode("-", $arr[0]);
// create a timestamp with mktime(), format it with date()
return date("d M Y (H:i)", mktime($timearr[0], $timearr[1], $timearr[2], $datearr[1], $datearr[2], $datearr[0]));
}

Finally, before we leave this script, let’s explore the section used to display a message indicating whether or not the file is available for check-out.

<?php
		// check the status of each file
		// 0 -> file is not checked out
		// display appropriate message and icon
		if ($status == 0)
		{
		?>
		<tr>
		<td><img src="images/a.jpg" width=40 height=33 alt="" border=0 align="absmiddle"><font size="-1" color="#43c343"><b>This document is available to be checked out</b></font></td>
		</tr>
		<?php
		}
		else
		{
		// not 0 -> implies file is checked out to another user
		// run a query to find out user's name
		$query2 = "SELECT username FROM user WHERE id = '$status'";
		$result2 = mysql_db_query($database, $query2, $connection) or die ("Error in query: $query2 . " . mysql_error());
		list($username) = mysql_fetch_row($result2);
		// and display message and icon
		?>
		<tr>
		<td>
		<img src="images/na.jpg" width=40 height=33 alt="" border=0 align="absmiddle"><font size="-1" color="#e9202a">This document is currently checked out to <b><?php echo $username; ?></b></font>
		</td>
		</tr>
		<?php
		}
?>

Depending on the value of the “status” column, an icon and message is displayed for each file; green indicates that the file is available, while red indicates that it is not.

And here’s what it all looks like.

Digging Deeper

Each of the files listed in “out.php” is hyperlinked to the script “details.php”, which displays detailed file information. This script is passed a file ID via the URL GET method, and uses this ID to query the “data” table and obtain the file description, author comment, owner, creation date and file status.

<?php
// from out.php
// query to obtain detailed information on this file
// in case file is accessed directly,
// query must include constraint to ensure that user has view rights
$connection = mysql_connect($hostname, $user, $pass) or die ("Unable to connect!");
$query = "SELECT data.id, data.owner, category.name, user.username, data.realname, data.created, data.description, data.comment, data.status FROM data, user, category, perms WHERE data.id = '$id' AND data.owner = user.id AND data.category = category.id AND perms.rights = '1' AND perms.uid = '$SESSION_UID' AND data.id = perms.fid";
$result = mysql_db_query($database, $query, $connection) or die ("Error in query: $query. " . mysql_error());
?>

Once a result is obtained, a table is generated to display the various bits of information. If the file is checked out (the status field is not 0), another query is executed to find out more.

<table border="0" cellspacing="4" cellpadding="1">
<?php
// display details
list($id, $ownerId, $category, $owner, $realname, $created, $description, $comment, $status) = mysql_fetch_row($result);
mysql_free_result($result);

// corrections
if ($description == "") { $description = "No description available"; }

if ($comment == "") { $comment = "No author comments available"; }

$filename = $dataDir . $id . ".dat";

?>
<tr>
<td><?php
// display red or green icon depending on file status
if ($status == 0) { ?> <img src="images/a.jpg" width=40 height=33 alt="" border=0 align="absmiddle"><?php } else { ?> <img src="images/na.jpg" width=40 height=33 alt="" border=0 align="absmiddle"> <?php } ?> &nbsp;&nbsp;<font size="+1"><?php echo $realname; ?></font></td>
</tr>

<tr>
<td>Category: <?php echo $category; ?></td>
</tr>

<tr>
<td>File size: <?php echo filesize($filename); ?> bytes</td>
</tr>

<tr>
<td>Created on: <?php echo fixDate($created); ?></td>
</tr>

<tr>
<td>Owner: <?php echo $owner; ?></td>
</tr>

<tr>
<td>Description of contents: <?php echo $description; ?></td>
</tr>

<tr>
<td>Author comment: <?php echo $comment; ?></td>
</tr>

<?php
if ($status != 0)
{
// status != 0 -> file checked out to another user
// query to find out who...
$query = "SELECT user.username FROM data, user WHERE user.id = data.status";
$result = mysql_db_query($database, $query, $connection) or die ("Error in query: $query. " . mysql_error());
list($checkedTo) = mysql_fetch_row($result);
mysql_free_result($result);
// ...and display
?>
<tr>
<td>Currently checked out to: <?php echo $checkedTo; ?></td>
</tr>
<?php
}
?>
</table>

Once the file description has been printed, a menu also needs to be generated at the bottom of the screen. The items that show up in this menu vary depending on the situation:

  1. Every user gets the option to view (not check out) the most recent version of the document, and its revision history.

  2. If the file is not checked out and the user has appropriate “modify” permissions, an option to check out the document is also available.

  3. If the user is the owner of the file, options to edit document properties and delete the document from the system are also available.

Here’s the code that makes this variable menu possible.

	<table border="0" cellspacing="5" cellpadding="5">
	<tr>
	<!-- inner table begins -->
	<!-- view option available at all time, place it outside the block -->
	<td align="center"><a href="view.php?id=<?php echo $id; ?> "><img src="images/view.jpg" width=40 height=40 alt="" border="0"><br><font size="-1">View Document</font></a></td>

		<?php
		if ($status == 0)
		{
		// status = 0 -> file available for checkout
		// check if user has modify rights
		$query2 = "SELECT status FROM data, perms WHERE perms.fid = '$id' AND perms.uid = '$SESSION_UID' AND perms.rights = '2' AND data.status = '0' AND data.id = perms.fid";
$result2 = mysql_db_query($database, $query2, $connection) or die ("Error in query: $query2. " . mysql_error());

			if(mysql_num_rows($result2) > 0)
			{
			// if so, display link for checkout
		?>
			<td align="center"><a href="check-out.php?id=<?php echo $id; ?> "><img src="images/co.jpg" width=40 height=40 alt="" border="0"><br><font size="-1">Check Document Out</font></a></td>
			<?php
			}
		mysql_free_result($result2);

			if ($ownerId == $SESSION_UID)
			{
			// if user is also the owner of the file AND file is not checked out
			// additional actions are available
			?>
			<td align="center"><img src="images/info.jpg" width=40 height=40 alt="" border="0"><a href="edit.php?id=<?php echo $id; ?>"><br><font size="-1">Edit Document Properties</font></a></td>
			<td align="center"><img src="images/delete.jpg" width=40 height=40 alt="" border="0"><a href="delete.php?id=<?php echo $id; ?>"><br><font size="-1">Delete Document</font></a></td>
			<?php
			}
			?>
		<?php
		}
		// ability to view revision history is always available
		// put it outside the block
		?>
	<td align="center"><a href="history.php?id=<?php echo $id; ?>"><img src="images/history.jpg" width=40 height=40 alt="" border="0"><br><font size="-1">Revision History</font></a></td>

	</tr>
	<!-- inner table ends -->
	</table>

And here’s what the page looks like.

Basic Maintenance

As I’ve just explained, the owner of a document has the ability to edit document properties, or delete the document from the system. The scripts that accomplish these tasks are “edit.php” and “delete.php” respectively, and I’ll briefly explain them here.

“edit.php” is almost identical to “add.php” - it contains both a form and a form processing script, and includes all of the same form elements, with the exception of the file upload box. The document owner can use this script to alter the file description, and grant/revoke user permissions.

Since this is a pre-existing document, “edit.php” needs to query the database in order to pre-fill the form with the current values of the various fields. This piece of code is designed to obtain the list of allowed user IDs from the database, create an array, and then use the array to pre-select the usernames in the list boxes.

<?php
if (!$submit)
// form not yet submitted, display initial form
{
	// query to obtain current properties and rights
	$query = "SELECT category, realname, description, comment FROM data WHERE id = '$id' AND status = '0' AND owner = '$SESSION_UID'";
	$result = mysql_db_query($database, $query, $connection) or die ("Error in query: $query. " . mysql_error());

	// error check

	// obtain data from resultset
	list($category, $realname, $description, $comment) = mysql_fetch_row($result);
	mysql_free_result($result);

	// display the form

	// much water under the bridge...
?>

	<td><select name="view[]" multiple>
	<?php
	// query for view list
	$query = "SELECT uid FROM data, perms WHERE perms.fid = '$id' AND data.id = perms.fid AND status = '0' AND perms.rights = '1'";
	$result = mysql_db_query($database, $query, $connection) or die ("Error in query: $query. " . mysql_error());

		// place the result in an array
		$viewList = array();
		$y = 0;
		while (list($uid) = mysql_fetch_row($result))
		{
		$viewList[$y] = $uid;
		$y++;
		}

	// now query to get a complete list of users and user IDs
	$query2 = "SELECT id, username FROM user ORDER BY username";
	$result2 = mysql_db_query($database, $query2, $connection) or die ("Error in query: $query2 . " . mysql_error());

		while(list($ID, $USERNAME) = mysql_fetch_row($result2))
		{
		$str = "<option value=\"$ID\"";

			// iterate through current list of users and select those that match
			foreach($viewList as $temp)
			{
			if ($ID == $temp) { $str .= " selected"; }
			}

		$str .= ">$USERNAME</option>";
		echo $str;
		}
	?>
	</select></td>

}
else
{
// process form
}
?>

Here’s what the form looks like.

Once the form is submitted, the database is UPDATEd with new information.

<?php
if (!$submit)
{
// form not yet submitted, display initial form
}
else
{
	// snip!

	// update db with new information
	$query = "UPDATE data SET category='$category', description='$description', comment='$comment' WHERE id = '$id'";
	$result = mysql_db_query($database, $query, $connection) or die ("Error in query: $query. " . mysql_error());

	// clean out the old permissions
	$query = "DELETE FROM perms WHERE fid = '$id'";
	$result = mysql_db_query($database, $query, $connection) or die ("Error in query: $query. " . mysql_error());

	// INSERT new user permissions - view
	for($x=0; $x<sizeof($view); $x++)
	{
	$query = "INSERT INTO perms (fid, uid, rights) VALUES('$id', '$view[$x]', '1')";
	$result = mysql_db_query($database, $query, $connection) or die ("Error in query: $query. " . mysql_error());
	}

	// INSERT new user permissions - modify
	for($x=0; $x<sizeof($modify); $x++)
	{
	$query = "INSERT INTO perms (fid, uid, rights) VALUES('$id', '$modify[$x]', '2')";
	$result = mysql_db_query($database, $query, $connection) or die ("Error in query: $query. " . mysql_error());
	}

}
?>

The D Word

Finally, we come to “delete.php”, which takes care of removing a document from the file system and the database.

<?php
// checks and includes

// query to ensure that the logged-in user is the owner of this file and the file is not checked out
$connection = mysql_connect($hostname, $user, $pass) or die ("Unable to connect!");
$query = "SELECT status FROM data WHERE id = '$id' AND owner = '$SESSION_UID' AND status = '0'";
$result = mysql_db_query($database, $query, $connection) or die ("Error in query: $query. " . mysql_error());

// error check
// all ok, proceed!
mysql_free_result($result);

// delete from db
$query = "DELETE FROM data WHERE id = '$id'";
$result = mysql_db_query($database, $query, $connection) or die ("Error in query: $query. " . mysql_error());

$query = "DELETE FROM perms WHERE fid = '$id'";
$result = mysql_db_query($database, $query, $connection) or die ("Error in query: $query. " . mysql_error());

$query = "DELETE FROM log WHERE id = '$id'";
$result = mysql_db_query($database, $query, $connection) or die ("Error in query: $query. " . mysql_error());

// delete from directory
$filename = $id . ".dat";
unlink($dataDir . $filename);

// clean up and back to main page
mysql_close($connection);
$message = "Document successfully deleted";
header("Location: out.php?message=$message");
?>

Nothing too complex here - verify that the file can be deleted, DELETE all records containing the file ID from the various tables, delete the file (this will fail if your Web server doesn’t have the right permissions) and go back to the main page.

At this point, I’ve built the foundation for the system, although I have not yet addressed the scripts which actually take care of checking documents in and out. That, together with an explanation of how I plan to implement the revision history mechanism and the search feature, will be addressed in the second part of this article. Download the code, play with it, send me your thoughts/flames/savings…and come back next time for more!

Note: All examples in this article have been tested on Linux/i586 with Apache 1.3.12, mySQL 3.23 and PHP 4.02. Examples are illustrative only, and are not meant for a production environment. YMMV!

This article was first published on 02 May 2001.