Skip to main content

How to Fetch Data from MySQL Database in PHP and Display in Table

This is going to be the second part of the tutorial on how to save and retrieve data from a MySQL database in PHP. In the last post, we learned how to create an HTML form and link in with a database in PHP. In this tutorial, we will learn how to fetch the saved information from the database and display it in a nice HTML table.

MySQL Table

The database backend (MySQL table) is the common part that this tutorial will share with the last one. If you recall, the database phonebook consisted of just one table table1 which contained the following fields or columns: id, fname, lname, phonenumber, email.

PHP Script (show.php)

The following code retrieves the data and shows it in a table, the code with the comments is pretty self-explanatory:

<?php
define
('DB''phonebook');
define('DB_HOST''localhost');
define('DB_USER''db_user');
define('DB_PASS''db_pass');
define('DB_TABLE''table1');

$db = new mysqli(DB_HOSTDB_USERDB_PASSDB);

// Check connection
if ($db->connect_errno) {
    echo 
"Failed to connect to MySQL: " $mysqli->connect_error;
    exit;
}

// Fetch all the data in ascending order - notice this is where
// we use the "id" field which is a primary key
$result $db->query('SELECT * FROM ' DB_TABLE ' ORDER BY id ASC');
// The following "fetch_all" method, as the name suggests, fetches all
// the rows at once. The "MYSQLI_ASSOC" parameter makes the result in a 
// nice associative array
$phonebook $result->fetch_all(MYSQLI_ASSOC);

// Close connections
$db->close();

function 
sanitize($string) {
    return 
htmlspecialchars(stripslashes(trim($string)), ENT_QUOTES'UTF-8'false);
}
?>
<!DOCTYPE html>
<html>
    <head>
        <title>Phonebook</title>
        <style>
            table, th, td {
                border: 1px solid black;
                border-collapse: collapse;
            }
            th, td {
                padding: 10px;
            }
            tr:nth-child(even) {
                background: #e3e3e3;
            }
        </style>
    </head>
    <body>
        <h1>Show Phonebook Entries</h1>
        <table>
            <tr>
                <th>S. No.</th>
                <th>Name</th>
                <th>Phonenumber</th>
                <th>Email</th>
            </tr>
            <?php foreach ($phonebook as $phonebook_row) : ?>
                <tr>
                    <td><?php echo sanitize($phonebook_row['id']) ?></td>
                    <td><?php echo sanitize($phonebook_row['fname'] . ' ' $phonebook_row['lname']) ?></td>
                    <td><?php echo sanitize($phonebook_row['phonenumber']) ?></td>
                    <td><?php echo sanitize($phonebook_row['email']) ?></td>
                </tr>
            <?php endforeach; ?>
        </table>
        <div><a href="index.php">Add New Entry</a></div>
    </body>
</html>

One thing to mention is that, unless specifically required, it is always best practice to use htmlspecialchars on user-input data before displaying it on a page. If you recall from the first post, we did use it before inserting the data into the database. And even though we know the data coming from MySQL is already "escaped" I still prefer to run it through htmlspecialchars. The fourth argument which is $double_encode when set to false will prevent double encodes so we are safe from breaking the data here.

Here is the output:



The last thing to do to wrap this tutorial is to link this new page from the phonebook entry page by adding the following line towards the end of index.php (from the previous post). You can also redirect to show.php after saving the entry from save.php page if you'd like that.

<div><a href="show.php">Show New Enrties</a></div>

Here is the completed script in action:


Popular posts from this blog

Fix For Toshiba Satellite "RTC Battery is Low" Error (with Pictures)

RTC Battery is Low Error on a Toshiba Satellite laptop "RTC Battery is Low..." An error message flashing while you try to boot your laptop is enough to panic many people. But worry not! "RTC Battery" stands for Real-Time Clock battery which almost all laptops and PCs have on their motherboard to power the clock and sometimes to also keep the CMOS settings from getting erased while the system is switched off.  It is not uncommon for these batteries to last for years before requiring a replacement as the clock consumes very less power. And contrary to what some people tell you - they are not rechargeable or getting charged while your computer or laptop is running. In this article, we'll learn everything about RTC batteries and how to fix the error on your Toshiba Satellite laptop. What is an RTC Battery? RTC or CMOS batteries are small coin-shaped lithium batteries with a 3-volts output. Most laptops use

The Best Way(s) to Comment out PHP/HTML Code

PHP supports various styles of comments. Please check the following example: <?php // Single line comment code (); # Single line Comment code2 (); /* Multi Line comment code(); The code inside doesn't run */ // /* This doesn NOT start a multi-line comment block /* Multi line comment block The following line still ends the multi-line comment block //*/ The " # " comment style, though, is rarely used. Do note, in the example, that anything (even a multi-block comment /* ) after a " // " or " # " is a comment, and /* */ around any single-line comment overrides it. This information will come in handy when we learn about some neat tricks next. Comment out PHP Code Blocks Check the following code <?php //* Toggle line if ( 1 ) {      // } else {      // } //*/ //* Toggle line if ( 2 ) {      // } else {      // } //*/ Now see how easy it is to toggle a part of PHP code by just removing or adding a single " / " from th

How to Remove Bullet Points in CSS

This will be a short guide on how to remove bullet points from ordered/unordered lists with CSS, you only need two lines of CSS for this. The first removes the actual bullet points and the second one removes the space to the left, as evident from the following video: We'll also be doing some bullet beautification in the last section if that's what you are looking to do. How to Remove Bullet Points in HTML/CSS Now that we know what CSS properties actually accomplish what we want let's see how we can implement this in our HTML code. Using Inline "style" Tag (The quick and dirty way) As the title suggests, this is the quickest way to remove bullet points in which you wouldn't have to edit any CSS files (for example, for Blogger or WordPress). This method would be useful for a one-off case - just add the following " style " attribute to the list you want to remove the bullet points from: <h1>Ordered List</h1> <ol  style="list-styl