Preloading Forms /Update

One of the many tasks on our website is to allow users to input data, and to update their records on file. We will gather form data from the user, and update the attached database with an SQL update statement. Generally, we will use the unique ID number of the user, or any other data, to identify which record to update.

It is easy to imagine allowing a user to enter data, and clicking the submit button. This action sends the form data (usually via the POST method) to a "form handler" page, that gathers the data, and enters it into the database:

$myFirst = $_POST['FirstName']); //Grab the First Name typed in by the user, sent via the POST method
$myID = $_POST['CustomerID']); //Grab the Customer's unique ID, sent via a hidden form field
//Create the SQL string
$updateSQL = "UPDATE tblCustomers SET FirstName = '" . $FirstName . "' WHERE CustomerID = " . $myID;

One of the things we forget about is the fact that once the user has entered data, he expects it to still be there! For instance, when a user comes back to update his data, he wants his data "pre-loaded" into the form. If the user gets an empty form every time he wants to update data, he will think the data never went into the database correctly in the first place.

Preloading A Form

To preload, or prepopulate a form, we need to enter data into the "value" attribute of the form element. This works great for textbox, password, textarea and hidden form elements. You will need to do some special handling for checkboxes, radio buttons and select options which we will cover later. Here is an example of a preloaded textbox:

$FirstName = $row['FirstName']; //This is data coming from a database select statement
<!--Below is a line of HTML with a print PHP statement preloading the value of $FirstName-->
Enter Your First Name: <input type="text" name="FirstName" value="<?print $FirstName; ?>" / >

showFirst Example

The following example are 3 files that work together to demonstrate the fundamentals of showing and updating data via a web page.  The first page "showFirst.php" shows a user all of the first names of users in a customer table, such as the one we built to demonstrate MySQL.

The second page "preloadFirst.php" loads the value into a form that allows a user to insert new data.  The ID of the user is carried along with the page in a hidden form field.

The third file is not really a page, "updateFirst.php" has no visual component.  It exists to update the data, and then pass the user back to the first page:

Processing Raw Data

As we become more serious about handling our data, we need to process it on it's way in and out of the database. SQL statements use the single quote as a special character, so just like PHP is sensitive to the double quote, we need to do some special processing to handle the single quotes on the way into the database:

$myFirst = addslashes(strip_tags(trim($_POST['FirstName'])));

The above line of code is a more realistic version of processing data that has come across on a form POST, and is intended for entry into the database. The above line includes 3 distinct functions, processed in a specific order. The order of operations in functions works from outside in, just like in math. The innermost function, "trim", trims extra spaces on the outside of the data. The next function, "strip_tags", removes any HTML or code recognized by PHP as harmful to the database or the server. The third function "addslashes" passes an escape character (a forward slash) prior to every single quote. This allows MySQL to store the data without trying to process a SQL string based on a word with a single quote, like "O'Reilly".

We also need to process the data coming out of the database. Just as we added the slash on the way in, we will now need to remove it on the way out:

$FirstName = stripslashes(trim($row['FirstName']));

Here we still trim the spaces that may contaminate our data on the way out of the database, but we also use the function "stripslashes" to remove any slashes added in place of the single quotes we may have added to the data on the way in!

Preparing Our Table to Update

When we built our demo "firstTable", we hit a database table and printed all the fields and records present. This is a useful start on building an administrative tool for updating the database. "firstTable" already sniffs the names of the fields and enters a row of the field names, then populates an HTML table beneath it that displays the data:

Since we want to use the unique CustomerID to be able to identify the customer's record, we want to build a new page, called "preload.php" that will require the CustomerID to be passed into it, this time via the GET method. We only need to pass in the CustomerID, because that is all we need to add to be able to get all the data to preload the customer's form:

myID = strip_tags(trim($_GET['id'])); //strip hacker bits, trim spaces
if(!is_numeric($myID)){myRedirect("index.php");} //if not a number, redirect

While we are "GET"ing the ID passed in via the Querystring, we are also processing the data, and checking to see if it is truly a number, via the "is_numeric" function. If it is not, we are politely sending the user to a different page, via a custom form called "myRedirect" which is covered later on this page. Below is the basic syntax for sending the CustomerID of Steve Jones in the table above, with a CustomerID of 17 into a page called "preload.php":

preload.php?id=17

If we could somehow dynamically build a page that builds an HTML link with this data in it automatically, we could target the preload.php page for any given CustomerID. That is exactly how we will adapt our "firstTable". We will add a very specific line of PHP code that will build a link targeting "preload.php" as we build our table:

//create update link for primary key
print "<td><a href=preload.php?id=" . $row['CustomerID'] . ">" . $row['CustomerID'] . "</a></td>";

Now we have added a "link" to our table that will pass the data on to the "preload.php" page, and allow us to update the record:

When the CustomerID number is clicked, the user is directed to the preLoad page for that particular customer. These 2 pages now need to work together, and also involve a third page, "preloadUpdate.php", which receives the form data as entered by the user, updates the database, then sends the user back to the original firstTable page, once the data has been updated. Below is the custom function that is called at the end of the process to send the user back to the original page:

function myRedirect($myURL)
{ //passes user to relative URL
header("Location: http://" . $_SERVER['HTTP_HOST'] . dirname($_SERVER['PHP_SELF']) . "/" . $myURL);
}

This special function is needed in PHP because the default behavior of the "header" function allows us to send a user to a different page, but requires absolute referencing. This function "sniffs" where the page currently is, and adds the absolute referencing data to the address so you don't have to.

Adding and Deleting Data

We can use the "firstTable" as a model to be able to add and delete records from the database as well. To add a record, we use the same "preLoad" and "preLoadUpdate" pages as the foundation, but pass in a special variable if we are adding a record, instead of updating a record. This informs both subsequent pages to be aware that this is an INSERT to the database, since the syntax is completely different from an UPDATE statement. We will place an "Add Record" link on our table, which will send the special querystring value to the preLoad page:

preLoad.php?nw=y

If the querystring value for "nw" (new) is "y" (for yes) then there will be no values preloaded into the form. The empty variables will preload as empty strings, allowing the user to enter data into the textboxes for the first time. When the form is submitted, the info is passed on to the new version of preLoadUpdate.php, via a hidden form field:

<input type="hidden" name="nw" value="y" />

Without this information being passed on to the "preLoadUpdate.php" page, there would be no way for the page to know the difference between an update and an insert. Now that the page sees the special hidden form field, it prepares to insert a new record, instead of looking for an old record to update.

Deleting Data

We can use a form built into the firstTable page to target back to itself for the purpose of deleting records. This is similar to our "postback" pages, and limits our number of pages to 3 to perform full functionality. The mechanism for deleting the record uses a radio button to select the item. In our example, there is even a bit of JavaScript to warn you before you delete a record.

Below is the working example of firstTable3.php, which allows you to add, update & delete records. Please don't delete all the records, so the demo maintains it's usefulness.

nmEdit

While we are building our pages, it would be nice if we had a single fle that allowed us to access and edit data in all our tables. While we have this capability in applications like phpMyAdmin, they are not trivial to install, and not simple for quick and easy access.

The following file can be used by you to view, edit, update and delete the data in all the tables in your DB. It is a work in progress: