How to write information from html form to MySQL Database

Question

Alright, so I'm setting up a website that has a form on it and I want to save all the information that the user types into the form to my MySQL Database. The form is coded like this:

<form method="post" action="claim.php" name="ClaimForm" id="ClaimForm" autocomplete="on">
    <fieldset>
        <legend>Contact Details</legend>
        <div>
            <label for="firstname" accesskey="U">Your First Name</label>
            <input name="firstname" type="text" id="firstname" placeholder="Enter your name" required />
        </div>
        <div>
            <label for="lastname" accesskey="U">Your Last Name</label>
            <input name="lastname" type="text" id="lastname" placeholder="Enter your name" required />
        </div>
        <div>
            <label for="email" accesskey="E">Email</label>
            <input name="email" type="email" id="email" placeholder="Enter your Email Address" pattern="^[A-Za-z0-9](([_\.\-]?[a-zA-Z0-9]+)*)@([A-Za-z0-9]+)(([\.\-]?[a-zA-Z0-9]+)*)\.([A-Za-z]{2,})$" required />
        </div>
        <div>
            <label for="streetaddress">Street Address</label>
            <input name="streetaddress" type="text" id="streetaddress" placeholder="123 Stanley dr." required />
        </div>
        <div>
            <label for="postalcode">Postal Code</label>
            <input name="postalcode" type="text" id="postalcode" placeholder="12345, A1B 2C3, etc." required />
        </div>
        <label for="city">City</label>
        <input name="city" type="text" id="city" placeholder="Schenectady" required />
        <div>
            <label for="state">State/Province</label>
            <input name="state" type="text" id="state" placeholder="New York" required />
        </div>
        <div>
            <label for="country">Country</label>
            <input name="country" type="text" id="country" placeholder="United States" required />
        </div>
    </fieldset>
    <fieldset>
        <legend>Extra</legend>
        <div>
            <label for="controllers" accesskey="S">Number of Controllers</label>
            <select name="controllers" id="controllers" required="required">
                <option value="0">0</option>
                <option value="1">1</option>
                <option value="2">2</option>
                <option value="3">3</option>
                <option value="4">4</option>
                <option value="5">5</option>
            </select>
        </div>
        <div>
            <label for="color" accesskey="C">Color</label>
            <select name="color" id="color" required="required">
                <option value="Black">Black</option>
                <option value="White">White</option>
                <option value="Red">Red</option>
                <option value="Blue">Blue</option>
                <option value="Gold">Gold</option>
                <option value="Purple">Purple</option>
            </select>
        </div>
    </fieldset>
    <fieldset>
        <legend>Captcha Verification</legend>
        <label for="verify" accesskey="V" class="verify"><img src="captcha.php" alt="Verification code" /></label>
        <input name="verify" type="text" id="verify" size="6" required style="width: 50px;" title="This confirms you are a human user and not a spam-bot." />
    </fieldset>
    <input type="submit" class="submit" id="submit" value="Submit" />
</form>

I tried using this code in Claim.php to try and save that to the database:

<?php
$mysql_host     = "localhost";
$mysql_username = "username";
$mysql_password = "password";
$mysql_database = "database";

mysql_select_db($mysql_database, mysql_connect($mysql_host, $mysql_username, $mysql_password));
//Sending form data to sql db.
mysqli_query("INSERT INTO Information (Firstname,Lastname,Email,StreetAddress,PostalCode,City,StateProvince,Country,Controllers,Color) VALUES ('$_POST[firstname]','$_POST[lastname]','$_POST[email]','$_POST[streetaddress]','$_POST[postalcode]','$_POST[city]','$_POST[state]','$_POST[country]','$_POST[conrollers]','$_POST[color]'))");
?>

Is there anything wrong with my code? Or is my database structured wrong? I just started learning how to code and this is confusing me.

Picture of my database structure:

Solution 1

Please use mysqli. I have altered your code to prepare the insert instead.

If you didn't, it would be a huge SQL injection party.

Also, to access $_POST, you should give a string index, like $_POST['firstname']. Though it works like $_POST[firstname], PHP will emit a warning.

<?php
$mysql_host     = "localhost";
$mysql_username = "username";
$mysql_password = "password";
$mysql_database = "database";

$mysqli  = new Mysqli($mysql_host, $mysql_username, $mysql_password, $mysql_database);
$prepare = $mysqli->prepare("INSERT INTO `Information`(`Firstname`,`Lastname`,`Email`,`StreetAddress`,`PostalCode`,`City`,`StateProvince`,`Country`,`Controllers`,`Color`) VALUES (?,?,?,?,?,?,?,?,?,?)");
$prepare->bind_param("ssssssssss", $_POST['firstname'], $_POST['lastname'], $_POST['email'], $_POST['streetaddress'], $_POST['postalcode'], $_POST['city'], $_POST['state'], $_POST['country'], $_POST['controllers'], $_POST['color']);
$prepare->execute();
$mysqli->close();
?>

Solution 2

You may want to consider getting your php info to see what version you are running.

If you are running a version that supports mysqli objects you may want to start there and instantiate an object of mysqli.

Mysqli documentation: http://us.php.net/manual/en/book.mysqli.php

This makes it so your methods are not deprecated.

Also keep in mind you are not going to see any errors in your output when posting to your page. This can complicate debugging. Use these two lines of php to see errors:

error_reporting(E_ALL);
ini_set('display_errors', '1');

It is also good practice to make sure you set all of your variables. You can do this by using the isset() method to check they are set before you insert your data.

I bet you will find some things wrong when errors are set.

It looks like you are not accessing your variables correctly.

$_POST[varname] will not access the data and throw an error message.

$_POST['varname'] will work.

Comments

[Shivam Amin]

Ok, I tried this out, but there is still no data in the database. Also, why is "ssssssssss" there? What does it do?

[Dave Chen]

Please print_r($_POST), also, make sure the MySQLi credentials are correct.

[Shivam Amin]

This is what I got after adding print_r($POST) to my file: Array ( [firstname] => Fname [lastname] => LName [email] => [email protected] [streetaddress] => 123 Stanley dr. [postalcode] => 12345 [city] => Schenectady [state] => New York [country] => United States [controllers] => 0 [color] => Black [verify] => 52fe7 [conrollers] => )

[Shivam Amin]

Thanks for telling me to add print_r($_POST). I would have never caught the typo, and it adds the information to my database. Thanks alot. Also, you might want to fix the typo conrollers to controllers in your answer. Might help other people who have the same question as me.

[Shivam Amin]

Yes, thank you didn't notice that my single quotes were in the wrong place.

[Dave Chen]

The long strand of s'es are to indicate what type of data you are inserting. In this case, all of them are strings (from your database image). You have ten pieces of post, therefore ten s'es.