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.
Requirements
- A Cloud Server running Linux (any distribution)
- Apache, MySQL/MariaDB, and PHP installed and running
Apache, MySQL/MariaDB, and PHP are installed and running on a Standard Linux 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 display customer 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)
);
Add two example reviews to the table:
INSERT INTO user_review (reviewer_name, star_rating, details) VALUES ('Ben', '5', 'Love the calzone!');
INSERT INTO user_review (reviewer_name, star_rating, details) VALUES ('Leslie', '1', 'Calzones are the worst.');
Create a user for the database. 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';
Create the PHP script
The code in this tutorial is simplified for the purpose of showing examples. When creating a website, we strongly advise you follow best security practices to ensure that your PHP scripts do not expose access to the server.
Create a file showreviews.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/showreviews.php
This page will have PHP embedded inside the HTML, so the page will begin with the basic HTML declarations:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<body>
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, add the HTML to begin the table we will use to display the data:
<table border="1" align="center">
<tr>
<td>Reviewer Name</td>
<td>Stars</td>
<td>Details</td>
</tr>
Follow this with the PHP code which will query the database and loop through the results, displaying each review in its own table row:
<?php
$query = mysqli_query($dbconnect, "SELECT * FROM user_review")
or die (mysqli_error($dbconnect));
while ($row = mysqli_fetch_array($query)) {
echo
"<tr>
<td>{$row['reviewer_name']}</td>
<td>{$row['star_rating']}</td>
<td>{$row['details']}</td>
</tr>;
}
?>
And finally, close out the table and the HTML:
</table>
</body>
</html>To test the script, visit showreviews.php in a browser.
The full PHP script is:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<body>
<?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);
}
?>
<table border="1" align="center">
<tr>
<td>Reviewer Name</td>
<td>Stars</td>
<td>Details</td>
</tr>
<?php
$query = mysqli_query($dbconnect, "SELECT * FROM user_review")
or die (mysqli_error($dbconnect));
while ($row = mysqli_fetch_array($query)) {
echo
"<tr>
<td>{$row['reviewer_name']}</td>
<td>{$row['star_rating']}</td>
<td>{$row['details']}</td>
</tr>\n";
}
?>
</table>
</body>
</html>
vServer (VPS) from IONOS
Low-cost, powerful VPS hosting for running your custom applications, with a personal assistant and 24/7 support.
Related articles
Andrey VPShutterstock Change the PHP Version on a Plesk Server
Learn how to change the PHP version for a server with Plesk. This tutorial includes instructions on how to find the PHP version your Plesk server is using, change to a different PHP version, and install a different PHP version if necessary.
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.
Install and Use PHP Composer on Ubuntu 16.04
PHP Composer is a package management system for PHP which prevents users from having to "reinvent the wheel" when it comes to commonly-used website components like user authentication or database management. Composer is modeled on other popular package management systems like Ruby's Bundler.
Using Composer in IONOS web hosting packages
Composer is a so-called "Dependency Management Tool", which makes it possible to comfortably reference external sources and libraries in one's own PHP projects and to keep them up-to-date.
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.
