Use PHP to insert information into a MySQL/MariaDB database from a HTML form

A PHP script is a convenient way to accept information submitted from a website's HTML form and insert it into a MySQL/MariaDB database. This tutorial will cover how to create a basic PHP script for inserting data, and an HTML form to take user input from a webpage and pass it to the PHP script.

Requirements

  • A Cloud Server running Linux (any distribution)
  • Apache, MySQL/MariaDB, and PHP installed and running
$1 Domain Names – Grab your favorite one
  • Simple registration
  • Premium TLDs at great prices
  • 24/7 personal consultant included
  • Free privacy protection for eligible domains
Note

Apache, MySQL/MariaDB, and PHP are installed and running on a Standard installation by default. If your server was created with a Minimal installation, you will need to install and configure Apache, MySQL/MariaDB, and PHP before you proceed.

Create the MySQL/MariaDB database and user

For this tutorial we will create a web page for an imaginary restaurant. The web page will allow customers to submit reviews of the restaurant.

Log in to the command line MySQL/MariaDB client:

mysql -u root -p

Create a database for the reviews:

CREATE DATABASE reviews;

Switch to that database:

USE reviews;

For this example, we will only create one table. It will have three fields:

  • An ID field: This will be set to auto-increment.
  • The reviewer's name: A text field with a 100-character limit.
  • A star rating: A numeric rating of 1-5 TINYINT
  • Review details: A text field with a limit of approximately 500 words. VARCHAR(4000)

Create the table:

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

For security reasons, it is always best to create a unique user for each database, particularly when that database will be accessed from a website.

The following command will create a user review_site with password JxSLRkdutW and grant the user access to the reviews database:

GRANT ALL ON reviews.* to review_site@localhost IDENTIFIED BY 'JxSLRkdutW';
Web Hosting
Fast, scalable hosting for any website
  • 99.9% uptime
  • PHP 8.3 with JIT compiler
  • SSL, DDoS protection, and backups

Create the Web Page HTML Form

Create the file reviews.html in your webspace and open it for editing. For example, to create the file in /var/www/html the command is:

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

Put the following content into this file:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
    <body>
    <p>Let us know how we're doing!</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 review: <br>
                <textarea name="details" rows="10" cols="30"></textarea><br><br>
            <input type="submit" value="Submit" name="submit">
    </form>
</body>
</html>

A few things to note about this basic HTML form:

  • This form uses the POST method to pass data to the addreview.php PHP script.
  • The name for each input field will be used as the PHP variable name in the next step. It is usually best to use the database table field name for these values.
  • Never trust user input. In this example, we require a number from 1 to 5 for the star rating. If we asked users to manually enter their star rating, inevitably people would enter the wrong thing. When applicable, it's best to ask users to choose from a drop-down menu with the correct values already populated.

Create the PHP Script

Create a file addreview.php in your webspace and open it for editing. For example, to create the file in /var/www/html the command is:

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

Every PHP script must begin with the PHP opening tag:

<?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";

Then we add a section to connect to the database, and give an error if the connection fails:

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

Next, we "translate" the data from the HTML form into PHP variables:

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

Then insert this information into the database:

$query = "INSERT INTO user_review (reviewer_name, star_rating, details)
    VALUES ('$reviewer_name', '$star_rating', '$details')";

Add a nested if statement which will give an error message if the process fails, or thank the user for their review if it succeeds:

if (!mysqli_query($dbconnect, $query)) {
        die('An error occurred when submitting your review.');
    } else {
      echo "Thanks for your review.";
    }

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

}
?>
Note

If you get the error message "Database connection failed: Access denied for user 'review_site'@'localhost' (using password: YES)" you can test the login information by logging in to MySQL/MariaDB from the command line with the command:

mysql -u review_site -p reviews

To test the script, visit reviews.html in a browser and submit a review. Then log in to the reviews database with the command line MySQL/MariaDB client:

mysql -u root -p reviews

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

MariaDB [reviews]> SELECT * FROM user_review;
+----+---------------+-------------+---------------------------------------------------------+
| id | reviewer_name | star_rating | details                                                 |
+----+---------------+-------------+---------------------------------------------------------+
|  1 | Ben           |           5 | Love the calzone!                                      |    
|  2 | Leslie        |           1 | Calzones are the worst.                              |
+----+---------------+-------------+---------------------------------------------------------+
2 rows in set (0.00 sec)

The full PHP script is:

<?php
$hostname = "localhost";
$username = "review_site";
$password = "JxSLRkdutW";
$db = "reviews";
$dbconnect=mysqli_connect($hostname,$username,$password,$db);
if ($dbconnect->connect_error) {
    die("Database connection failed: " . $dbconnect->connect_error);
}
if(isset($_POST['submit'])) {
    $reviewer_name=$_POST['reviewer_name'];
    $star_rating=$_POST['star_rating'];
    $details=$_POST['details'];
    $query = "INSERT INTO user_review (reviewer_name, star_rating, details)
    VALUES ('$reviewer_name', '$star_rating', '$details')";
        if (!mysqli_query($dbconnect, $query)) {
                die('An error occurred. Your review has not been submitted.');
        } else {
            echo "Thanks for your review.";
        }
}
?>
Cloud Backup powered by Acronis
Mitigate downtime with total workload protection
  • Automatic backup & easy recovery
  • Intuitive scheduling and management
  • AI-based threat protection
Was this article helpful?
We use cookies on our website to provide you with the best possible user experience. By continuing to use our website or services, you agree to their use. More Information.