How to Create an HTML Form That Stores Data in a MySQL Database

Collecting data from website visitors is critical for many web applications. A common way to gather information is through HTML forms. However, just retrieving form data is not enough. To make use of the submitted information, web developers need to store it in databases.

This comprehensive guide will walk through the steps to create an HTML form that saves entries into a MySQL database using PHP.

Setting Up the Environment

Before creating the HTML form, we need to set up a development environment that supports PHP and MySQL. There are several options for local web servers, but for this tutorial, we will use XAMPP since it bundles Apache, MySQL, PHP and popular CMS platforms.

Install XAMPP

Go to https://www.apachefriends.org/download.html and download the installer for your operating system. Follow the setup wizard to install XAMPP.

Start Apache and MySQL

By default, the Apache and MySQL modules within XAMPP are stopped. We need to initialize them to develop locally.

Open the XAMPP control panel and start Apache and MySQL. This will also launch phpMyAdmin which we‘ll use later.

With XAMPP running, our local development environment with PHP and MySQL support is ready. Next, we will create a database to store the form data.

Creating a MySQL Database

We need somewhere to persist the submitted form information. For this, we will create a new MySQL database using phpMyAdmin.

Access phpMyAdmin

Open a web browser and navigate to http://localhost/phpmyadmin to access the phpMyAdmin interface.

Log in with the default XAMPP credentials:

• Username: root
• Password: (no password)

Adding the Database

Once logged into phpMyAdmin, click on the "Databases" tab in the top menu.

Specify your desired database name (e.g. form_data) in the text field:

Then click the "Create" button to initialize the empty database.

We now have a MySQL database ready to store data sent from the HTML form.

Best Practices for Structuring Form Data Storage

When designing a database schema to collect web form submissions, it‘s important to model your tables appropriately to ensure efficient data handling. Here are some best practices to follow:

Normalize the Data

Database normalization principles should be applied to prevent data redundancy and improve integrity:

• Organize information into separate logical tables based on entity type
• Use unique ID columns as references between tables
• Avoid repeating groups of columns where possible

For example, you may have one table containing customer contact details, and a separate order details table linked by customer ID, rather than combining this data.

Use Appropriate Column Data Types

Carefully consider the type and length of database columns based on expected form values:

• VARCHAR vs TEXT for varying text input
• INT vs FLOAT for numeric data
• DATETIME columns to track submission timestamps

Index Columns Frequently Queried

Add indexes to columns that will be frequently searched or sorted to speed up query performance. For example, indexing columns like date submitted, customer ID, status, etc.

Partition Large Tables

When storing millions of rows, partition tables by date or other metrics allowing management of discrete subsets of data.

Keeping these tips in mind while structuring your MySQL database will help optimize it for collecting, querying and managing web form entries effectively.

Designing the HTML Form

With the foundations set up, we can focus on building the actual HTML form. This will contain various input fields to capture information that we want to save to the database.

For our example, we‘ll create a simple contact form to collect name, email and message data.

Form Structure

Here is the basic structure of our HTML contact form.

<form action="submit.php" method="POST">

  <label>Name:</label>
  <input type="text" name="name">

  <label>Email:</label> 
  <input type="email" name="email">

  <label>Message:</label>
  <textarea name="message"></textarea>

  <input type="submit" value="Submit">

</form>

The <form> tag contains the overall form, with action="submit.php" indicating the PHP script that will receive the entered data.

Inside are input fields for name, email and message, along with a submit button. Each field has a name attribute that will become the key when submitted.

Handling Submission

We specified the PHP file submit.php in the form‘s action attribute to process the data when the form gets submitted.

Here is an example submit.php script that handles the request:

<?php

// Connect to MySQL database  
$con = mysqli_connect("localhost","root","","form_data");

// Get form data
$name = $_POST[‘name‘];
$email = $_POST[‘email‘]; 
$message = $_POST[‘message‘];

// Insert form data into database
$sql = "INSERT INTO entries(name, email, message) VALUES(‘$name‘, ‘$email‘, ‘$message‘)";

mysqli_query($con, $sql);

?>

This script connects to the MySQL database we created earlier, fetches the form data with the $_POST array, and uses an INSERT query to save the entry into a database table.

With this submit.php handler set up, our HTML form will now be able to send and store data.

Creating a Table to Store Entries

The example PHP script tries to insert the form submission into a database table called entries. We still need to create this table using phpMyAdmin before data can actually be saved.

Navigate back to phpMyAdmin in your browser and select the database you created earlier.

Click on the "SQL" tab to edit an SQL statement that will generate the entries table:

CREATE TABLE entries (
  id INT(11) PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100),
  email VARCHAR(100), 
  message TEXT
);   

The statement structures a table called entries with columns for ID, name, email and message. The ID field is auto-incremented to uniquely identify each entry.

Click "Go" or "Execute" to run the statement and build the table within your selected database.

Now when form data gets handled by submit.php, it can properly insert into our prepared table.

Using PDO for Accessing MySQL

While the basic mysqli methods work for querying MySQL in our example above, PHP Data Objects (PDO) offers a more modern, object-oriented approach. Let‘s refactor our database logic using PDO instead:

// Connect to MySQL
$dsn = ‘mysql:host=localhost;dbname=form_data;charset=utf8‘;
$user = ‘root‘;
$pass = ‘‘;

try {
  $dbh = new PDO($dsn, $user, $pass);

  // Insert form data
  $stmt = $dbh->prepare("INSERT INTO entries (...) VALUES (?)"); 
  $stmt->execute([$_POST data]);

  $dbh = null;

} catch (PDOException $e) {
  print "Error!: " . $e->getMessage();
  die();
}

PDO wraps database connections in exception handling to help catch errors. It also supports prepared statements out of the box which are more secure.

Overall PDO provides better protection against vulnerabilities like SQL injections compared to the plain mysqli API.

Securing Forms Against Attacks

When processing user-submitted form data, web applications must safeguard against malicious attacks like SQL injection which can destroy or compromise your database.

Here are some key security precautions to take:

Sanitize All Inputs
Strip out invalid characters, escape strings, cast to expected data types, etc. using PHP filter functions and prepared statements.

Use Parameterized Queries
Placeholder values rather than concatenating raw strings avoid SQL syntax being manipulated.

Limit Database Permissions
Don‘t connect with admin privileges. Assign read/write access only for that particular database or tables.

Follow CSRF Best Practices
Validate tokens on sensitive forms to prevent cross-site request forgery.

Enable MySQL Security Features
Leverage user account control, network firewall rules and other protections.

Implement HTTPS
Encrypt all traffic between the browser and server for secure transmission.

Proactively building these checks into your web forms and backend infrastructure is crucial to lock down security from the start.

Optimizing Write Performance Into MySQL

While storing form data is often less resource intensive than complex analytical queries, with enough volume inserts can still become taxing. Here are some optimization strategies:

Collect Entries in a Staging Table First
Initially place submissions into a separate table, then bulk copy to the permanent table with a single INSERT query periodically.

Use Transactions for Batch Inserts
Wrap multiple INSERT statements into a transaction to vastly improve performance compared to individual queries.

Structure Indexes Efficiently
Balance indexing lookup columns for SELECTs with write speed. Fewer indexes result in faster inserts.

Partition Tables That Get Big
Breaking large tables into smaller chunks can maintain speed as data grows.

Tune MySQL Server Config
Adjust buffer sizes, memory limits and other settings appropriate for high insertion workloads.

With large web forms collecting hundreds of thousands of entries, performance tuning is key to prevent delays in saving user data as it flows in.

Submitting Form Data to External APIs

In some cases, you may want to synchronize information captured from web forms with external systems like CRMs, support ticketing tools or mailing list services.

Instead of just storing entries in a MySQL database, you can also use the form handler script to pass data to third-party APIs.

For example, here‘s how we can POST form details to a hypothetical API endpoint:

$url = ‘https://api.example.com/v1/contacts‘;

$data = [
  ‘name‘ => $_POST[‘name‘],
  ‘email‘ => $_POST[‘email‘],
  ‘message‘ => $_POST[‘message‘]
];

$options = [
  ‘http‘ => [
    ‘header‘  => "Content-type: application/json\r\n",
    ‘method‘  => ‘POST‘,
    ‘content‘ => json_encode($data)
  ]
];

$context  = stream_context_create($options);
$result = file_get_contents($url, false, $context);

This allows absorbing web form data into external systems in addition to a MySQL database by leveraging PHP as an integration engine.

Testing and Deployment

As a final step, thoroughly test your integrated HTML form and MySQL backend:

Functionality Testing
Verify form validation, error handling, thank you messages and data saving across different scenarios.

Cross-Browser Testing
Check appearance and behavior works on all target desktop and mobile platforms.

Security Testing
Attempt SQL injection and other attack vectors to identify vulnerabilities.

Performance Testing
Load test storage and retrieval with simulated production workloads.

When testing confirms everything operates correctly, deploy the web form proudly! Useful next steps are adding email notifications, running analysis on collected data and more.

Conclusion

Capturing website visitor information using HTML forms connected to MySQL is a common need for many web applications. With the steps covered in this guide including proper database modeling, input validation and performance considerations, you should feel equipped to build robust data collection mechanisms with PHP and MySQL.

The world awaits all those incredible web apps ready to launch and gather data from interested users!