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!
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!
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:
}
?>
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!
Related articles
"Error establishing a database connection" MySQL/MariaDB error
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.
Use PHP to Retrieve Information From a MySQL/MariaDB Database
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.
How to use MySQL With Your Ruby on Rails Application
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.
Php.ini: Edit PHP configurations on a cloud server with Linux
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.
lassedesignenShutterstock Changing PHP Version - Common Errors
In this article we want to discuss with you common bugs in PHP version changes and work out solutions to fix them.
How to install Composer on Ubuntu 20.04
When programming with PHP, it's difficult to ignore the package manager Composer in the long run. Fortunately, installing Composer on Ubuntu 20.04 is a breeze. In this article, we'll show you how to install Composer in four easy steps as well as the process for adding and updating dependencies. Read on to get started with Composer.
