A PHP script is a con­ve­nient way to accept in­for­ma­tion 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.

Re­quire­ments

  • A Cloud Server running Linux (any dis­tri­b­u­tion)
  • Apache, MySQL/MariaDB, and PHP installed and running
$1 Domain Names – Register yours today!
  • Simple reg­is­tra­tion
  • Premium TLDs at great prices
  • 24/7 personal con­sul­tant included
  • Free privacy pro­tec­tion for eligible domains
Note

Apache, MySQL/MariaDB, and PHP are installed and running on a Standard in­stal­la­tion by default. If your server was created with a Minimal in­stal­la­tion, 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 restau­rant. The web page will allow customers to submit reviews of the restau­rant.

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 re­view­er'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 ap­prox­i­mate­ly 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, par­tic­u­lar­ly when that database will be accessed from a website.

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

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

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, in­evitably people would enter the wrong thing. When ap­plic­a­ble, 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 con­nec­tion 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 con­nec­tion 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 in­for­ma­tion 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 con­nec­tion failed: Access denied for user 'review_site'@'lo­cal­host' (using password: YES)" you can test the login in­for­ma­tion 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 pro­tec­tion
  • Automatic backup & easy recovery
  • Intuitive sched­ul­ing and man­age­ment
  • AI-based threat pro­tec­tion
Go to Main Menu