Tutorials

Updating a database through forms

Inserting a new record

If a user wants to update the mymusic database through a web form, the form itself must use field names exactly as they are in the database. In the following example, a user wants to add a missing track to a FooBar Fighters album.

Example 2-3-1
<input type="text" name="SongTitle" value="Tom's Diner">
<input type="text" name="TrackNum" value="15">
<input type="hidden" name="AlbumID" value="1">
<input type="submit" value="Submit">


A script to perform this addition would need to perform the following steps:

1. Clean up any strings before they're written to the database. For instance, the apostrophe in "Tom's Diner" would need be changed to "Tom''s Diner".
2. Build an Insert query.
3. Execute the query.
4. Retrieve the newly created index number if further processing is needed.

That process can be expressed in the following code:

Example 2-3-2
// 1. Clean up any strings.
$SongTitle = preg_replace("/\'/", "''", $SongTitle);
$SongTitle = "'" . $SongTitle . "'";

// 2. Build an Insert query.
$database_fields = "AlbumID, TrackNum, SongTitle";
$database_values = "$AlbumID, $TrackNum, $SongTitle";
$insert_query = "Insert Into Tracks ($database_fields) Values ($database_values)";

// 3. Execute the query.
$result = mysql_query($insert_query, $Conn);

// 4. Retrieve the index.
$TrackID = mysql_insert_id();
echo "The index of the new track is $TrackID.";


Vigilante simplifies the process and even processes the strings.

Example 2-3-3
$form_data = AssignFormInputToArray();
$TrackID = AddRecord("Tracks", $form_data, '', '');
echo "The index of the new track is $TrackID.";


In Example 2-3-3, the data from the form is converted into an associative array with field names serving as array keys. The AddRecord() function performs a number tasks:

1. It determines which fields are being written to the table by comparing database field names with array keys.
2. It makes note of which database fields contain string data and processes any strings to conform to SQL syntax.
3. It executes the query.
4. It returns the primary index of the new record.

Updating an existing record

The process by which Vigilante updates a database record is admittedly more involved than what a basic script would take to perform the same task. In the following example, a user wants to correct the spelling of a FooBar Fighters album.

Example 2-3-4
<input type="text" name="AlbumTitle" value="One By One">
<input type="text" name="ReleaseDate" value="1997-04-25 00:00:00">
<input type="hidden" name="AlbumID" value="15">
<input type="submit" value="Submit">


A script to perform this addition would need to perform the following steps:

1. Clean up any strings before they're written to the database.
2. Build an Update query.
3. Execute the query.

That process can be expressed in the following code:

Example 2-3-5
//1. Clean up any strings.
$AlbumTitle = preg_replace("/\'/", "''", $AlbumTitle);
$AlbumTitle = "'" . $Album Title . "'";

//2. Build an Update query.
$update_query = "Update Albums Set AlbumTitle=$AlbumTitle Where AlbumID=$AlbumID";

//3. Execute the query.
$result = mysql_query($update_query, $Conn);


If the user instead decides to update the release information as well, the script would need to accommodate that field and any others in the form.

Example 2-3-6
$AlbumTitle = preg_replace("/\'/", "''", $AlbumTitle);
$AlbumTitle = "'" . $Album Title . "'";
$ReleaseDate = "'" . $ReleaseDate . "'";

$update_query = '';
$update_query .= "Update Albums Set AlbumTitle=$AlbumTitle, ";
$update_query .= "ReleaseDate=$ReleaseDate ";
$update_query .= "Where AlbumID=$AlbumID";
$result = mysql_query($update_query, $Conn);


Vigilante takes a different approach by first comparing the existing database record with the form input, then building a query based only on changed values. (It will not even execute a query if the form values are not changed.) With this method, fields can be added or deleted from the web form with no effect on the scripting.

Example 2-3-7
//1. Convert the form data into an associative array.
$form_data = AssignFormInputToArray();

//2. Retrieve the database record to be updated.
$current_record = SelectRecord("Albums", "AlbumID=$AlbumID", "row");

//3. Update the record.
UpdateRecord($current_record, 0, $form_data, "Albums", "AlbumID=$AlbumID");


NOTE: The second argument of the UpdateRecord() function is the row number of the database result, which is required for some of PHP's MySQL functions. This value is usually set to 0 since only one record is ever retrieved from the database.

CAUTION: UpdateRecord is a single-record function. At the moment, Vigilante does not have a specific function to handle multiple-record updates, but another function, RunSelectQuery, can be used to perform any kind of database query, including updates and deletions.