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

    Register great TLDs for less than $1 for the first year.

    Why wait? Grab your favorite domain name today!

    Matching email
    SSL certificate
    24/7/365 support
    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';

    Dedicated Hosting - Powerful web hosting with dedicated hardware

    Fast and scalable, starting from $55/month!

    Fast
    Secure
    State of the art

    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 from IONOS

    Make costly downtime a thing of the past and back up your business the easy way!

    Simple
    Secure
    Integrated
    Related articles
    Frequent errors when changing PHP versions
    Frequent errors when changing PHP versions
    • Web development

    In this article we want to discuss with you common bugs in PHP version changes and work out solutions to fix them.

    Frequent errors when changing PHP versions
    Edit the PHP Configurations on a Cloud Server with Linux
    Php.ini: Edit PHP configurations on a cloud server with Linux
    • Web development

    The open-source scripting language PHP is used on many servers in order to enable the execution of various scripts. The main PHP configurations are located in the php.ini file, where they can also be modified. Keep reading our dedicated article to find out where the PHP configuration file is located in Linux and how you can edit it.

    Php.ini: Edit PHP configurations on a cloud server with Linux
    Use PHP to Retrieve Information From a MySQL-MariaDB Database
    Use PHP to Retrieve Information From a MySQL/MariaDB Database
    • Web development

    PHP is designed to easily integrate into a website. One of the most common uses for PHP is to take content from a database and output it on an HTML page. This tutorial will cover how to connect to a MySQL/MariaDB database, pull out information from a simple table, and display it in a simple HTML table.

    Use PHP to Retrieve Information From a MySQL/MariaDB Database
    Establishing a database connection MySQLMariaDB error
    "Error establishing a database connection" MySQL/MariaDB error
    • Know-how

    Learn how to fix "Error establishing a database connection” for MySQL/MariaDB on a Linux Server. With this guide you can easily fix the error for MySQL and MariaDB.

    "Error establishing a database connection" MySQL/MariaDB error
    Use MySQL With Ruby on Rails
    How to use MySQL With Your Ruby on Rails Application
    • Know-how

    Ruby on Rails uses SQLite as its database by default, but it also supports the use of MySQL. SQLite is an excellent alternative to a traditional database like MySQL, but it has some limitations, particularly with regards to concurrency and scaling to a high load, which may make MySQL a better choice for your project.

    How to use MySQL With Your Ruby on Rails Application