Writing the Non-Library Code

I'll begin by describing all the parts of this project except the library. The library module is driven by the needs of the other PHP programs, so it actually makes sense to look at the other programs first.

Preparing the Database

The database for this segment is almost the same as the one used in Chapter 9 "Data Normalization." I added one table to store queries. All other tables are the same as those in Chapter 9. The SQL script to create this new version of the spy database is available on the CD-ROM as "buildSpy.sql." Note this version is slightly different than the version in Chapter 9, because it includes several queries as part of the data! In order to make the program reasonably secure, I didn't want typical users to be able to make queries. I also don't want users to be limited to the few queries I thought of when building this system. One solution is to store a set of queries in the database and let appropriate users modify the queries. I called my new table the storedQuery table. It can be manipulated in the system just like the other tables, so a user with password access can add, edit, and delete queries. Here is the additional code used to build the storedQuery table:

######################################
# build storedQuery table
######################################

CREATE TABLE storedQuery (
  storedQueryID int(11) NOT NULL AUTO_INCREMENT,
  description varchar(30),
  text varchar(255),
  PRIMARY KEY (storedQueryID)
);

INSERT INTO storedQuery VALUES (
  null,
  'agent info',
  'SELECT * FROM agent'
);

The storedQuery table has three fields. The description field holds a short English description of each query. The text field holds the actual SQL code of the query.

TRAP?

Proper SQL syntax is extremely important when you store SQL syntax inside an SQL database as I'm doing here. It's especially important to keep track of single and double quotes. To include the single quotes that some queries require, you'll need to precede the quote with a backslash character. For example, if I want to store the following query:

SELECT * FROM agent WHERE agent.name = 'Bond',

I would actually store this text instead:

SELECT * FROM agent WHERE agent.name = \'Bond\'

This is necessary in order to store the single quote characters. Otherwise they will be interpreted incorrectly. I'll show you how to remove the backslash characters at the appropriate time.

Examining the spyMaster.php Program

The sypMaster.php program is the entry point into the system. All access to the system comes from this page. It has two main parts. Each segment encapsulates an HTML form that will send a request to a particular PHP program. The first segment has a small amount of PHP code that sets up the query list box.

Creating the Query Form

<html>
<head>
<title>Spy Master Main Page</title>
<?
  include "spyLib.php";
?>

</head>
<body>
<form action = "viewQuery.php"
      method = "post">

<table border = 1
       width = 200>
<tr>
  <td><center><h2>View Data</h2></center></td>
</tr>

<tr>
  <td><center>
    <select name = "theQuery" size = 10>
<?
//get queries from storedQuery table

$dbConn = connectToSpy();
$query = "SELECT * from storedQuery";
$result = mysql_query($query, $dbConn);
while($row = mysql_fetch_assoc($result)){
  $currentQuery = $row['text'];
  $theDescription = $row['description'];
  print <<<HERE
      <option value = "$currentQuery">$theDescription</option>

HERE;
  } // end while

?>
    </select>
  </center>
</tr>

<tr>
  <td><center>
    <input type = "submit"
       value = "execute request" >
  </center></td>
</tr>
</table>

</form>

Most of the code is ordinary HTML. The HTML code establishes a form that will call viewQuery.php when the user presses the Submit button. I added some PHP code here as well. The PHP generates a special input box based on the entries in the storedQuery table.

Including the spyLib Library

The first thing to notice is the include() statement. This command allows you to import another file. PHP will read that file and interpret it as HTML. An included file can contain HTML, CSS, or PHP code. Most of the functionality for the spy data program is stored in the spyLib.php library program. All the other PHP programs in the system begin by including spyLib.php. Once this is done, every function in the library can be accessed as if it were a locally defined function. As you will see, this provides tremendous power and flexibility to a programming system.

Connecting to the Spy Database

The utility of the spyLib library becomes immediately apparent as I connect to the spy database. Rather than worrying about exactly what database I'm connecting to, I simply defer to the connectToSpy() function in spyLib(). In the current code I don't need to worry about the details of connecting to the database. With a library I can write the connecting code one time and re-use that function as needed.

TRICK?

There's another advantage to using a library when connecting to a database. It's quite likely that if you move this code to another system you'll have a different way to log in to the data server. If the code for connecting to the server is centralized, it only needs to be changed in one place when you want to update the code. This is far more efficient than searching through dozens of programs to find every reference to the mysql_connect() function.

Notice the connectToSpy() function returns a data connection pointer I can use for other database activities.

Retrieving the Queries

I decided to encode a series of pre-packaged queries into a table. I'll explain more about my reasons for this in the section on the viewQuery program. The main form needs to present a list of query descriptions and let the user select one of these queries. I use an SQL SELECT statement to extract everything from the storedQuery table. I then use the description and text fields from storedQuery to build a multiline list box.

Creating the Edit Table Form

The second half of the spyMaster program presents all the tables in the database and allows the user to choose a table for later editing. Most of the functionality in the system comes through this section. Surprisingly, there is no PHP code at all in this particular part of the page. An HTML form will send the user to the editTable.php program.

<hr>
<form action = "editTable.php"
      method = "post">

<table border = 1>
<tr>
  <td colspan = 2><center>
    <h2>Edit / Delete table data</h2>
  </center></td>
</tr>

<tr>
  <td>Password:</td>
  <td>
    <input type = "password"
       name = "pwd"
       value = "absolute"><br>
  </td>
</tr>

<tr>
  <td colspan = 2><center>
    <select name = "tableName"
        size = 5>
      <option value = "agent">agents</option>
      <option value = "specialty">specialties</option>
      <option value = "operation">operations</option>
      <option value = "agent_specialty">agent_specialty</option>
      <option value = "storedQuery">storedQuery</option>
    </select>
  </center></td>
</tr>

<tr>
  <td colspan = 2><center>
    <input type = "submit"
       value = "edit table">
  </center></td>
</tr>
</table>

</form>

</body>
</html>

TRICK?

To make debugging easier, I pre-loaded the password field with the appropriate password so I don't have to type it in each time. In a production environment, you should of course leave the password field blank so the user cannot get into the system without the password.

Building the viewQuery.php Program

When the user chooses a query, program control is sent to the viewQuery.php program. This program does surprisingly little on its own.

<html>
<head>
<title>View Query</title>
</head>
<body>

<center>
<h2>Query Results</h2>
</center>
<?
include "spyLib.php";

$dbConn = connectToSpy();

//take out escape characters...
$theQuery = str_replace("\'", "'", $theQuery);

print qToTable($theQuery);

print mainButton();

?>

</body>
</html>

Once viewQuery.php connects to the library, it uses functions in the library to connect to the database and print out desired results. The qToTable() function does most of the actual work. It will take whatever query is passed to it and generate a table with add, delete, and edit buttons.

You might wonder why I chose this approach to queries. After all, I could have let the user type in a query directly or provided some sort of form that allows the user to search for certain values. Either of these approaches has advantages, but they also pose some risks. It's very dangerous to allow direct access to your data from a Web form. Malicious users can introduce Trojan Horse commands that can snoop on your data, change data, or even delete information from the database. I sometimes build a form that has enough information to create an SQL query and then build that query in a client-side form (sounds like a good end-of-chapter exercise). In this case, I stored queries in another table. People with administrative access have the ability to add new queries to the database, but ordinary users do not. I pre-loaded the storedQuery database with a number of useful queries, then added the capacity to add new queries whenever the situation demands it. There are still some draw-backs to this system (primarily that ordinary users cannot build custom queries), but it is far more secure than a system that builds a query based on user input.

The str_replace() function is necessary because SQL queries contain single quote (') characters. When I store a query as a VARCHAR entity, the single quotes embedded in the query cause problems. The normal solution to this problem is to use a backslash, which indicates that the quote should not be immediately interpreted, but should be considered a part of the data. The problem with this is the backslash is still in the string when I try to execute the query. The str_replace() function replaces all instances of "\'" with a simple single quote (').

Note that the qToTable() function doesn't actually print anything to the screen. All it does is build a complex string of HTML code. The viewQuery.php program prints the code to the screen.

TRICK?

If you are using a library, it's best if the library code does not print anything directly to the screen. Instead, it should simply return a value to whatever program called it. This will allow multiple uses for the data. For example, if the qToTable() function printed directly to the screen, you could not use it to generate a file. Since the library code returns a value but doesn't actually do anything with that value, the code that calls the function has the freedom to use the results in multiple ways.

The mainButton() function produces a simple HTML form that directs the user back to the spyMaster.php page. Even though the code for this is relatively simple, it is repeated so often that it makes sense to store it in a function rather than copying and pasting it in every page of the system.

Viewing the editTable.php Program

The editTable.php follows a familiar pattern. It has a small amount of PHP code, but most of the real work is sent off to a library function. The main job of this module is to check for an administrative password. If the user does not have the appropriate password, further access to the system is blocked. If the user does have the correct password, the very powerful tToEdit() function provides access to the add, edit, and delete functions.

<html>
<head>
<title>Edit table</title>
</head>
<body>
<h2>Edit Table</h2>
<?
include "spyLib.php";

//check password

if ($pwd == $adminPassword){
  $dbConn = connectToSpy();
  print tToEdit("$tableName");
} else {
  print "<h3>You must have administrative access to proceed</h3>\n";
} // end if
print mainButton();

?>
</body>
</html>

The $pwd value comes from a field in the spyMaster.php page. The $adminPassword value is stored in spyLibrary.php. (The default admin password is "absolute," but you can change it to whatever you want by editing spyLib.php.)

Viewing the editRecord.php Program

The editRecord.php program is called from a form generated by editTable.php. (Actually, the tToEdit() function generates the form, but tToEdit() is called from editTable.php.) This program expects variables called $tableName, $keyName, and $keyVal. These variables (provided by tToEdit() automatically) help editRecord build a query that will return whatever record the user selects. (You'll need to trust me for now on how the appropriate record data is sent. You can read ahead to the description of tToEdit() for details on how this exactly works.)

<html>
<head>
<title>Edit Record</title>
</head>
<body>
<h1>Edit Record</h1>
<?

// expects $tableName, $keyName, $keyVal
include "spyLib.php";

$dbConn = connectToSpy();

$query = "SELECT * FROM $tableName WHERE $keyName = $keyVal";
print smartRToEdit($query);

print mainButton();

?>
</body>
</html>

The editRecord.php program prints out the results of the smartRToEdit() library function. This function takes the single-record query and prints HTML code that lets the user update the record appropriately.

Viewing the updateRecord.php Program

The smartRToEdit() function calls another PHP program called updateRecord.php. This program calls a library function that actually commits the user's changes to the database.

<html>
<head>
<title>Update Record</title>
</head>
<body>

<h2>Update Record</h2>
<?

include "spyLib.php";

$dbConn = connectToSpy();

$fieldNames = "";
$fieldValues = "";

foreach ($_REQUEST as $fieldName => $value){
  if ($fieldName == "tableName"){
    $theTable = $value;
  } else {

    $fields[] = $fieldName;
    $values[] = $value;
  } // end if
} // end foreach

print updateRec($theTable, $fields, $values);

print mainButton();

?>
</body>
</html>

It is more convenient for the updateRec() function if the field names and values are sent as arrays, so the PHP code in updateRecord.php converts the $_REQUEST array to an array of fields and another array of values. These two arrays are passed to the updateRec() function, which will process them.

Viewing the deleteRecord.php Program

The deleteRecord.php program acts in a now-familiar manner. It mainly serves as a wrapper for a function in the spyLib library. In this particular case, the program simply sends the name of the current table, the name of the key field, and the value of the current record's key to the delRec() function. That function will delete the record and return a message regarding the success or failure of the operation.

<html>
<head>
<title>Delete Record</title>
</head>
<body>
<h2>Delete Record</h2>
<?

include "spyLib.php";

$dbConn = connectToSpy();
print delRec($tableName, $keyName, $keyVal);
print mainButton();
?>

</body>
</html>

Viewing the addRecord.php Program

Adding a record is actually much like editing a record. It actually requires two distinctive steps. The addRecord.php program calls the tToAdd() function, which builds a form allowing the user to add data to whichever table is currently selected. It isn't necessary to send any information to this function except the name of the table, because the key value will be automatically generated by tToAdd().

<html>
<head>
<title>Add a Record</title>
</head>
<body>
<h2>Add Record</h2>
<?
include "spyLib.php";

$dbConn = connectToSpy();

print tToAdd($tableName);
print mainButton();

?>

</body>
</html>

Viewing the processAdd.php Program

The tToAdd() function called by the addRecord.php program doesn't actually add a record. Instead, it places an HTML form on the screen that allows the user to enter the data for a new record. When the user submits this form, he or she is passed to the processAdd.php program, which calls procAdd() in the library code. The procAdd() function generates the appropriate SQL code to actually add the new record to the table. In order to do this, procAdd() needs to know the field names and values. These are passed to the function in arrays just like in updateRecord.php.

<html>
<head>
       <title>Process Add</title>
</head>
<body>
<h2>Process Add</h2>
<?
include "spyLib.php";

$dbConn = connectToSpy();

$fieldNames = "";
$fieldValues = "";

foreach ($_REQUEST as $fieldName => $value){
  if ($fieldName == "tableName"){
    $theTable = $value;
  } else {
    $fields[] = $fieldName;
    $values[] = $value;
  } // end if
} // end foreach

print procAdd($theTable, $fields, $values);

print mainButton();


?>
</body>
</html>