GOT Donâ€™t worry, weâ€™ve all got problems. Relationships falter, bosses
make capricious demands, and family â€” oh, we all know about family. Sadly, in the
crazy lives that we all live, PHP and MySQL can do nothing to make your girl/
boyfriend love you more or make your dealings with your parents or in-laws any
easier. But no scripting language or relational database is better equipped in these
But if youâ€™re working for a company that sells or otherwise dispenses goods, it is
a virtual guarantee that someone somewhere is going to be unhappy with what he
or she has received. When that person complains, you are going to want to have a
place in which to record the problems and the steps required for resolution.
The problem-tracking application in this chapter can be used for that purpose.
What we have here is fairly generic, and depending on the goods involved with
your business, it is likely that you are going to want some fields that apply to your
specific products. Anyhow, this application should get you moving in the right
Determining the Scope and Goals of
This problem-tracking system should have aspects that are publicly available and
others that only someone with the proper authorization can view. It makes sense to
have a form that users can access over the Web in order to report their problems.
Alternatively, someone on the support staff should be able to report problems â€” for
example, while taking a phone call from a dissatisfied customer.
Once the problem is entered, it should be tracked by the staff. Each action taken
in the attempt to solve the problem should be noted. And the tracking should have 441
442 Part IV: Not So Simple Applications
a public and a private realm â€” actions that you want the user to see must be differ-
entiated from those that you do not want the user to see.
Those with problems should be able to keep track of them in two ways. They
should be emailed whenever a publicly viewable update is made to their case, and
a Web page detailing their problem should be available.
What do you need?
The first thing you need is a form into which people can enter their complaints.
What we present in Figure 13-1 is fairly generic; remember that for your own appli-
cations you will probably want to add information regarding specific products.
Figure 13-1: Problem entry form
Once a problem is entered, there must be a place for the staff to work on the
complaint. It should include all the information about the user, the history of the
complaint, and a place to enter new information. This problem-update form would
look something like the one in Figure 13-2.
The support-staff members need a home, a place where they can log in and see
both unassigned tasks and those that are assigned to them and are still open. The
staff page would look something like the one in Figure 13-3.
Chapter 13: Problem-Tracking System 443
Figure 13-2: Problem update form
Figure 13-3: Staff page
444 Part IV: Not So Simple Applications
If you want to see if any of your users are hypochondriacs, you can use the user-
history page shown in Figure 13-4, which lists all problems associated with a user.
Figure 13-4: User history page
What do you need to prevent?
In setting up this part of the application, youâ€™re concerned with gathering informa-
tion efficiently and in a way thatâ€™s pleasant for the user. Therefore, your worries are
more of an interface-design nature, and thus more in the realm of Web design than
Developers, though, are concerned with making sure that the data collected is
valid, and complies with database limitations. You might want to endow your
forms with some client-side scripting that checks values for obvious problems
before sending them in.
Designing the Database
As you can see from Figure 13-5, the problems table is at the center of the schema.
Chapter 13: Problem-Tracking System 445
sources entered_by city
source_id source_id state
source entry_dt zip
Figure 13-5: Tracking system schema
446 Part IV: Not So Simple Applications
Here are some design considerations we had to keep in mind as we designed our
â—† Each customer can have one or many problems. The history table records
the steps taken to remedy the problem or problems.
â—† The status table is a lookup table, containing the possible states of a
problem, notably open, closed, in processing, and so on.
â—† The sources table is another lookup table, which records where the prob-
lem was originally recorded. If a user enters a complaint over the Web, the
sources table will record that; complaints received by the support staff
might originate from a phone call, email, or flaming arrow.
â—† The entry_types table notes whether a specific item in the history table
should be public or private. If it is private, it will not be available on the
Web page when the user comes to view the progress of the problem, and
an email will not be sent to the user when an update takes place. The pub-
lic updates will be viewable and the user will receive email notification.
Now for a couple of notes on this schema and the create statements that follow.
Depending on how you plan on running your site, you may wish to add a table or
change a column definition or two.
Notice that we have a problem_code column in the problems table. However, if
you will be emailing users regarding the status of problems, you may want some-
thing a little less transparent than the following: http://yoursite.com/tracking/
In Chapter 9 we take some precautions when we run into a similar situation. We
didnâ€™t want people to gain access to restricted parts of our data simply by guessing
at variable names in the URL. Here we adopt the same technique we used there in
the survey application, creating a random 8-character alphanumeric string from the
md5() and uniqueid() functions. Itâ€™s true that we run a risk of the same random
number coming up twice, and in fact this approach might not be right for a very
large application. But it works here.
Listing 13-1 shows the create statements for the tables we used in this applica-
tion. In addition to the create statements, this listing includes some of the default
data you will need to start the application. Note that if you install this application
from the CD-ROM you will have a full set of dummy data you can play with.
Listing 13-1: create Statements Used in the Problem-Tracking System
drop table if exists status;
create table status
status_id tinyint not null auto_increment
, status varchar(20) not null
, primary key (status_id)
Chapter 13: Problem-Tracking System 447
insert into status (status) values (â€˜Openedâ€™);
insert into status (status) values (â€˜In Progressâ€™);
insert into status (status) values (â€˜Closedâ€™);
insert into status (status) values (â€˜Re-openedâ€™);
drop table if exists status_seq;
create table status_seq
id tinyint not null auto_increment
, primary key (id)
insert into status_seq (id) select max(status_id)+1 from status;
drop table if exists sources;
create table sources
source_id tinyint not null auto_increment
, source varchar(10) not null
, primary key (source_id)
insert into sources (source) values (â€˜webâ€™);
insert into sources (source) values (â€˜emailâ€™);
insert into sources (source) values (â€˜phoneâ€™);
insert into sources (source) values (â€˜in-storeâ€™);
insert into sources (source) values (â€˜staffâ€™);
insert into sources (source) values (â€˜programâ€™);
drop table if exists source_seq;
create table source_seq
id int not null auto_increment
, primary key (id)
insert into source_seq (id) select max(source_id)+1 from sources;
drop table if exists entry_types;
create table entry_types
448 Part IV: Not So Simple Applications
Listing 13-1 (Continued)
entry_type_id tinyint not null auto_increment
, entry_type varchar(10) not null
, primary key (entry_type_id)
insert into entry_types (entry_type) values (â€˜privateâ€™);
insert into entry_types (entry_type) values (â€˜publicâ€™);
drop table if exists entry_type_seq;
create table entry_type_seq
id tinyint not null auto_increment
, primary key (id)
insert into entry_type_seq (id) select max(entry_type_id)+1 from
drop table if exists staff;
create table staff
staff_id int not null auto_increment
, username varchar(20) not null
, password varchar(255) not null
, staff_name varchar(50) not null
, active tinyint default 1
, primary key (staff_id)
, unique (username)
insert into staff (username,password,staff_name) values