POPULATING A FORM FROM A MULTI RECORD DATABASE, UPDATING RECORDS, CREATING NEW RECORDS - Jul 15th, 2020
|
Updated 1/28/19 mysql_insert and mysql_update code
To pre-populate forms so that information about a specific record can be viewed and updated if desired, you’ll need to
choose a field value that can uniquely identify that record. It can be an email address, the record number, or any other
easily input unique identifier.
This recipe uses the email address field of a Publicity Contacts database.
It also uses two forms.
A short, email address submission form, and a longer form that can display all the desired information in your record
and either updated or inserted into a new record.
For security purposes, access to this page should be restricted to appropriate individuals, but that’s covered
elsewhere in the cookbook.
First you’ll need to cycle through all of the existing records in your table and compare the submitted email address
with those found.
If a match is found, the main form needs to be populated with the existing information from the matching record, and a
procedure for updating that information begun.
If there is no match, and a new record is to be created, appropriate error checking for required fields needs to take
place before the record is created.
At each step, there need to be appropriate error messages to guide the user.
Here’s one approach to accomplishing this task:
At the top of the viewer, just after your normal list records calls, there are some blocks of code.
The code to pre-populate the form fields if a match is found after an email address is submitted.
<?php if (@$_REQUEST['save2'] ) :?>
<?php
$email_address = $_REQUEST['email_address'] ;
list($publicity_listingsRecords, $publicity_listingsMetaData) = getRecords(array(
'tableName' => 'publicity_listings',
'allowSearch' => false,
'where' => "email_address = '$email_address'",
));
?>
<?php foreach ($publicity_listingsRecords as $record):?>
<?php foreach ($record as $name => $value):?>
<?php if (array_key_exists($name, $_REQUEST)) { continue; }
$_REQUEST[$name] = $value;
?>
<?php endforeach; ?>
<?php endforeach; ?>
<?php endif ?>
The code to set a counter ($count) if a matching email addresses is found,
<?php $count= 0 ?>
<?php // check for matching email
$email = mysql_escape(@$_REQUEST['email_address']);
$count = mysql_count('publicity_listings', "email_address = '$email'");
?>
Based on that count, the code to determine if the next process will be to update an existing record or insert a new
record
<?php // Set a $show update variable to change the form submit name to “Save” if no duplicate emails are found?>
<?php if ($count == 1):?>
<?php $showupdate = 1 ?>
<?php else:?>
<?php $showupdate = 0 ?>
<?php endif ?>
the code to check for errors if a new record is to be created,
<?php
// process form
if (@$_REQUEST['save']) {
// error checking
$errorsAndAlerts = "";
if (!@$_REQUEST['first_name']) { $errorsAndAlerts .= "Please enter a first_name<br />\n"; }
if (!@$_REQUEST['last_name']) { $errorsAndAlerts .= "Please enter a last_name<br />\n"; }
// if (!@$_REQUEST['media_type']) { $errorsAndAlerts .= "Please select a media type<br />\n"; }
if (!@$_REQUEST['specialty_group']) { $errorsAndAlerts .= "Please select a specialty group<br />\n"; }
if (!@$_REQUEST['source']) { $errorsAndAlerts .= "Please select a source<br />\n"; }
if (!@$_REQUEST['email_address']) { $errorsAndAlerts .= "Please enter an email address<br />\n"; }
elseif(!isValidEmail(@$_REQUEST['email_address'])) { $errorsAndAlerts .= "Please enter a valid email
address(example: user@example.com)<br />\n"; }
// turn off strict mysql error checking for: STRICT_ALL_TABLES
mysqlStrictMode(false); // disable Mysql strict errors for when a field isn't defined below (can be caused when
fields are added later)
?>
The code to insert a new record into the database.
<?php
if ($count == 0 &&!$errorsAndAlerts ) {
$tableName = 'publcity_listings';
$colsToValues = array();
$colsToValues['createdDate='] = 'NOW()';
$colsToValues['updatedDate='] = 'NOW()';
$colsToValues['createdByUserNum'] = 0;
$colsToValues['updatedByUserNum'] = 0;
$colsToValues['department'] = $_REQUEST['department'];
$colsToValues['first_name'] = $_REQUEST['first_name'];
$colsToValues['last_name'] = $_REQUEST['last_name'];
$colsToValues['title'] = $_REQUEST['title'];
$colsToValues['email_address'] = $_REQUEST['email_address'];
$colsToValues['street_address'] = $_REQUEST['street_address'];
$colsToValues['city'] = $_REQUEST['city'];
$colsToValues['state'] = $_REQUEST['state'];
$colsToValues['zip'] = $_REQUEST['zip'];
$colsToValues['phone'] = $_REQUEST['phone'];
$colsToValues['cell'] = $_REQUEST['cell'];
$colsToValues['description'] = $_REQUEST['description'];
$userNum = mysql_insert($tableName, $colsToValues, true);
// On Success
$first_name = $_REQUEST['first_name'];
$last_name = $_REQUEST['last_name'] ;
$_REQUEST = array(); // clear form values
$errorsAndAlerts = "A record for $first_name $last_name has been successfully created.<br />You can now enter
another email address.";
}
?>
Some additional error checking code,
<?php
if ( @$count > 0 && @$_REQUEST['save2'] && @!$_REQUEST['go']) {
$errorsAndAlerts = "The email address $email already exists in the Publicity database.<br />Update the information below
as required, then re-submit the form.<br /><br />Remember, before you can update a contact's information, you must check
the check box above the 'Submit' button.";
}
if ( @$count == 0 && @$_REQUEST['save2'] ) {
$email = @$_REQUEST['email_address'];
$errorsAndAlerts = "The email $email is not currently in the Publicity database.<br /> Please fill out the form below
to create a new record for this contact.";
}
?>
And, the code to update an existing record,
<?php if (@$count > 0 && @$_REQUEST['go'] && @$_REQUEST['save2'] ): ?>
<?php
<?php $where = "email_address = '$email_address'"?>
<?php $email = @$_REQUEST['email_address'];
$tableName = 'publcity_listings';
$colsToValues = array();
$colsToValues['department'] = $_REQUEST['department'];
$colsToValues['first_name'] = $_REQUEST['first_name'];
$colsToValues['last_name'] = $_REQUEST['last_name'];
$colsToValues['title'] = $_REQUEST['title'];
$colsToValues['email_address'] = $_REQUEST['email_address'];
$colsToValues['street_address'] = $_REQUEST['street_address'];
$colsToValues['city'] = $_REQUEST['city'];
$colsToValues['state'] = $_REQUEST['state'];
$colsToValues['zip'] = $_REQUEST['zip'];
$colsToValues['phone'] = $_REQUEST['phone'];
$colsToValues['cell'] = $_REQUEST['cell'];
$colsToValues['description'] = $_REQUEST['description'];
mysql_update($tableName, null, $where, $colsToValues);
// on success
$first_name = @$_REQUEST['first_name'] ;
$last_name = @$_REQUEST['last_name'] ;
$_REQUEST = array(); // clear form values
$errorsAndAlerts = "The contact information for $first_name $last_name has been updated.<br />You can now enter
another email address.";
?>
<?php endif ?>
In the body of the viewer are some identifying information
<h1 class="body-text-white-bold-11">ADD/MODIFY PUBLICITY RECORDS</h1>
<span class="body-text-white-10">Enter as much information as you can.<br />
A </span><span class="body-text-bold-red-11">*</span> <span class="body-text-white-10">indicates a required
field.</span>
<!-- PUBLICITY RECORD FORM -->
<?php if (@$errorsAndAlerts): ?>
<div style="color: #C00; font-weight: bold; font-size: 14px; font-family: arial;"><br />
<?php echo $errorsAndAlerts; ?><br />
</div>
<?php endif ?>
<br />
<?php if (@$errorsAndAlerts ==""):?><br /><span class="body-text-white-11">Enter the contact's email address
here.<br />
If it exists, the form will be populated with the existing information and can be updated.<br />
If not, you can add a new record for this contact.</span><br /> <?php endif ?>
The email address submission form
<form method="post" action="?">
<input type="hidden" name="save2" value="1" />
<table border="0" cellspacing="10" cellpadding="12">
<tr>
<td class=" body-text-bold-white-10">Email Address</td>
<td><input class="form" type="text" name="email_address" value="<?php echo
htmlspecialchars(@$_REQUEST['email_address']); ?>" size="50" /></td>
</tr>
<tr>
<td colspan="2" align="center"><br />
<input class="button" type="submit" name="save2" value="Click To Enter Email >>" /></td>
</tr>
</table>
</form>
And the main information form (note the use of $showupdate variable to set the update confirmation check box and the
form name attributes for either updating or inserting records and the hidden email_address field so that the user is
forced to pre-populate existing records when email addresses match):
<form method="post" action="?">
<input type="hidden" <?php if ($showupdate == 0 ): ?>name="save"<?php else:?>name="save2"<?php endif ?>
value="1" />
<table border="0" cellspacing="10" cellpadding="12">
<tr>
<td colspan="2"><hr color="#b2b2b2" /></td>
</tr>
<tr>
<td colspan="2"><hr color="#b2b2b2" /></td>
</tr>
<tr>
<td colspan="2"><hr color="#b2b2b2" /></td>
</tr>
<tr>
<td class=" body-text-bold-white-10">Organization</td>
<td><input class="form" type="text" name="organization" value="<?php echo
htmlspecialchars(@$_REQUEST['organization']); ?>" size="50" /></td>
</tr>
<tr>
<td class=" body-text-bold-white-10">Department</td>
<td><input class="form" type="text" name="department" value="<?php echo
htmlspecialchars(@$_REQUEST['department']); ?>" size="50" /></td>
</tr>
<tr>
<td class=" body-text-bold-white-10"><span class="body-text-bold-red-11">*</span> First Name</td>
<td><input class="form" type="text" name="first_name" value="<?php echo
htmlspecialchars(@$_REQUEST['first_name']); ?>" size="50" /></td>
</tr>
<tr>
<td class=" body-text-bold-white-10"><span class="body-text-bold-red-11">*</span> Last Name</td>
<td><input class="form" type="text" name="last_name" value="<?php echo
htmlspecialchars(@$_REQUEST['last_name']); ?>" size="50" /></td>
</tr>
<tr>
<td class=" body-text-bold-white-10">Title</td>
<td><input class="form" type="text" name="title" value="<?php echo htmlspecialchars(@$_REQUEST['title']);
?>" size="50" /></td>
</tr>
<tr>
<td colspan="2"><hr color="#b2b2b2" />
<input type="hidden" class="form" name="email_address" value="<?php echo
htmlspecialchars(@$_REQUEST['email_address']); ?>" /></td>
</tr>
<tr>
<td class=" body-text-bold-white-10">Street Address</td>
<td><input class="form" type="text" name="street_address" value="<?php echo
htmlspecialchars(@$_REQUEST['street_address']); ?>" size="50" /></td>
</tr>
<tr>
<td class=" body-text-bold-white-10">City</td>
<td><input class="form" type="text" name="city" value="<?php echo htmlspecialchars(@$_REQUEST['city']); ?>"
size="50" /></td>
</tr>
<tr>
<td class=" body-text-bold-white-10">State</td>
<td><input class="form" type="text" name="state" value="<?php echo htmlspecialchars(@$_REQUEST['state']);
?>" size="50" /></td>
</tr>
<tr>
<td class=" body-text-bold-white-10">Zip Code</td>
<td><input class="form" type="text" name="zip" value="<?php echo htmlspecialchars(@$_REQUEST['zip']); ?>"
size="50" /></td>
</tr>
<tr>
<td colspan="2"><hr color="#b2b2b2" /></td>
</tr>
<tr>
<td class=" body-text-bold-white-10">Land Phone</td>
<td><input class="form" type="text" name="phone" value="<?php echo htmlspecialchars(@$_REQUEST['phone']);
?>" size="50" /></td>
</tr>
<tr>
<td class=" body-text-bold-white-10">Cell Phone</td>
<td><input class="form" type="text" name="cell" value="<?php echo htmlspecialchars(@$_REQUEST['cell']); ?>"
size="50" /></td>
</tr>
<tr>
<td colspan="2"><hr color="#b2b2b2" /></td>
</tr>
<tr>
<td class=" body-text-bold-white-10">Additional Information</td>
<!-- Note: The first preg_replace <br /> should have no slash and no spaces between the <br and the >, the second a <br
with no space before the slash>, the third with a space before the slash>) -->
<?PHP @$_REQUEST['description'] = preg_replace("[<br />]", "", @$_REQUEST['description'] ); // suppress in text area
?>
<?PHP @$_REQUEST['description'] = preg_replace("[<br />]", "", @$_REQUEST['description'] ); ?>
<?PHP @$_REQUEST['description'] = preg_replace("[<br />]", "", @$_REQUEST['description'] ); ?>
<td style="text-align:left"><textarea cols="50" rows="5" name="description" ><?php echo
@$_REQUEST['description']; ?></textarea></td>
</tr>
<tr>
<td colspan="2"><hr color="#b2b2b2" /></td>
</tr>
<?php if ($showupdate == 1 ): ?>
<tr>
<td colspan="2"><input type = "checkbox" id="go" name="go" value = "1" <?php checkedIf(1,
@$_REQUEST['go']);?> />
<span class=" body-text-bold-white-10">When you're ready to update, check this box and click
"Submit"</span></td>
</tr>
<?php endif ?>
<tr>
<td colspan="2" align="center"><br />
<input class="button" type="submit" <?php if ($showupdate == 0 ): ?>name="submit"<?php
else:?>name="submit2"<?php endif ?> value="Submit >>" /></td>
</tr>
</table>
</form>
|
The materials on this web site have been created for use with CMS Builder content management software. CMS Builder software is published and licensed for use by InteractiveTools.com. Please contact Interactive Tools for information on the downloading of the software or the purchasing of licenses.
|
