Home » Internet » Web Development » How to store your form data to MySQL

How to store your form data to MySQL



You can easily store your form data to MySQL because it comes free with almost every web hosting package. When you have a web form on your website or blog you have various options for handling data submitted by your visitors:

  • Mail the form data to a particular email address
  • Store the data in a text file (we’ll cover this in another post)
  • Store the data to MySQL in a database table

Once you’ve stored data into a MySQL table you’ll be able to analyze it in many forms, and precisely this is the reason why data is stored in databases. You can run queries and create reports once you have substantial data.

Creating a MySQL database table

Every web host comes with a MySQL link that enables you to manipulate your database, most probably, using phpmyadmin. In order to create a MySQL table you first need to create a database. Using phpmyadmin you can easily create a new database. You can also use an existing database by simply selecting it from the list of existing databases.

It’s better to write down the structure of your MySQL table before creating it. Suppose you want to collect the following information:

  • First name
  • Last name
  • Gender
  • Age
  • City
  • Date of form submission

The last one you can straightaway derive from the system. So with this information, we create a new table by first clicking the SQL table and then entering the following standard SQL query/command:

CREATE TABLE form_data(
        id integer NOT NULL AUTO_INCREMENT,
        first_name VARCHAR(255),
        last_name VARCHAR(255),
        gender VARCHAR(10),
        age INTEGER,
        city VARCHAR(255),
        submit_date DATE,
        PRIMARY KEY(id));

After entering this click “Go” and your MySQL table “form_data” will be created.

Creating a web form

You’ll need to create an interface that your visitors will use to submit this data. You can have this basic form:

<form name=”web_form” id=”web_form” method=”post” action=”process-form-data.php”>
        <p><label>Enter first name: </label><input type=”text” name=”fname” id=”fname” /></p>
        <p><label>Enter last name: </label><input type=”text” name=”lname” id=”lname” /></p>
        <p><label>Select gender: </label><select name=”gender” id=”gender”>
                <option value=”-” selected=”selected”>-</option>
                <option value=”Male”>Male</option>
                <option value=”Female”>Female</option>
                <option value=”NA”>NA</option>
        </select></p>
        <p><label>Enter age: </label><input type=”text” name=”age” id=”age” /></p>
        <p><label>Enter city: </label><input type=”text” name=”city” id=”city” /></p>
        <p><input type=”submit” name=”s1″ id=”s1″ value=”Submit” /></p>
</form>

Whenever you have a web form you have another file associated with it that handles the data submitted by your visitors. Whatever happens to you form data, it happens in this file. In our example that file is “process-form-data.php”; we tell to the form what “action” to take once the data is submitted.

Creating the server-side script that stores your form data to MySQL

I’ve used a PHP file here but it’s up to you what server side language you want to use. You can use Perl, or Python, or CGI, or a horde of server-side programming scripts and languages available for different platforms. Our “process-form-data.php” contains the following PHP code that first captures all the form values into local PHP variables and then inserts them into the MySQL table we created above (comments are preceded by //).

<?php
// Connecting to the MySQL server
$host=”mysql.yourdomain.com”;
$user_name=”username”;
$pwd=”kj67GbF54″;
$database_name=”visitors”; //assuming you created this
$db=mysql_connect($host, $user_name, $pwd);
if (mysql_error() > “”) print mysql_error() . “<br>”;
mysql_select_db($database_name, $db);
if (mysql_error() > “”) print mysql_error() . “<br>”;
// Storing form values into PHP variables
$fname = $_POST[“fname”]; // Since method=”post” in the form
$lname = $_POST[“lname”];
$gender = $_POST[“gender”];
$age = $_POST[“age”];
$city = $_POST[“city”];
$submitdate = date(”Ymd”);
// Inserting these values into the MySQL table
// we created above
$query = “insert into form_data (first_name, last_name, gender, age, city, submit_date) values ('” . $fname . “‘, ‘” . $lname . “‘, ‘” . $gender . “‘, ” . $age . “, ‘” . $city . “‘, ‘” . $submitdate . “‘)”;
$result = mysql_query($query);
// mysql_query() is a PHP function for executing
// MySQL queries
echo “<h1>Thank you for submitting your details!</h1>”;
?>

When you are storing values into MySQL tables you need to enclose the alphanumeric, date and text values within quotes and that’s why you see those three single quotes. They are actually a single quote followed by a double quote: the single quote becomes part of the final SQL string that may look like:

insert into form_data (first_name, last_name, gender, age, city, submit_date) values (‘Danny’, ‘Garcia’, ‘Male’, 23, ‘LA’, ‘20100615’)

As you’ll note the number 23 is not enclosed within quotes, and if it, it becomes a character value.

So this is how you store your form data in MySQL.