PHP, AJAX, MySQL

Inserting Form Data Into MySQL Using PHP and AJAX

Posted in Web Development on February 2017

How do you store information from a PHP webform into a MYSQL database? It’s rather simple but, if you’ve never created one before, it can be daunting. I’ve encountered a lot of poorly produced videos, whether video quality or voice, and overly complicated tutorials. The hope for this post is to help those who’ve encountered similar issues and hopefully make learning about PHP and MySQL easier.

I’ll be using a local server environment called MAMP—it works for MAC and Windows. Because I’m developing locally, and needing PHP, Apache, and a database, MAMP encompasses everything I need. It’s free and easy to use. First open MAMP or whatever LAMP software you choose. If your web host offers MySQL, it probably offers phpMyAdmin, which is a web interface to manage your database. If you have cPanel, phpMyAdmin is probably located there. Navigate to phpMyAdmin within “Tools”. You’ll need to create a new database called “tutorial”, since this is a tutorial. This will be a simple form to gather users’ name and email, so the table name will be called “person”. Remember, keep naming conventions consistent. You’ll find people creating uppercase or camel case, but traditionally most stick to lowercase. Once your table is setup, you’ll need to create four columns, as follows:

id INT(11) AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(60) NOT NULL,
email VARCHAR(60) NOT NULL,
time TIMESTAMP

MySQL database

Everything should be straightforward, except for “id”. It needs to be auto incremented so you know who submitted chronologically. Meaning, if John was the very first submission and David was the second, you would see John as number 1 and David as number 2. Also, don’t allow white spaces. You’ll want to use underscore for things like full_name. Set the length/value for “name” and “email” to 60. We’ll also have a timestamp, so you can see when a user submitted. I would highly advise adding this field no matter what because if you add it later, after there’s been submissions in the database, those submissions will automatically have the date when you added the new field.

The second and third portions, HTML and PHP, are rather self-explanatory. If not, I’ve added inline documentation to better explain. If you’re using MAMP, your files will need to be placed within MAMP’s htdocs folder.

index.html

<html>
    <head>
        <title>Inserting Form Data Into MySQL Using PHP and AJAX</title>
        <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
        <!--AJAX-->
        <script>
            $(document).ready(function() {
                <!--#my-form grabs the form id-->
                $("#my-form").submit(function(e) {
                    e.preventDefault();
                    $.ajax( {
                        <!--insert.php calls the PHP file-->
                        url: "insert.php",
                        method: "post",
                        data: $("form").serialize(),
                        dataType: "text",
                        success: function(strMessage) {
                            $("#message").text(strMessage);
                            $("#my-form")[0].reset();
                        }
                    });
                });
            });
        </script>
    </head>
    <body>
        <!--The "message" id will be display via PHP and AJAX--> 
        <p id="message"></p>     
        <form id="my-form" action="" method="post">
            <input type="text" name="name" placeholder="Name" required>
            <input type="email" name="email" placeholder="Email" required>
            <button  type="submit">Insert</button>
        </form>
    </body>
</html>

insert.php

<?php

    //Connection to MySQL
    $con = mysqli_connect('localhost', 'root', 'root');

    if(!$con) {
        die('Not Connected To Server');
    }

    //Connection to database
    if(!mysqli_select_db($con, 'tutorial')) {
        echo 'Database Not Selected';
    }

    //Create variables
    $name = $_POST['name'];
    $email = $_POST['email'];
    $query = mysqli_query($con,"SELECT * FROM person WHERE name='$name' OR email='$email'");
    $sql = "INSERT INTO person (name, email) VALUES ('$name', '$email')";

    //Make sure name is valid
    if(!preg_match("/^[a-zA-Z'-]+$/",$name)) { 
        die ("invalid first name");
    }
 
    //Response
    //Checking to see if name or email already exsist
    if(mysqli_num_rows($query) > 0) {
        echo "The name, " . $_POST['name'] . ", or email, " . $_POST['email'] . ", already exists.";
    }
    elseif(!mysqli_query($con, $sql)) {
        echo 'Could not insert';
    }
    else {
        echo "Thank you, " . $_POST['name'] . ". Your information has been inserted.";
    }

    //Close connection
    mysqli_close($con);

?>

Again, this is just a basic form. Validation is rather simplistic, and the inputs could be more secure. Having the required attribute in the HTML input is highly recommended. However, extra security to prevent malicious inputs are wise. Otherwise, people or bots can easily submit incorrect data or spam to your database. You can read more about prevention here. There are other ways to have required fields using JavaScript but, for this simple form, the required attribute is perfect.

Back To Top