How to save an HTML form to a MySQL database

Contents

HTML forms allow users to enter data directly on a website, which can then be stored in a database. They act as the interface between the website and the database, so entries such as names, email addresses, or comments can be captured and managed systematically. In this guide, we show you how to use PHP to store information from an HTML form in an HTML form database such as MySQL/MariaDB.

What are the requirements for storing data?

To reliably store data from an HTML form in a MySQL or MariaDB HTML form database, you need an environment that meets the following requirements:

Web server with PHP support: An Apache or NGINX) web server with PHP) installed and enabled so server-side scripts can process the form information
Basic knowledge of PHP and SQL: You’ll need a foundational understanding of PHP and SQL to connect the form to the database and insert data correctly.
Access to the web server configuration: You must have access to the web server so you can run PHP scripts, create database tables, and configure permissions if necessary.

Note

Apache, MySQL/MariaDB, and PHP are part of a standard installation and run there. If your server was created with a minimal installation, you must install and configure Apache, MySQL/MariaDB, and PHP before you can continue.

How to insert information from an HTML form into MySQL/MariaDB

For this tutorial, we’ll create a fictional restaurant website. The goal is to give customers the option to submit their reviews directly on the site. We’ll show you how to process HTML forms with a PHP script and store the submitted data in a MySQL or MariaDB database.

Step 1: Create a database

First, we’ll create a database so you can store all the information submitted through the HTML form. To begin, log in to the MySQL/MariaDB command-line client:

mysql -u root -p

bash

Now create a database for the reviews by using the SQL command CREATE DATABASE:

CREATE DATABASE reviews;

bash

Then switch to this database:

USE reviews;

bash

For this example, we’ll keep things simple and create only one table. This table will include the following four fields:

An ID field: This field is set to AUTO_INCREMENT. That means it automatically increases by one for every new record, ensuring each entry has a unique value.
Name of the reviewer: A text field with a maximum length of 100 characters.
A star rating: A numeric rating from 1 to 5, stored as the TINYINT data type.
Review details: A text field that stores comments or additional details about the review. With VARCHAR(4000), it provides space for roughly 500 words.

Now create the table using the CREATE TABLE command:

CREATE TABLE user_review (
id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
reviewer_name VARCHAR(100),
star_rating TINYINT,
details VARCHAR(4000)
);

bash

Step 2: Create a user

For security reasons, it’s recommended to create a dedicated user for each database, especially when the database is accessed from a website.

The following MariaDB command creates a user named review_site with the password JxSLRkdutW and grants that user access to the review database:

GRANT ALL ON reviews.* TO review_site@localhost IDENTIFIED BY 'JxSLRkdutW';

bash

If you’re using MySQL, the GRANT ... IDENTIFIED BY ... command is no longer recommended in current versions. Instead, you should first create the user with CREATE USER and then apply the permissions with GRANT:

CREATE USER 'review_site'@'localhost' IDENTIFIED BY 'JxSLRkdutW';
GRANT ALL PRIVILEGES ON reviews.* TO 'review_site'@'localhost';
FLUSH PRIVILEGES;

bash

Step 3: Create an HTML form for the website

In the next step, we’ll create the review form for customer feedback. To do this, create a file named reviews.html in your web space and open it for editing. For example, to create the file in /var/www/html using the nano text editor, use the following command:

sudo nano /var/www/html/reviews.html

bash

Now insert the following content into this file:

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Restaurant Review</title>
</head>
<body>
<p>How was your experience with us?</p>
<form action="addreview.php" method="POST">
Your name: <input type="text" name="reviewer_name"><br><br>
How many stars would you give us?
<select name="star_rating">
<option value="1">1 star</option>
<option value="2">2 stars</option>
<option value="3">3 stars</option>
<option value="4">4 stars</option>
<option value="5">5 stars</option>
</select><br><br>
Your rating: <br>
<textarea name="details" rows="10" cols="30"></textarea><br><br>
<input type="submit" value="Submit" name="submit">
</form>
</body>
</html>

html

Below are a few things to keep in mind when working with this basic HTML form:

This form uses the POST method to send data to the PHP script addreview.php.
The name you assign to each input field will be used as the PHP variable name in the next step. It’s generally a good idea to match these names with the corresponding column names in your database table.
Never trust user input. In this example, the star rating must be a number between 1 and 5. If users were allowed to type in the rating manually, they could easily enter an invalid value. That’s why it’s best to have users choose from predefined options in a dropdown menu instead.

Step 4: Create the PHP script

In the last step, we create the PHP script that inserts the data from the HTML form into the database. To do this, we establish a connection to MySQL or MariaDB, capture user input from the form, and store it in the table created earlier. First, create a file named addreview.php in your web space and open it for editing. This is where the PHP code will go. To create the file in /var/www/html using nano, run the following command:

sudo nano /var/www/html/addreview.php

bash

Every PHP script must begin with the opening PHP tag:

<?php

php

Next, add a MySQL/MariaDB connection block with the server location (localhost), the database name, and the database username and password.

$hostname = "localhost";
$username = "review_site";
$password = "JxSLRkdutW";
$db = "reviews";

php

The following code block uses the mysqli_connect function to connect to the database. The script also outputs an error if the connection fails:

$dbconnect = mysqli_connect($hostname, $username, $password, $db);
if (mysqli_connect_errno()) {
die("Database connection failed: " . mysqli_connect_error());
}

php

In the next step, we retrieve the data the user entered via the HTML form and store it in PHP variables so we can then process it and insert it into the database:

if (isset($_POST['submit'])) {
$reviewer_name = $_POST['reviewer_name'];
$star_rating = $_POST['star_rating'];
$details = $_POST['details'];

php

Next, the data entered by the user must be written to the database. To do this, we create a SQL INSERT query that takes the values from the PHP variables and inserts them into the corresponding fields of the user_review table. To avoid SQL injections, we use prepared statements at this point:

$stmt = $dbconnect->prepare("INSERT INTO user_review (reviewer_name, star_rating, details) VALUES (?, ?, ?)");
$stmt->bind_param("sis", $reviewer_name, $star_rating, $details);
$stmt->execute();

php

Add a PHP if-else statement that displays an error message if the process fails. If the process is successful, thank the user for their review:

if ($stmt->execute()) {
echo "Thank you for your review.";
} else {
die("An error occured.");
}
$stmt->close();
}

php

Finally, close the opening if statement and add a closing PHP tag:

}
?>

php

Step 5: Test the script

To test the script, visit reviews.html in a browser and then submit a sample review. Next, use the MySQL/MariaDB client from the command line to log in to the review database:

mysql -u root -p reviews

bash

Use SELECT * FROM user_review to display the entire contents of the table:

MariaDB [reviews]> SELECT * FROM user_review;
+----+---------------+-------------+---------------------------------------------------------+
| id | reviewer_name | star_rating | details                                                 |
+----+---------------+-------------+---------------------------------------------------------+
|  1 | Ben           |           5 | Delicious calzone!                                      |
|  2 | Leslie        |           1 | The calzone is not good.                                |
+----+---------------+-------------+---------------------------------------------------------+
2 rows in set (0.00 sec)

bash

The complete PHP script is:

// Database connection details
$hostname = "localhost";
$username = "review_site";
$password = "JxSLRkdutW";
$db       = "reviews";
// Establish a connection to the database
$dbconnect = new mysqli($hostname, $username, $password, $db);
// Check the connection
if ($dbconnect->connect_error) {
    die("Database connection failed: " . $dbconnect->connect_error);
}
// Execute only if the form was submitted
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['submit'])) {
    // Validate input (basic validation)
    $reviewer_name = trim($_POST['reviewer_name'] ?? '');
    $star_rating   = intval($_POST['star_rating'] ?? 0);
    $details       = trim($_POST['details'] ?? '');
    // Simple validation
    if ($reviewer_name === '' || $star_rating < 1 || $star_rating > 5 || $details === '') {
        die("Please fill out all fields correctly.");
    }
    // Create prepared statement
    $stmt = $dbconnect->prepare("INSERT INTO user_review (reviewer_name, star_rating, details) VALUES (?, ?, ?)");
    
    if ($stmt === false) {
        die("Database error: " . $dbconnect->error);
    }
    $stmt->bind_param("sis", $reviewer_name, $star_rating, $details);
    // Execute statement
    if ($stmt->execute()) {
        echo "Thank you for your review.";
    } else {
        die("An error occurred: " . $stmt->error);
    }
    $stmt->close();
}
// Close the connection
$dbconnect->close();
?>

php