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_HOST, DB_USER, DB_PASS, DB);
// 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>
