Code-day #1: Inserting form data into a database

Recently I was asked "Do you code?" I said, "Absolutely, all the time" and his response was "Wow, I wish I knew how to code." I explained to him a good starting point is Codecademy, but it got me thinking that I could perhaps help some people learn the basics or even more advanced coding. That's the basis of the weekly "Code-day" series.

Today we will learn how to take user input from an HTML form and insert that into a database. We will also learn how it can all be done in one file. As a bonus we will learn how to validate it, sanitize it, and how to do it via AJAX.

This demo is what we will be building. I won't be worrying about styling.


HTML

First we'll create the file 'index.php' -- This is because we will need to run PHP code later.

Inside this file we will start off with the simple HTML structure with an HTML form inside.

<!DOCTYPE html>
 <html>
    <head>
        <meta charset="UTF-8">
    </head>
    <body>
        <form class="form" action="" method="POST">
            First name: <input type="text" class="fname" name="firstname" value=""><br>
            Last name: <input type="text" class="lname" name="lastname" value=""><br><br>
            <input type="submit" value="Submit">
        </form>
    </body>
 </html>

It's always a good idea to define the charset especially when it pertains to inserting data into a database. By default MySQL DBs are set to latin and HTML is set to UTF-8 in HTML5, however it was ISO-8859-1 by default for HTML4. You can see we are using the DOCTYPE html, which is HTML5, but old habits die hard.

We will be using POST method to send data to the server, but there is also a GET method. POST should always be used for form submission, GET should be used for data retrieval. This is because POST is more secure at sending sensitive data, whereas GET will append the data to the request URL (e.g. http://yoursite.com/index.php?firstname=value1&lastname=value2)

So now we should have something that looks like this:

html form

Database

If you don't have a web hosting provider then I recommend downloading LAMP/MAMP/WAMP. If you do then they should have phpMyAdmin as well as some CPanel app to help you create the User and DB. Remember your DB username and password. We will need it later

Let's create a new DB named 'test' and choose the collation 'utf8_general_ci' this will match our HTML charset
phpmyadmin db create

Now lets create the table 'users' with 3 columns.
phpmyadmin table create

Column #1 is 'id' which is our primary key. It will be an INTEGER type with a 5 digit length (allowing 99,999 entries) which is auto incrementing making it an always unique number. Column #2 will be 'fname', the first name input from the HTML form. Lastly column #3 will be 'lname', the last name input. Both of these will be the VARCHAR type with a length of 20, just in case. You can add comments in the specified sections to help you remember if needed.
phpmyadmin table create details


PHP

Now that the DB is setup we can get back to coding. We will now create the code that allows us to pass the form data into the database.

First we want to check and see if the server is sending a POST request. '//' denotes comments to help better understand the code.

<?php
//Check and see if POST request is being sent
 if ($_SERVER['REQUEST_METHOD'] == 'POST')
 {
 }
?>

Now we want to insert the post request data into variables for easier use. 'firstname' and 'lastname' refer to the cooresponding form input names

<?php
//Check and see if POST request is being sent
 if ($_SERVER['REQUEST_METHOD'] == 'POST')
{ 
    //Capture POST array values and put them into variables
    $fname = $_POST['firstname'];
    $lname = $_POST['lastname'];
}
?>

Simple enough so far right? Now let's connect the database. The sequence for connecting a DB is URL, DB Username, DB Password, and DB Name. Remember we set our DB to be named 'test'.

<?php
//Check and see if POST request is being sent
 if ($_SERVER['REQUEST_METHOD'] == 'POST')
{ 
    //Connect to DB. If error occurs stop and echo error
    $conn = new mysqli("localhost", "username", "password", "test");
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }

    //Capture POST array values and put them into variables
    $fname = $_POST['firstname'];
    $lname = $_POST['lastname'];

    //Close DB connection
    $conn->close();
}
?>

Okay now let's insert the data into the database.

<?php
//Check and see if POST request is being sent
 if ($_SERVER['REQUEST_METHOD'] == 'POST')
{ 
    //Connect to DB. If error occurs stop and echo error
    $conn = new mysqli("localhost", "username", "password", "test");
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }

    //Capture POST array values and put them into variables
    $fname = $_POST['firstname'];
    $lname = $_POST['lastname'];

    //Setup SQL statement
    $sql = "INSERT INTO users (fname, lname) VALUES ('".$fname."', '".$lname."')";

    //If query executes successfully echo success statement, otherwise echo error
    if ($conn->query($sql) === TRUE) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }

    //Close DB connection
    $conn->close();
}
?>

Alright! That will take the form data and insert it into the DB. We're done, right? Nope... If we were to put this on a live site it would be exploit heaven. You could easily do a SQL injection attack by simply putting );DROP TABLE users; as their first or last name input. That little line would delete our entire users database.


The Right Way

We can do a few things to fix this. First let's change the MySQLi code to PDO format. This is far more secure due to its usage of prepared statements. This will remove the threat of a SQL injection attack. Also it supports far more databases making your code much more compatible. The MySQLi version is much easier to follow logically and to read for beginners, which is why we started with that.

<?php

    //Check and see if POST request is being sent
     if ($_SERVER['REQUEST_METHOD'] == 'POST')
    { 
        //Connect to DB. If error occurs stop and echo error
        $conn = new PDO("mysql:host=localhost;dbname=test", 'username', 'password');

        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        }

        //Setup PDO binds
        $params = array(':fname' => $_POST["firstname"], ':lname' => $_POST["lastname"]);

        //Prepare statement
        $pdo = $conn->prepare('INSERT INTO users (fname, lname) VALUES (:fname, :lname)');

        //Execute statement, if an error echo error. 
        if ($pdo->execute($params)){
            echo "Success!";
        } else {
            echo "Failure!";
        }

        //Close DB connection
        $conn = null;
    }
?>

But we're not done yet. We still have to validate and sanitize the user input.

<?php
    //Check and see if POST request is being sent
     if ($_SERVER['REQUEST_METHOD'] == 'POST')
    { 
        //Function to sanitize user input
        function sanitize($data) {
            $data = trim($data);
            $data = stripslashes($data); 
            $data = htmlspecialchars($data);
            return $data;
        }

        //Validate and make sure everything was filled out
        //If firstname is empty notify user, otherwise sanitize input
        if (empty($_POST["firstname"])) {
            echo "First name is required";
        } else {
            $fname = sanitize($_POST["firstname"]);
        }

        //If lastname is empty notify user, otherwise sanitize input
        if (empty($_POST["lastname"])) {
            echo "Last name is required";
        } else {
            $lname = sanitize($_POST["lastname"]);
        }

        //Connect to DB. If error occurs stop and echo error
        $conn = new PDO("mysql:host=localhost;dbname=test", 'username', 'password');
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        }

        //Setup PDO binds
        $params = array(':fname' => $fname, ':lname' => $lname);

        //Prepare statement
        $pdo = $conn->prepare('INSERT INTO users (fname, lname) VALUES (:fname, :lname)');

        //Execute statement, if an error echo error. 
        if ($pdo->execute($params)){
            echo "Success!";
        } else {
            echo "Failure!";
        }

        //Close DB connection
        $conn = null;
    }
?>

Here we setup a function named sanitize() to use three convienent PHP functions trim(), stripslashes(), and htmlspecialchars() to santize the user input. We also setup validation methods using PHP to check and see that the form was filled out. If not then we show a message to the user using echo.


Put it together

<?php
    //Check and see if POST request is being sent
     if ($_SERVER['REQUEST_METHOD'] == 'POST')
    { 
        //Function to sanitize user input
        function sanitize($data) {
            $data = trim($data);
            $data = stripslashes($data); 
            $data = htmlspecialchars($data);
            return $data;
        }

        //Validate and make sure everything was filled out
        //If firstname is empty notify user, otherwise sanitize input
        if (empty($_POST["firstname"])) {
            echo "First name is required";
        } else {
            $fname = sanitize($_POST["firstname"]);
        }

        //If lastname is empty notify user, otherwise sanitize input
        if (empty($_POST["lastname"])) {
            echo "Last name is required";
        } else {
            $lname = sanitize($_POST["lastname"]);
        }

        //Connect to DB. If error occurs stop and echo error
        $conn = new PDO("mysql:host=localhost;dbname=test", 'username', 'password');
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        }

        //Setup PDO binds
        $params = array(':fname' => $fname, ':lname' => $lname);

        //Prepare statement
        $pdo = $conn->prepare('INSERT INTO users (fname, lname) VALUES (:fname, :lname)');

        //Execute statement, if an error echo error. 
        if ($pdo->execute($params)){
            echo "Success!";
        } else {
            echo "Failure!";
        }

        //Close DB connection
        $conn = null;
    }
?>

<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
    </head>
    <body>
        <form class="form" action="" method="POST">
            First name: <input type="text" class="fname" name="firstname" value=""><br>
            Last name: <input type="text" class="lname" name="lastname" value=""><br><br>
            <input type="submit" value="Submit">
        </form>
    </body>
</html>

Easy! This is a perfectly fine stopping point. We've accomplished our main goal. You should now understand how to set up an HTML form, how to connect to a database, and how to insert data into a database the secure way. Not only that but all in one file no less! I do have one more thing to show you if you want to stick around...


Incorporating AJAX

AJAX will allow this us enhance the user experience of this page by performing the request, and therefore the data processing, in the background without refreshing the page. This is wholly unnecessary for this demo, but it's nice to know.

<?php
    //Check and see if POST request is being sent
     if ($_SERVER['REQUEST_METHOD'] == 'POST')
    { 
        //Function to sanitize user input
        function sanitize($data) {
            $data = trim($data);
            $data = stripslashes($data); 
            $data = htmlspecialchars($data);
            return $data;
        }

        //Validate and make sure everything was filled out
        if (empty($_POST["firstname"])) {
            echo "First name is required";
        } else {
            $fname = sanitize($_POST["firstname"]);
        }

        if (empty($_POST["lastname"])) {
            echo "Last name is required";
        } else {
            $lname = sanitize($_POST["lastname"]);
        }

        //Connect to DB. If error occurs stop and echo error
        $conn = new PDO("mysql:host=localhost;dbname=test", 'username', 'password');
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        }

        //Setup PDO binds
        $params = array(':fname' => $fname, ':lname' => $lname);

        //Prepare statement
        $pdo = $conn->prepare('INSERT INTO users (fname, lname) VALUES (:fname, :lname)');

        //Execute statement, if an error echo error. 
        if ($pdo->execute($params)){
            echo "Success!";
        } else {
            echo "Failure!";
        }

        //Close DB connection
        $conn = null;

    }
?>

<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <script src="//code.jquery.com/jquery-1.11.2.min.js"></script>
    </head>
    <body>
        <form class="form" action="" method="POST">
            First name: <input type="text" class="fname" name="firstname" value=""><br>
            Last name: <input type="text" class="lname" name="lastname" value=""><br><br>
            <input type="submit" value="Submit">
        </form>

        <script>
        $(document).on('submit', '.form', function(e) {
            $.ajax({
                url: "index.php",
                type: "POST",
                data: $('.form').serialize(),
                success: function(data) {
                    $('body').html(data);
                }
            });
            e.preventDefault();
        });
        </script>
    </body>
</html>

What you'll notice is we added quite a few things. First off we included JQuery in the head of our HTML. This is so we can use the Ajax function. Next we added the ajax code to the bottom. This will run whenever the submit button is clicked. It will then send a POST request to this same page with the form data. Next is a little trick, because this is a single page it's difficult to get proper error handling from the PHP portion. Typically this is pulled from a separate file, but the data response in our case is the entire page code over again, so what I did was allowed the body to be overwritten with the html from the response. This allows us to get the necessary error messages from the PHP portion and it's difficult to notice. Also the e.precentDefault() is an event that will prevent the document from doing the default action, in this case refresh the page.

That's it! Good luck out there!

If you have any questions, comments, or suggestions please feel free to post them in the comments.