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 virtualized servers with root access
  • Unlimited traffic
  • Fast SSD NVMe storage
  • Free Plesk Web Host Edition
Was this article helpful?
We use cookies on our website to provide you with the best possible user experience. By continuing to use our website or services, you agree to their use. More Information.