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
Note

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

Note

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>
VPS Hosting
Fully virtualised servers with root access
  • Unlimited traffic and up to 1 Gbit/s bandwidth
  • Fast SSD NVMe storage
  • Free Plesk Web Host Edition
Was this article helpful?