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

    Own your online success
    IONOS takes care of your online challenges
    Create your website with the perfect domain and the UK's fastest hosting!