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>

    vServer (VPS) from IONOS

    Low-cost, powerful VPS hosting for running your custom applications, with a personal assistant and 24/7 support.

    100 % SSD storage
    Ready in 55 sec.
    SSL certificate

    Wait! We’ve got something for you!
    Have a look at our great prices for different domain extensions.


    Enter the web address of your choice in the search bar to check its availability.
    .club
    $1/1st year
    then $15/year
    .com
    $1/1st year
    then $15/year
    .info
    $1.60/1st year
    then $20/year
    .org
    $1/1st year
    then $25/year