Adding Fields and Tables to the Database
Adding Fields and Tables to the Database
This tutorial will walk you through the process of adding fields and tables
to the database. Expanding or modifying the schema of the database may be
necessary if a programmer wishes to track or report new information though
the interface. In this tutorial we will first step through an example
which adds a new field to an existing database table and creates an
interface to enter information into that field. The second part of this
tutorial will follow the creation of a new database table and the creation
of an interface to populate that table.
Written by Ziba R. Scott
and Adam D. Gorski
Table of Contents
Prepare the test environment
Modifying the database and the interface code will disrupt the application.
Clone the production system to a safe development environment. Make sure
the conf.php points to the development copy of the
database.
Adding a field to an existing table
Adding extra fields to a table will not break any HRDIODB functionality
unless it is created with a requirement to have a value submitted upon
record creation and none is supplied. This tutorial will add a
verified_by field to the HRDI_Days
table and create an interface in the "Edit Day" view. Through the
interface, an individual will be able to enter their name to be stored in
the verified_by field.
First add the verified_by field to the
HRDI_Days table using your favorite database interface.
Here is the command line MySQL method:
USE DATABASE hrdi_dev;
ALTER TABLE `HRDI_Days` ADD `verified_by` VARCHAR( 255 ) ;
In order to interact with the value that has been added to the table, the
data must be fetched with the hrdi_db class. Because the
'verified_by' field has been added to the 'HRDI_Days' table, the existing
UARS_Day class does all the work of reading and writing via the hrdi_db
class. It is now only necessary to access the 'verified_by' variable as a
member of the data array that is a part of every UARS_Day class object. See
the Reusing the uars_day Class tutorial and the hrdi_db
class.
To add an interface to the day edit screen for any field in the
HRDI_Days table is very simple. Just create an HTML
input of the type desired with the name of the input set to match the name
of the database field. To print out the value access it from the
$day->data array by name. Add this code to
day.php just after the FORM tag:
<tr>
<td class="label">Verified By:</td>
<td class="data" colspan="2">
<?php if ($edit) { ?>
<input type="text" name="verified_by" size="7" maxlength="7"
<?php } else {
print @$day->data['verified_by'];
} ?>
</td>
</tr>
Most of the above code is HTML for formatting. Adding an editable field to
the database only takes two lines of SQL and three lines of PHP. Also,
because the free form search dynamically reads the database, the new field
will also be searchable.
Adding a table to the database
Adding a new table to the HRDIODB will not interfere with the program's
operation unless you explicitly write code that does so. This example will
detail the creation of a log table that will have an entry for every visit
to a UARS Day made via the HRDIODB. First, create the table in MySQL. The
new table will be called day_log and have three fields:
id, which increments automatically,
day, the day viewed, and timestamp,
the time the day was viewed.
CREATE TABLE `day_log` (
`id` INT NOT NULL AUTO_INCREMENT,
`day` INT NOT NULL,
`timestamp` TIMESTAMP NOT NULL,
PRIMARY KEY(`id`)
);
Next, use the hrdi_db class to push a record into the table. Doing
this in day.php just after the hrdi_db is used to create a
new uars_day object will ensure that a log record is inserted each
time a day is viewed or edited.
// Existing Code
// Create a UARS day class instance
// New Code
// Insert a day log entry
$hdb->db_query("INSERT INTO `day_log` (`id`,`day`,`timestamp`)
VALUES ('','$uars_day',NOW())");
Further integration
The previous short examples may be all that is needed to enhance the
application to meet new needs. However, for more advanced extensions of
the database, be sure to read about the capabilities of the
PEAR DB database abstraction layer
used by this project. Also look at hrdi_db, the convenience class
written for this project to take advantage of
PEAR DB.
