$bind = $customer_id;
$fields = â€˜customer_idâ€™;
// use the code for their first problem as a customer code
if ($problem_code === NULL)
$problem_code = create_problem_code();
$customer_code = $problem_code;
$bind = $customer_code;
$fields = â€˜customer_codeâ€™;
$mode = DB_AUTOQUERY_INSERT;
elseif ($action == â€˜updateâ€™)
// update an existing customer record
$bind = $customer_id;
$where = â€˜ customer_id = ? â€˜;
$mode = DB_AUTOQUERY_UPDATE;
$private_error = â€śunknown value for action: $actionâ€ť;
$debug = E_ALL;
user_error(â€˜Error creating problem recordâ€™, E_USER_ERROR);
$stmt = $dbh->autoPrepare(â€˜customersâ€™, $fields, $mode, $where);
Chapter 13: Problem-Tracking System 473
$output = $dbh->execute($stmt, $bind);
$private_error = â€˜error updating customers: <pre>â€™
user_error(â€˜Error updating customer recordâ€™, E_USER_ERROR);
// $params = array_merge($params, compact($fields));
$params[â€˜actionâ€™] = $action;
This is where you expect the staff members to log in to the application. Note the use
of the staff_authenticate() function, which calls the authenticate() function
weâ€™ve been using throughout the book. Before a staff member can log in, he or she
must enter a valid password and username.
The page is going to show two lists of queries, a list of calls owned by the cur-
rently logged-in staff member, and a list of unowned calls, probably stuff that has
been entered over the Web.
$params = $_GET;
$last_list_value = NULL;
// get a list of all open unowned problems (in the hopes
// that the current user might grab one) and the current
// userâ€™s open problems. include information from the
// last entry in the history table for each problem.
$params[â€˜whereâ€™] = â€˜ status_id <> ? and ifnull(staff_id,?) = ?
$params[â€˜bindâ€™] = array(status_id(â€˜Closedâ€™), $staff_id,
$params[â€˜order_byâ€™] = â€˜ staff_id, last_entry_dt asc â€˜;
$list_titles = array(
â€˜â€™ => â€˜Unowned Callsâ€™
, $staff_id => â€˜Open Calls for â€˜.$staff_name
474 Part IV: Not So Simple Applications
$list_key = â€˜staff_idâ€™;
$list_titles = array(
â€˜Calls for â€˜.$params[â€˜firstnameâ€™].â€™
$list_key = â€˜customer_codeâ€™;
$last_list_value = $params[$list_key];
$tpl = template_object();
$neparams = array_filter($params, â€˜notemptyâ€™);
$first_list_value = $last_list_value;
foreach ($params[â€˜problemsâ€™] as $row)
if ($row[$list_key] !== $last_list_value)
$last_list_value = $row[$list_key];
$row[â€˜sourceâ€™] = source($row[â€˜source_idâ€™]);
$row[â€˜sourceâ€™] = â€˜No Source IDâ€™;
$row[â€˜statusâ€™] = status($row[â€˜status_idâ€™]);
Chapter 13: Problem-Tracking System 475
$row[â€˜statusâ€™] = â€˜No Status IDâ€™;
if (count($params[â€˜problemsâ€™]) > 0 && $last_list_value !=
The application presented in this chapter is very useful, as just about every
information-services department will have some sort of system to track user com-
plaints. As we stated at the beginning of this chapter, the problem-tracking system
presented here is fairly generic. However, it can definitely be the basis for a more
detailed application that youâ€™d custom-design for use in the workplace.
IN THIS CHAPTER
â—† Creating a secure site
â—† Working with PHP sessions
â—† Communicating with a credit-card-authorization service
IN THIS CHAPTER YOU are going to learn what you need to create a shopping-cart appli-
cation using PHP and MySQL. But this application is different from the others in this
book in that itâ€™s really impossible to talk about what you need for it without delving
into some other topics. In addition to understanding the schema and the PHP code,
you need to have a basic understanding of how to maintain state between pages. (If
you donâ€™t know what that means, donâ€™t worry, weâ€™ll get to it momentarily.) Also, you
need to know how to securely process credit-card transactions.
Donâ€™t read another sentence if you have not read through Chapter 12. You
must understand how the catalog works before you can take this on. For rea-
sons that shouldnâ€™t be too tough to understand, we built the shopping cart
atop the catalog.
Determining the Scope and
Goals of the Application
Anyone familiar with the Web knows what a shopping cart does. But it will be a bit
easier to understand what the code for this application is doing if we explicitly state
some of its purposes.
First, the application is going to have to display your wares; for this you reuse
the code from Chapter 12. Further, users have to be able to choose items that they
want to buy. Obvious, we know. Note what must happen after a user chooses an
item: The exact item must be noted, and the user should have the opportunity to
continue shopping. The server must remember what has been ordered. As the user
continues to browse, the server must keep track of the user and allow him or her to
check out with his or her requested items.
478 Part IV: Not So Simple Applications
This functionality requires you to use some method for maintaining state â€” that
is, the Web server needs to remember who the user is as he or she moves from page
to page. Now, you might recall that in the introduction to this book we discussed
the stateless nature of the Web and the HTTP protocol that the Web makes use of.
After responding to an HTTP request, the server completely and totally forgets what
it served to whom. The server takes care of requested information serially â€” one at
a time, as requests come in. There is no persistence, no connection that lasts after a
page has been served.
To give your site memory so that, in this case, the cart can remember who
ordered what, some information that identifies the user must be sent with each
page request. On the Web, you can store this information in exactly five ways:
â—† You can set a large cookie (a nugget of information thatâ€™s stored â€”
possibly in a persistent file â€” on the client machine) containing all
information about the state of the transaction and, in this case, the
contents of the shopping cart. Each time a request is made, the informa-
tion stored in the cookie is sent to the server. Note that the browser stores
the cookie information in a small text file (or in RAM) and sends the
information to the server with each request.
â—† You can set a small cookie that merely identifies the user to the server.
With the identifier, the server can find a database record that contains all
further information about state.
â—† You can make users use unique URLs (typically, theyâ€™ll call server-side
programs with unique, identifying, arguments). These work like the pre-
vious â€śsmall cookieâ€ť strategy, but enable users to have cookies turned off.
â—† You can send hidden data about the state to be kept on the userâ€™s
screen and resent (posted) with the next request. The session-identifying
information can be stored in hidden form fields.
â—† You can send hidden data to merely identify the user to be kept on the
userâ€™s screen and resent (posted) with the next request. Then the user-
identifying data are used on the server to find a database record that
contains all further information about state.
What do you need?
Since you are building this application atop the catalog, much of the code and infor-
mation should be very familiar. The one notable thing that is going to be added to
every page is a button that lets people go directly to the checkout. Figure 14-1 shows
Chapter 14: Shopping Cart 479
Figure 14-1: Category page with checkout button
What do you need to prevent?
You need to be careful about two things:
â—† Making sure you can track your users from page to page
â—† Keeping credit-card numbers and other personal information away from
The database used here is added to the catalog database. Information about goods
still comes from the tables reviewed there, while information on orders is stored in
the tables shown here.
The data schema here, represented in Figure 14-2, should look familiar if you
studied Chapter 1 and Chapter 12.