Use PHP to Retrieve Information From a MySQL/MariaDB Database

Leave your reply

Introduction

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.

To learn how to use PHP to take information from a website and put it into a MySQL/MariaDB database, see our related article Use PHP to Insert Information Into a MySQL/MariaDB Database From an HTML Form.

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. For more information, see the article Basic PHP Security.

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.

PHP MySQL

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>