With PHP, in­for­ma­tion can be easily retrieved from a MySQL or MariaDB database. A con­nec­tion to the database is es­tab­lished, and then a SQL query is executed with PHP mysqli_query(), allowing the results to be further processed.

Managed Database Services
Time-saving database services
  • En­ter­prise-grade ar­chi­tec­ture managed by experts
  • Flexible solutions tailored to your re­quire­ments
  • Leading security in ISO-certified data centers

Re­quire­ments

To access data from a MySQL or MariaDB database with PHP, a few basic re­quire­ments must be met. First, a working PHP en­vi­ron­ment on the server is necessary, typically in com­bi­na­tion with a web server such as Apache or Nginx. If this is not yet available, you will need to install PHP. In addition, MySQL or MariaDB must be installed. On the PHP side, the MySQLi extension is required, though it is already included in most standard in­stal­la­tions.

How to retrieve in­for­ma­tion from MySQL/MariaDB with PHP

In this tutorial, you’ll learn how to set up a basic MySQL/MariaDB database with a table and user, and then connect to it using PHP. Step by step, we’ll build a simple sample website that pulls restau­rant reviews from the database and displays them in an HTML table.

Step 1: Create a database

The first step is to create the database. To do this, log in to the MySQL/MariaDB client from the command line:

mysql -u root -p

Create a database for the reviews:

CREATE DATABASE reviews;

Switch to this database:

USE reviews;

For this example, we will create a single table with four fields:

  • ID: The primary key of the table, set to auto-increment.
  • Reviewer name: A text field with a maximum length of 100 char­ac­ters.
  • Star rating: A numeric field (TINYINT) for values from 1 to 5.
  • Review text: A text field for up to around 500 words (VARCHAR(4000)).

Create the table using the CREATE-TABLE command:

CREATE TABLE user_review (
id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
reviewer_name VARCHAR(100),
star_rating TINYINT,
details VARCHAR(4000)
);

Now let’s add two sample 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.');

Step 2: Create a database user

In the next step, create a user for the database. For security reasons, it is rec­om­mend­ed to set up a unique user for each database, es­pe­cial­ly if the database will be accessed from a website.

The following command creates a user named review_site with the password JxSLRk­dutW and grants access to the newly created database:

CREATE USER 'review_site'@'localhost' IDENTIFIED BY 'JxSLRkdutW';
GRANT SELECT ON reviews.* TO 'review_site'@'localhost';

Step 3: Create a PHP script

Note

The code in this tutorial is sim­pli­fied to il­lus­trate examples. When creating a real website, it is strongly rec­om­mend­ed to follow common security best practices to ensure that your PHP scripts do not com­pro­mise server access.

Create a file named showreviews.php in your webspace and open it for editing. For example, to create the file in the /var/www/html directory using the nano editor, run the following command:

sudo nano /var/www/html/showreviews.php

In this example, PHP is embedded within HTML, so the page starts with the basic HTML de­c­la­ra­tions. The CSS styling is also included in the HTML head:

<!doctype html>
<html>
<head>
<meta-charset="utf-8">
<style>
    table {
        border-collapse: collapse;
        margin: 20px auto;
        width: 80%;
    }
    th, td {
        border: 1px solid black;
        padding: 5px 10px;
        text-align: left;
    }
</style>
</head>
<body>
HTML

Every PHP script must begin with the PHP opening tag:

<?php
HTML

Next, add a MySQL/MariaDB con­nec­tion block that includes the server location (localhost), the database name, and the database username and password.

$hostname = "localhost";
$username = "review_site";
$password = "JxSLRkdutW";
$db = "reviews";
HTML

Next, add a section to establish a con­nec­tion to the database. If the con­nec­tion fails, an error message will appear. In both MySQL and MariaDB, you can connect your PHP script to the database using mysqli_connect(). This function requires the hostname, username, password, and database name, so PHP knows exactly which database to access.

$dbconnect=mysqli_connect($hostname,$username,$password,$db);
if (!$dbconnect) {
die("Database connection failed: " . mysqli_connect_error());
}
?>
HTML

Step 4: Insert the table

In the next step, add the HTML code to your script to create the table structure that will be used to display the data:

<table>
<tr>
<td>Reviewer Name</td>
<td>Stars</td>
<td>Details</td>
</tr>
HTML

Next, add the PHP code that queries the database and loops through the results, dis­play­ing 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>\n
}
?>
HTML

Finally, remember to close the table and the HTML block at the end of the document:

</table>
</body>
</html>
HTML

To test the script, open showreviews.php in your web browser.

Image: Display of showreviews.php in the browser
The table is now displayed in the browser.

Step 5: Overview of the completed script

The complete PHP script is as follows:

<!doctype html>
<html>
<head>
<meta-charset="utf-8”>
<style>
    table {
        border-collapse: collapse;
        margin: 20px auto;
        width: 80%;
    }
    th, td {
        border: 1px solid black;
        padding: 5px 10px;
        text-align: left;
    }
</style>
</head>
<body>
<?php
$hostname = "localhost";
$username = "review_site";
$password = "JxSLRkdutW";
$db = "reviews";
$dbconnect=mysqli_connect($hostname,$username,$password,$db);
if (!$dbconnect) {
die("Database connection failed: " . mysqli_connect_error());
}
?>
<table>
<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>
HTML
Go to Main Menu