<<

. 76
( 132 .)



>>

, (˜barney™,password(˜barney™),™Barney Rubble™)
;

drop table if exists staff_seq;
create table staff_seq
(
id int not null auto_increment
Chapter 13: Problem-Tracking System 449

, primary key (id)
)
type=InnoDB
;
insert into staff_seq (id) select max(staff_id)+1 from staff;

drop table if exists customers;
create table customers
(
customer_id integer not null auto_increment
, customer_code varchar(8)
, firstname varchar(40)
, lastname varchar(40)
, address varchar(40)
, address2 varchar(40)
, city varchar(20)
, state char(2)
, zip char(5)
, zip4 char(5)
, email varchar(255)
, day_area char(3)
, day_prefix char(3)
, day_suffix char(4)
, day_ext char(5)
, day_start char(8)
, day_end char(8)
, eve_area char(3)
, eve_prefix char(3)
, eve_suffix char(4)
, eve_ext char(5)
, eve_start char(8)
, eve_end char(8)
, primary key (customer_id)
)
type=InnoDB
;

drop table if exists customer_seq;
create table customer_seq
(
id int not null auto_increment
, primary key (id)
)

Continued
450 Part IV: Not So Simple Applications

Listing 13-1 (Continued)
type=InnoDB
;

drop table if exists problems;
create table problems
(
problem_id integer not null auto_increment
, customer_id integer not null
, problem_code char(8) not null
, status_id tinyint null
, staff_id integer null
, summary text
, problem text
, entered_by varchar(20) null
, source_id tinyint null
, entry_dt datetime
, modify_dt timestamp
, last_public_entry_id int null
, last_entry_id int null
, primary key (problem_id)
, key (customer_id)
, foreign key (customer_id) references customers (customer_id) on
delete cascade
, key (status_id)
, foreign key (status_id) references status (status_id) on delete
set null
, key (source_id)
, foreign key (source_id) references sources (source_id) on delete
set null
, key (staff_id)
, foreign key (staff_id) references staff (staff_id) on delete set
null
, unique (problem_code)
)
type=InnoDB
;
drop table if exists problem_seq;
create table problem_seq
(
id int not null auto_increment
, primary key (id)
)
type=InnoDB
Chapter 13: Problem-Tracking System 451

;



drop table if exists history;
create table history
(
entry_id integer not null auto_increment
, problem_id integer not null
, entry_type_id tinyint not null
, entered_by varchar(20) null
, source_id tinyint not null
, entry_dt timestamp
, notes text
, primary key (entry_id)
, key (problem_id), foreign key (problem_id) references problems
(problem_id) on delete cascade
, key (entry_type_id), foreign key (entry_type_id) references
entry_types (entry_type_id) on delete cascade
, key (source_id), foreign key (source_id) references sources
(source_id) on delete cascade
)
type=InnoDB
;
drop table if exists history_seq;
create table history_seq
(
id int not null auto_increment
, primary key (id)
)
type=InnoDB
;
drop table if exists admin;
create table admin
(
username varchar(50) not null
, password varchar(255) not null
, primary key (username)
)
type=InnoDB
;
insert into admin values (˜jay™,password(˜rules™));

delete from mysql.db where Db = ˜tracking™;

Continued
452 Part IV: Not So Simple Applications

Listing 13-1 (Continued)
grant delete, insert, select, update
on tracking.*
to nobody@localhost identified by ˜ydobon™
;

flush privileges;




Code Overview
The only really new part of this example is that it uses the PEAR Integrated
Template class. Templates are a common and useful way to separate an applica-
tion™s code from its design. Since these are frequently built by two different sets of
people, working on different schedules, keeping these two parts of your application
(not to mention the designers and the coders) at a distance from each other can make
your life a lot easier. Plus, looking ahead, a site™s design is something that is going
to change much more frequently than its basic functionality, so maintenance
becomes easier as well.
The idea behind a template is pretty easy to pick up. In one file you write all the
HTML for a page (or part of a page ” such as the navigational elements). Elements
of the page that will be filled in with data from a database, or with values resulting
from a calculation like “Total Amount Due,” are represented by some kind of stan-
dardized placeholder, like so:

<tr>
<td><b>Total Amount Due:</b></td>
<td align=”right”><b>{total_due}</b></td>
</tr>

We™ve picked the simplest templating system readily available, the
IntegratedTemplate (or IT) class from PEAR. It looks just like the preceding exam-
ple, shockingly enough, and also has some capacity for loops so you can repeat part
of a template, like a row in a parts table, as many times as you have rows of data to
fill it. There are lots of other, more advanced and complicated templating systems
out there. Try changing this example around some until you get frustrated at not
being able to do something ” that™s how you™ll know what to look for.


You™ll find IntegratedTemplate and its documentation here:
http://pear.php.net/package-info.php?pacid=108.
If IT isn™t to your liking, you may want to investigate FastTemplate and
Smarty, two other template engines.
Chapter 13: Problem-Tracking System 453


Code Breakdown
This application makes more liberal use of includes than some of the previous ones
you have seen. It contains a couple of very long forms that could really clutter up
a page. They have been pushed off to templates.

Reusable functions from /book/tracking/
functions.php
The base function set, described in Chapter 9, will be used here once again. The first
few of these functions are for convenience. The ones a little further down do some
pretty heavy and cool work.

fetch_staff()
If you™ve looked at some of the other applications in this book, this type of function
should be clear. Basically, this function takes a series of parameters that it uses to
modify a generic SELECT query that™s run against the staff table. If no parameters
are sent to this function, the most basic SELECT is run:

SELECT * FROM staff;

If arguments exist, the code uses them to modify the SELECT statement. It
employs a bit of intelligence here. If the arguments are numeric ” the function uses
is_numeric to figure this out ” the code adjusts the SELECT statement to examine
the staff_id field, like this:

SELECT * FROM staff; WHERE staff_id LIKE argument_value;

Alternatively, if the argument is a string (is_string comes into play for the job)
the SELECT statement looks at the username field. For example:

SELECT * FROM staff; WHERE username LIKE argument_value;

Here™s the full function.

function fetch_staff()
{
$params = func_get_args();
$wheres = array();
$bind = array();
foreach ($params as $arg)
{
if (is_string($arg))
{
454 Part IV: Not So Simple Applications

$wheres[] = ˜ username like ? ˜;
$bind[] = $arg;
}
elseif (is_numeric($arg))
{
$wheres[] = ˜ staff_id like ? ˜;
$bind[] = $arg;
}
else
{
user_error(
˜Invalid argument to fetch_staff()
:™.var_export($arg,TRUE)
, E_USER_NOTICE
);
}
}
$query = ˜select * from staff ˜;
if (count($wheres) > 0)
{
$query .= ˜ where ˜.implode(˜ or ˜, $wheres);
}
$result = db_connect()->getAll($query, $bind,
DB_FETCHMODE_ASSOC);
if (is_array($result) && count($result) == 1)
{
$result = array_shift($result);
}
return $result;

<<

. 76
( 132 .)



>>