MySQL/MariaDB: integrate an HTML form into the database

HTML forms allow users to enter data directly through a website, which can then be saved into a database. They thus serve as an interface between the website and the database, so that entries such as names, email addresses or comments can be stored and managed in a structured manner. This guide explains how to save, using PHP, information from an HTML form into a MySQL/MariaDB database.

What are the prerequisites?

To reliably save data from an HTML form into a MySQL or MariaDB database, you need an environment that meets the following prerequisites:

• Web server with PHP support: a web server, like Apache or NGINX, with PHP installed and enabled, so that server-side scripts can process the information passed by the form
• Basic knowledge of PHP and SQL: essential notions of PHP and SQL to establish the connection between the form and the database, and insert the data correctly
• Access to the web server configuration: ability to access the web server to run PHP scripts, create tables in the database and, if necessary, configure permissions

For this tutorial, we are creating a fictional website for a restaurant. The goal is to allow customers to leave reviews directly through the website. We show how to process an HTML form using a PHP script and then save the entered data to a MySQL or MariaDB database.

Step 1: Create the Database

First, we create a database so that you can store all the information from the HTML form. To do this, first connect to the MySQL/MariaDB client from the command line:

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

CREATE DATABASE avis_clients;

Then switch to this database:

For our example, and in order to simplify, we only create one table. This includes the following fields:

• An ID field: this field is defined with AUTO_INCREMENT. This setting ensures that the value is automatically increased by 1 with each new record, allowing a unique identifier to be assigned to each entry.
• The name of the person who left the review: a text field with a maximum length of 100 characters is used.
• A star rating: a numeric value between 1 and 5, defined with the data type TINYINT.
• A comment: a text field intended to store additional information or comments on the evaluation. Defined in VARCHAR(4000)it can contain approximately 500 words.

Now create the table using the CREATE TABLE command:

CREATE TABLE avis_clients (
id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
nom_du_client VARCHAR(100),
note_etoiles TINYINT,
commentaire VARCHAR(4000)
);

Step 2: Create a user

For security reasons, it is recommended to create a separate user for each database, especially when accessing from a website.

The following MariaDB command creates a named user avis_clients_site with password JxSLRkdutW and grants him access to the database containing customer reviews:

GRANT ALL ON avis_clients.* TO 'avis_clients_site'@'localhost' IDENTIFIED BY 'JxSLRkdutW';

If you are using MySQL, the command GRANT ... IDENTIFIED BY ... is no longer recommended in current versions. Instead, you should first create the user with CREATE USERthen use GRANT :

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

Step 3: Create the HTML Form for the Website

In the next step we create the review form for customer ratings. To do this, create a file named avis_clients.html in your web space and open it to modify it. For example, to create the file in /var/www/html with the nano text editor, use the following command:

sudo nano /var/www/html/avis_clients.html

Now insert the following content into this file:

Votre avis compte : comment s’est passée votre expérience chez nous ?

Here are some things to consider about this basic HTML form:

• This form uses the method POST to pass the data to the PHP script addreview.php.
• The name of each input field is adopted in the next step as a variable name in the PHP code. As a general rule, it is appropriate to use the same names as the database table fields.
• Never trust user input. In this example, the star rating should be a number between 1 and 5. If users entered the rating themselves, invalid values ​​could be sent. It is therefore preferable to offer predefined values ​​via a drop-down menu.

Step 4: Create the PHP Script

In the last step, we create the PHP script that inserts the HTML form data into the database. To do this, we establish a connection to MySQL or MariaDB, retrieve user input from the form and save it in the table created previously. Start by creating a file addreview.php on your web space and open it for modification. This is where the PHP code will be placed. To create the file in /var/www/html using nano, the command is:

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

Every PHP script must start with the PHP opening tag:

Then add a MySQL/MariaDB connection block with the server location (localhost), the database name, as well as the database user name and password.

$hostname = "localhost";
$username = "avis_clients_site";
$password = "JxSLRkdutW";
$db = "avis_clients";

The following code snippet establishes a database connection using the function mysqli_connect. Additionally, the script displays an error if this connection fails:

$dbconnect = mysqli_connect($hostname, $username, $password, $db);
if (mysqli_connect_errno()) {
die("Échec de la connexion à la base de données : " . mysqli_connect_error());
}

In the next step, we collect the data entered by the user via the HTML form and save it in PHP variables, so that we can then use it for processing and insertion into the database:

if (isset($_POST['submit'])) {
$nom_du_client = $_POST['nom_du_client'];
$note_etoiles = $_POST['note_etoiles'];
$commentaire = $_POST['commentaire'];

Then the data entered by the user must be written to the database. To do this, we create a query SQL INSERT which retrieves the values ​​of PHP variables and inserts them into the corresponding fields of the table avis_clients. To avoid SQL injections, we use prepared queries here:

$stmt = $dbconnect->prepare("INSERT INTO avis_clients (nom_du_client, note_etoiles, commentaire) VALUES (?, ?, ?)");
$stmt->bind_param("sis", $nom_du_client, $note_etoiles, $commentaire);

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 "Merci pour votre avis.";
} else {
die("Une erreur est survenue.");
}
$stmt->close();
}

Finally, close the conditional block and add a closing PHP tag:

Step 5: Test the Script

To test the script, open avis_clients.html in a browser, then send a sample review. Next, connect to the reviews database via the MySQL/MariaDB command line client:

mysql -u root -p avis_clients

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

MariaDB [avis_clients]> SELECT * FROM avis_clients;
+----+--------------+--------------+-------------------------------+
| id | nom_du_client | note_etoiles | commentaire                  |
+----+--------------+--------------+-------------------------------+
|  1 | Ben          |            5 | La calzone est délicieuse !   |
|  2 | Laura        |            1 | La calzone n’est pas bonne.   |
+----+--------------+--------------+-------------------------------+
2 rows in set (0.00 sec)

The complete PHP script is as follows:

connect_error) {
    die("Échec de la connexion à la base de données : " . $dbconnect->connect_error);
}
// Exécuter uniquement si le formulaire a été envoyé
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['submit'])) {
    // Valider les entrées (vérification minimale)
    $nom_du_client = trim($_POST['nom_du_client'] ?? '');
    $note_etoiles  = intval($_POST['note_etoiles'] ?? 0);
    $commentaire   = trim($_POST['commentaire'] ?? '');
    // Validation simple
    if ($nom_du_client === '' || $note_etoiles < 1 || $note_etoiles > 5 || $commentaire === '') {
        die("Veuillez remplir correctement tous les champs.");
    }
    // Créer une requête préparée
    $stmt = $dbconnect->prepare("INSERT INTO avis_clients (nom_du_client, note_etoiles, commentaire) VALUES (?, ?, ?)");
    if ($stmt === false) {
        die("Erreur de base de données : " . $dbconnect->error);
    }
    $stmt->bind_param("sis", $nom_du_client, $note_etoiles, $commentaire);
    // Exécuter la requête
    if ($stmt->execute()) {
        echo "Merci pour votre avis.";
    } else {
        die("Une erreur est survenue : " . $stmt->error);
    }
    $stmt->close();
}
// Fermer la connexion
$dbconnect->close();
?>