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:

Please, don't use `mysql_*` functions to write new code. They are no longer maintained and the community has begun [deprecation process](http://goo.gl/q0gwD). See the [red box](http://goo.gl/OWwr2)? Instead you should learn about [prepared statements](http://goo.gl/orrj0) and use either [PDO](http://goo.gl/TD3xh) or [MySQLi](http://php.net/mysqli). If you can't decide which, [this article](http://goo.gl/YXyWL) will help you. If you pick PDO, [here is good tutorial](http://goo.gl/b2ATO). Also see [Why shouldn't I use mysql functions in PHP?](http://goo.gl/J5jAo) — Dave Chen, Jul 11 '13 at 22:57
@DaveChen Hey, sorry about that. I'm still trying to learn. I really have no clue what the differences between both are. — Shivam Amin, Jul 11 '13 at 22:59

Dave Chen · Accepted Answer · 2013-07-11T23:21:04.703

4

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();
?>

edited Jul 11 '13 at 23:21

answered Jul 11 '13 at 23:02

Dave Chen

10,887
8
39
67

1

Ok, I tried this out, but there is still no data in the database. Also, why is "ssssssssss" there? What does it do? – Shivam Amin Jul 11 '13 at 23:11
1

Please `print_r($_POST)`, also, make sure the MySQLi credentials are correct. – Dave Chen Jul 11 '13 at 23:13
This is what I got after adding `print_r($POST)` to my file: `Array ( [firstname] => Fname [lastname] => LName [email] => Emailaddress@email.com [streetaddress] => 123 Stanley dr. [postalcode] => 12345 [city] => Schenectady [state] => New York [country] => United States [controllers] => 0 [color] => Black [verify] => 52fe7 [conrollers] => )` – Shivam Amin Jul 11 '13 at 23:16
1

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 Jul 11 '13 at 23:20
1

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. – Dave Chen Jul 11 '13 at 23:52

Eugene Scray · Answer 2 · 2013-07-11T23:09:31.570

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.

Yes, thank you didn't notice that my single quotes were in the wrong place. — Shivam Amin, Jul 11 '13 at 23:20

score 0 · Answer 3 · answered Jul 11 '13 at 23:03

The first thing I see is that you didn't escape your values before inserting them in your query. Imagine I write this in your email field :

'); drop table Information --

And you lost all your data.

To escape values, you could use the mysql_real_escape_string function() like this :

"......".mysql_real_escape_string($_POST['email'])."...."

How to write information from html form to MySQL Database

3 Answers3

Linked