How to fetch data from database in PHP and display

In this article, you will learn how to fetch data from database in PHP and display in HTML web page.

Data is one of the most important assets to any business. It is a significant resource in any organization, and for certain organizations, it is one of the main resources. The manner in which you store your information assumes a significant function in simple entry, use, and security. Digitizing data storage permits you to store large volumes of data in less space than paper would occupy.

To add, access, and perform operations on data, we need a database management system. MySQL is most popular and widely used Open Source Relational Database Management System.

Database

Suppose, we have the following 'employee' table. Execute the following query to create the employee table in your MySQL database -

CREATE TABLE IF NOT EXISTS `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `emp_name` varchar(150) NOT NULL,
  `email` varchar(150) NOT NULL,
  `phone` varchar(100) NOT NULL,
  `created_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

INSERT INTO `employee` (`id`, `emp_name`, `email`, `phone`, `created_date`) VALUES
(1, 'John', This email address is being protected from spambots. You need JavaScript enabled to view it.', '2323234543', '2019-06-05 15:36:07'),
(3, 'Priska', This email address is being protected from spambots. You need JavaScript enabled to view it.', '9393452387', '2019-05-28 15:36:07'),
(7, 'Alaya', This email address is being protected from spambots. You need JavaScript enabled to view it.', '3290349906', '2019-03-02 11:16:07'),
(8, 'Carle', This email address is being protected from spambots. You need JavaScript enabled to view it.', '9059098968', '2019-06-01 10:06:07'),
(9, 'Amma', This email address is being protected from spambots. You need JavaScript enabled to view it.', '6750390948', '2019-04-05 16:30:07');

Create Database Connection

Before, we can get data in the MySQL database, we need to be able to connect to the server. The beneath code is utilized to make a MySQL database connection in PHP. When we fetch, insert, update or delete data from MySQL database, there we will include this file. Make sure to replace the database credentials and name -

config.php

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "company";

$conn = new mysqli($servername , $username, $password', $database);

//Check for connection error
if($conn->connect_error){
  die("Error in DB connection: ".$conn->connect_errno." : ".$conn->connect_error);    
}
?>

Retrieve Data From Database

The given MySQL statement fetch data from the database -

$select = "SELECT * FROM employee WHERE is_enabled = '1'";
$result = $conn->query($select);

Display in Table

Here is the complete code to fetch data from the database and display in the HTML table -

<?php 

// Include configuration file  
require ('config.php');
 
//Select query 
$select = "SELECT * FROM employee";
$result = $conn->query($select);
?> 

<!DOCTYPE html> 
<html lang="en"> 
<head> 
    <meta charset="UTF-8"> 
    <title>Employee Details</title> 
	
	<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" >
</head> 
<body> 
    <div class="container">
		<table class="table">
			<thead class="thead-light">
			<tr>
			  <th scope="col">Emp Name</th>
			  <th scope="col">Email</th>
			  <th scope="col">Phone</th>
			</tr>
			</thead>
			<tbody>
                         
                        <?php   
			// Loop the employee data
				echo '<table class="table table-bordered">';
				while($row = $result->fetch_object()){
					echo '<tr>'
						.'<td>'.$row->emp_name.'</td>'
						.'<td>'.$row->email.'</td>'
						.'<td>'.$row->phone.'</td>'
						.'</tr>';
				}
				echo '</table>';
                        ?> 
			</tbody> 
        </table> 
    </div> 
</body> 
</html>