The FORM

The FORM. The FORM project is a full-stack project that creates an HTML form and sends data to a PHP script that inserts the form data into a database table.

You can open the completed version of the project in your browser here.

If you want to download completed versions of the scripts and files for The FORM, you can find them here.

The FORM Setup. Create a new directory for the form project inside the "htdocs" folder (inside MAMP or XAMPP). The htdocs directory is where your AMP setup (MAMP/XAMPP) expects to find URLs that run from localhost. I recommend you name it "n413_form". Copy the database connection script (n413connect.php) file from the the LIST project to the new folder or download it here.

The HTML Form. Begin by creating a new file in your n413_form directory named form_1.html. This will not be a PHP script, since no PHP is needed for this step. The HTML web page will hold a simple form for a name, an e-mail address, and a comment. Start with the <head> section. You can add CSS style sheets, etc, if you wish: 

<!DOCTYPE html>
<html>
	<head>
		<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
		<meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=yes">
		<title>Full Stack Amp Jam Form Project</title>
		<style>
			body	{ font-family:Arial; }
			h2	{ text-align:center;margin-top:50px; }
		</style>
	</head>

Add the body, headline, and form tags. The form tag should have attributes for method and action as shown below. Using POST for the method means the form data will not be sent as part of the URL (as it would be if we were to use the GET protocol). The action attribute should point to a PHP file we will create in your n413_form directory. The action URL will be: n413post.php 

<!DOCTYPE html>
<html>
	<head>
		<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
		<meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=yes">
		<title>Full Stack Amp Jam Form Project</title>
		<style>
			body	{ font-family:Arial; }
			h2	{ text-align:center;margin-top:50px; }
		</style>
	</head>       
	<body>
		<h2>Full Stack Amp Jam Form Project</h2>
		<form method="POST" action="n413post.php">
                
		</form>
	</body>
</html>

Now add the form body. Enclose the form elements in a <div> tag so you can format things. You need inputs for name and email, and a text area for comments. Also a submit button.

It is essential that the inputs have a "name" attribute, as this is what is used for the key in the name-value pairs sent in the POST operation. The names you use will be sent to your PHP script, so name them with the same names that will be used in the database table columns: "name", "email", and "comment".

Finally, add labels for the input fields and do any styling you want to add: 

<!DOCTYPE html>
<html>
	<head>
		<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
		<meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=yes">
		<title>Full Stack Amp Jam Form Project</title>
		<style>
			body	{ font-family:Arial; }
			h2	{ text-align:center;margin-top:50px; }
			#form-container { width:30%;margin-left:40%;margin-top:100px; }
			input	{ font-size:18px;margin-bottom:20px; }
			textarea{height:100px;width:300px;margin-bottom:30px;font-size:16px; }
			#submit	{float:right;}
		</style>
	</head>       
	<body>
		<h2>Full Stack Amp Jam Form Project</h2>
		<form method="POST" action="n413post.php">
			<div id="form-container">
				Name: <input type="text" id="name" name="name" value="" placeholder="Enter Name" required/><br/>
				E-mail: <input type="email" id="email" name="email" value="" placeholder="Enter E-mail" required/><br/>
				Comment: <textarea id="comment" name="comment" value="" placeholder="Add your comment here:"></textarea><br/>
				<input type="submit" id="submit" value="Submit" />
			</div>        
		</form>
	</body>
</html>

The form is now ready to go, but using the "Submit" button will give you a "404 Not Found" error, since we don't yet have the PHP script for the POST action. Before we write the PHP script, we need to create the database table for storing the data.

The Database Table. Create a new table in the ampjam_db database and name it "form_responses". Refer back to "The TABLE" if you need to. The new table needs 5 columns :

  • id, which should be Type INT and set up for AUTO-INCREMENT (A_I)
  • name, which should be VARCHAR, length 255
  • email, also VARCHAR, length 255
  • comment, which should be TEXT
  • timestamp, which should be Type TIMESTAMP

To add the 5th column, notice the top of the form, where it says "Add __1__ column(s)", with a "Go" button. Click the "Go" button and a new line will appear in the form.

Once you add the timestamp column, use the dropdown for the "Default" option and set it to CURRENT_TIMESTAMP, as you see in the screenshot. This will add a timestamp to the field anytime a record is created. Handy for documenting when your form was received.

Save the table and have a look at the "Structure" tab. Make any necessary edits or corrections. You don't need to add any records. We will do that once we connect the form with a PHP script.

You can download the SQL script for the form_responses table here.

The PHP Script. Create a new PHP file in your n413_form directory named n413post.php. The data will be sent to this script from the form when the submit button is clicked. There are a few things this script must do:

  • Make a database connection.
  • Get the data from the POST message and "sanitize" it.
  • Put the Post variables into a SQL query that does an "INSERT".
  • Determine whether the INSERT operation was successful.
  • Send an appropriate message back to the browser for the user.

The first step is to open a PHP tag and make a database connection. Do this by including the n413connect.php script you first placed in the n413_form directory: 

    <?php
        include("n413connect.php");
    
    ?>

You will now have a reference to your database connection in the variable $link.

Next, set up some PHP variables to store the form data. Name them $name, $email, and $comment, and set them to an empty string. Now, test to see if the the POST variables came from your form. The POST variables will be in a special "super-global" variable in PHP named $_POST. There is a similar super-global variable for $_GET and $_REQUEST. The $_REQUEST variable will contain both the GET and POST variables, and may be used instead of $_POST. These variables are associative arrays, with the "name" attribute from your form used for the keys. Use the PHP function isset() to see if they exist, and if they do, move the value into your new variables: 

    <?php
        include("n413connect.php");
        
        $name = "";
        $email = "";
        $comment = "";
        
        if(isset($_POST["name"])) { $name = $_POST["name"]; }
    	if(isset($_POST["email"])) { $email = $_POST["email"]; }
        if(isset($_POST["comment"])) { $comment = $_POST["comment"]; }
        
    ?>

Sanitizing. Pulling in user-supplied data always involves a risk that a bad actor might try to cause problems by sending data that could damage your database or compromise your server. Any user-supplied data should be considered untrusted data. So we need to take a few steps to mitigate the danger.

This step is known as "sanitizing" the data, and the most important step is to use a mysqli function known as mysqli_real_escape_string(). This prevents "SQL injection", where values that you expect to be names or email addresses actually contain SQL commands. mysqli_real_escape_string() escapes the quotes and other syntax elements so they can't be interpreted as SQL syntax within the query.

Other sanitizing steps include removing html encoding and the syntax for "Javascript injection", which can hijack the user's browser when data from your database is read back into another web page. We will use these sanitizing steps:

  • Decode HTML entities (representing HTML markup with "&xx;" syntax). This puts the data in plain character representation. We will use PHP's html_entity_decode() function for this.
  • Trim any leading or trailing spaces. The trim() function will do this.
  • Strip away any slashes that would escape special characters. stripslashes() does this.
  • Strip away tags that would contain HTML markup. strip_tags() will handle this.
  • Process the data through mysqli_real_escape_string() to escape any special characters the safe way.

This is a lot of code to run for each variable we need to process, so it would be best to put this code into a PHP function so we can write it one time and run it as often as needed. Writing PHP functions is very similar to writing functions for any other language, with a few special considerations.

PHP is very strict about variable scope, so any variables we need must either be passed in as arguments, or declared as "globals" by the global statement. The mysqli_real_escape_string() function needs a reference to the database connection, so we will need to declare $link as a global. Place the new sanitize() function after the include statement so we have a good value for $link. We can now assign the POST values to the new variables by passing them through the sanitize() function (below): 

    <?php
        include("n413connect.php");
        
        function sanitize($item){
            global $link;
            $item = html_entity_decode($item);
            $item = trim($item);
            $item = stripslashes($item);
            $item = strip_tags($item);
            $item = mysqli_real_escape_string( $link, $item );
            return $item;
        }
        
        $name = "";
        $email = "";
        $comment = "";
        
        if(isset($_POST["name"])) { $name = sanitize($_POST["name"]); }
    	if(isset($_POST["email"])) { $email = sanitize($_POST["email"]); }
        if(isset($_POST["comment"])) { $comment = sanitize($_POST["comment"]); }
        
    ?>

INSERT query.We are now ready to write an SQL query to insert the data into the form_responses table. We do this with an INSERT query. The INSERT query requires a list of all the table columns we are using, and a second list of values (in order) that should be used for each column. This is tedious to do, and it is easy to make mistakes. Fortunately, phpMyAdmin provides a nice shortcut. Use the phpMyAdmin form to insert a fake record:

When you click "Go", phpMyAdmin will display the INSERT query it generates from the Insert form, complete with backticks. You can copy this query and paste it into your PHP script, replacing the fake value data with your PHP variables. (Be sure to delete these fake entries if you don't want fake data in your table.)

So, borrowing phpMyAdmin's beautiful INSERT query, we can write a query and send it to MySQL: 

    <?php
        include("n413connect.php");
        
        function sanitize($item){
            global $link;
            $item = html_entity_decode($item);
            $item = trim($item);
            $item = stripslashes($item);
            $item = strip_tags($item);
            $item = mysqli_real_escape_string( $link, $item );
            return $item;
        }
        
        $name = "";
        $email = "";
        $comment = "";
        
        if(isset($_POST["name"])) { $name = sanitize($_POST["name"]); }
    	if(isset($_POST["email"])) { $email = sanitize($_POST["email"]); }
        if(isset($_POST["comment"])) { $comment = sanitize($_POST["comment"]); }
        
        $sql = "INSERT INTO `form_responses` (`id`, `name`, `email`, `comment`, `timestamp`) 
        	VALUES (NULL, '".$name."', '".$email."', '".$comment."', CURRENT_TIMESTAMP)";
        $result = mysqli_query($link, $sql);
        
    ?>

The modifications to the original query were:

  • The entire query was enclosed in double quotes
  • The strings for the values were deleted (leaving the single quotes), and double quotes were dropped in to close the quoted sections of the query string.
  • PHP dot operators were dropped into the gaps to add the PHP variables. i.e., 'John Smith' became '".$name."'
  • The semi-colon at the end of the original query was deleted, but the semi-colon to end the PHP line of code was added outside the double quote for the query string. i.e., the last part of the query went from CURRENT_TIMESTAMP); to CURRENT_TIMESTAMP)";

Managing the single/double quotes is tricky, and there are some syntax shortcuts to cut down on the amount of quoting, but this method keeps the string stucture explicitly clear. One mental trick for memorizing the opening sequence for a variable insertion is: "single quote - double quote - dot - dollar sign". The back side is the reverse, but without the dollar sign.

Once the query string is defined, it is sent to MySQL with mysqli_query() and the record is added to the database. The only thing left for the script to do is figure out whether everything worked and notify the user. In the LIST project, the $result value that was returned from mysqli_query() contained the data we asked for. In the case of an INSERT query, $result will contain true or false, depending on whether the INSERT was successful. So we can use this logic to know what to tell the user: 

    <?php
        include("n413connect.php");
        
        function sanitize($item){
            global $link;
            $item = html_entity_decode($item);
            $item = trim($item);
            $item = stripslashes($item);
            $item = strip_tags($item);
            $item = mysqli_real_escape_string( $link, $item );
            return $item;
        }
        
        $name = "";
        $email = "";
        $comment = "";
        
        if(isset($_POST["name"])) { $name = sanitize($_POST["name"]); }
    	if(isset($_POST["email"])) { $email = sanitize($_POST["email"]); }
        if(isset($_POST["comment"])) { $comment = sanitize($_POST["comment"]); }
        
        $sql = "INSERT INTO `form_responses` (`id`, `name`, `email`, `comment`, `timestamp`) 
        	VALUES (NULL, '".$name."', '".$email."', '".$comment."', CURRENT_TIMESTAMP)";
        $result = mysqli_query($link, $sql);
        
    ?>
    
    <!DOCTYPE html>
    <html>
	<head>
		<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
		<meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=yes">
		<title>Full Stack Amp Jam Form Project</title>
		<style>
			body	{ font-family:Arial; }
			h2	{ text-align:center;margin-top:50px; }
			#message-container { width:30%;margin-left:40%;margin-top:100px; }
			.signoff{ float:right;font-style:italic;margin-top:30px; }
		</style>
	</head>       
	<body>
		<h2>Full Stack Amp Jam Form Project</h2>
		<div id="message-container">
		<?php
			if($result){
			    echo '<p>Thank you for submitting your comment. <br/>';
			}else{
			    echo '<p>Sorry, but something went wrong.  Please try again later. <br/>';
			}
		?>
		    <span class="signoff">The Web Site Team</span></p>
		</div>
	</body>
</html>

Completed Project. You can open the completed version of the project in your browser here.

Here are completed versions of the scripts used for this project: 

(n413connect.php)

<?php
        $dbhost = 'localhost:8889'; //XAMPP is 'localhost:3306'
        $dbuser = 'root';
        $dbpwd  = 'root'; //XAMPP password is ''
        $dbname = 'ampjam_db';
        $link = mysqli_connect($dbhost, $dbuser, $dbpwd, $dbname);
        if (!$link) { die('Connect Error (' . mysqli_connect_errno() . ') '. mysqli_connect_error()); }
    ?>
(form_1.html)

<!DOCTYPE html>
<html>
	<head>
		<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
		<meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=yes">
		<title>Full Stack Amp Jam Form Project</title>
		<style>
			body	{ font-family:Arial; }
			h2	{ text-align:center;margin-top:50px; }
			#form-container { width:30%;margin-left:40%;margin-top:100px; }
			input	{ font-size:18px;margin-bottom:20px; }
			textarea{height:100px;width:300px;margin-bottom:30px;font-size:16px; }
			#submit	{float:right;}
		</style>
	</head>       
	<body>
		<h2>Full Stack Amp Jam Form Project</h2>
		<form method="POST" action="n413post.php">
			<div id="form-container">
				Name: <input type="text" id="name" name="name" value="" placeholder="Enter Name" required/><br/>
				E-mail: <input type="email" id="email" name="email" value="" placeholder="Enter E-mail" required/><br/>
				Comment: <textarea id="comment" name="comment" value="" placeholder="Add your comment here:"></textarea><br/>
				<input type="submit" id="submit" value="Submit" />
			</div>        
		</form>
	</body>
</html>
(form_responses.sql)

-- phpMyAdmin SQL Dump
-- version 4.8.3
-- https://www.phpmyadmin.net/
--
-- Host: localhost:8889
-- Generation Time: Apr 23, 2020 at 05:07 PM
-- Server version: 5.7.23
-- PHP Version: 7.2.10

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `ampjam_db`
--

-- --------------------------------------------------------

--
-- Table structure for table `form_responses`
--

CREATE TABLE `form_responses` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `comment` text NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `form_responses`
--

INSERT INTO `form_responses` (`id`, `name`, `email`, `comment`, `timestamp`) VALUES
(1, 'Oscar Wilde', 'oscar@wilde.com', 'Be yourself; everyone else is already taken.', '2020-04-19 18:05:09'),
(2, 'Albert Einstein', 'al@emc2.com', 'Two things are infinite: the universe and human stupidity; and I\'m not sure about the universe.', '2020-04-19 18:05:53'),
(3, 'Frank Zappa', 'z@eatyellowsnow.com', 'So many books, so little time.', '2020-04-19 18:07:22'),
(4, 'Mark Twain', 'mark@twain.com', 'If you tell the truth, you don\'t have to remember anything.', '2020-04-19 18:09:06'),
(5, 'John Lennon', 'John@fabfour.com', 'Life is what happens when you\'re busy making other plans.', '2020-04-19 18:11:45'),
(6, 'Dr. Strangelove', 'strange@love.com', 'Gentlemen, you can\'t fight in here! This is the war room!', '2020-04-19 18:31:42'),
(7, 'Groot', 'groot@guardians.com', 'I am Groot.', '2020-04-19 18:32:35');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `form_responses`
--
ALTER TABLE `form_responses`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `form_responses`
--
ALTER TABLE `form_responses`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
(n413post.php)

<?php
        include("n413connect.php");
        
        function sanitize($item){
            global $link;
            $item = html_entity_decode($item);
            $item = trim($item);
            $item = stripslashes($item);
            $item = strip_tags($item);
            $item = mysqli_real_escape_string( $link, $item );
            return $item;
        }
        
        $name = "";
        $email = "";
        $comment = "";
        
        if(isset($_POST["name"])) { $name = sanitize($_POST["name"]); }
    	if(isset($_POST["email"])) { $email = sanitize($_POST["email"]); }
        if(isset($_POST["comment"])) { $comment = sanitize($_POST["comment"]); }
        
        $sql = "INSERT INTO `form_responses` (`id`, `name`, `email`, `comment`, `timestamp`) 
        	VALUES (NULL, '".$name."', '".$email."', '".$comment."', CURRENT_TIMESTAMP)";
        $result = mysqli_query($link, $sql);
        
    ?>
    
    <!DOCTYPE html>
    <html>
	<head>
		<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
		<meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=yes">
		<title>Full Stack Amp Jam Form Project</title>
		<style>
			body	{ font-family:Arial; }
			h2	{ text-align:center;margin-top:50px; }
			#message-container { width:30%;margin-left:40%;margin-top:100px; }
			.signoff{ float:right;font-style:italic;margin-top:30px; }
		</style>
	</head>       
	<body>
		<h2>Full Stack Amp Jam Form Project</h2>
		<div id="message-container">
		<?php
			if($result){
			    echo '<p>Thank you for submitting your comment. <br/>';
			}else{
			    echo '<p>Sorry, but something went wrong.  Please try again later. <br/>';
			}
		?>
		    <span class="signoff">The Web Site Team</span></p>
		</div>
	</body>
</html>

If you want to download completed versions of the PHP scripts and image files, you can find them here.

If you are ready, lets pull what you've done so far into The SITE!