Hello Developer, In this tutorial, You will learn to display data in an HTML table using PHP and MySQL. Even You will know more to fetch new records from the database and show them in the tabular format using MySQLi procedure, MySQLi Object-oriented, PDO & prepared statement with a new concept & an example.
Steps to Display Data From MySQL Database with PHP
In this step, you will learn to display data from the database dynamically with an advanced coding concept that will help you to improve your coding skills for writing smart & standard code in PHP.
Before getting started, make sure that the local server (xamp/wamp) must be installed in your system and start it if it is not being started already.
Learn Also –
Preview an Image before Uploading using PHP
PHP Image Gallery With MySQL Database
How to create pagination using PHP & mysql
You can test yourself to display data with the following folder structure –
codingstatus/
|__database.php
|__table.php
|__developers.php
|1. Connect PHP to MySQL Database
You can use the following database connection query to connect PHP to the MySQL database
- $hostName – It contains host name
- $userName – It contains database username
- $password – It contains database password
- $databaseName – It contains database name.
File Name – database.php
<?php
$hostName = "localhost";
$userName = "root";
$password = "";
$databaseName = "codingstatus";
$conn = new mysqli($hostName, $userName, $password, $databaseName);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>2. Insert Data Into PHPMyAdmin Table
Before displaying data, you have to insert data into the Database. If you have not already done it and don’t know about it, then you can learn it through the following URL –
Insert Data into Database using PHP & MySQL
3. Fetch Data From MySQL Table
Now, You have to fetch data from the MySQL table. So, just follow these points –
- First of all, include a database connection file database.php
- assign $conn to a new variable $db and table name to another variable $table
- Define columns name in an indexed array and assign them to the $columns
- Also, assign fetch_data() function to the $fetchData
fetch_data() – This function accepts three parameters like $db, $table & $column and It contains MySQLi SELECT query that will return records in an array format by fetching from the database
File name – developers.php
<?php
include("database.php");
$db= $conn;
$tableName="developers";
$columns= ['id', 'fullName','gender','email','mobile', 'address','city','state'];
$fetchData = fetch_data($db, $tableName, $columns);
function fetch_data($db, $tableName, $columns){
if(empty($db)){
$msg= "Database connection error";
}elseif (empty($columns) || !is_array($columns)) {
$msg="columns Name must be defined in an indexed array";
}elseif(empty($tableName)){
$msg= "Table Name is empty";
}else{
$columnName = implode(", ", $columns);
$query = "SELECT ".$columnName." FROM $tableName"." ORDER BY id DESC";
$result = $db->query($query);
if($result== true){
if ($result->num_rows > 0) {
$row= mysqli_fetch_all($result, MYSQLI_ASSOC);
$msg= $row;
} else {
$msg= "No Data Found";
}
}else{
$msg= mysqli_error($db);
}
}
return $msg;
}
?>
4. Display Data in HTML Table
Now, You have to display data in the HTML table. So, you have to follow these points –
- First of all, Include PHP script file developers.php
- Create an HTML table using Bootsrap 4
- Check $fetchData is an array or not with if & else condition
- Then apply foreach loop to the $fetchData
- After that print the required data in the table
File Name – table.php
<?php
include("developers.php");
?>
<!DOCTYPE html>
<html>
<head>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-sm-8">
<?php echo $deleteMsg??''; ?>
<div class="table-responsive">
<table class="table table-bordered">
<thead><tr><th>S.N</th>
<th>Full Name</th>
<th>Gender</th>
<th>Email</th>
<th>Mobile Number</th>
<th>Address</th>
<th>City</th>
<th>State</th>
</thead>
<tbody>
<?php
if(is_array($fetchData)){
$sn=1;
foreach($fetchData as $data){
?>
<tr>
<td><?php echo $sn; ?></td>
<td><?php echo $data['fullName']??''; ?></td>
<td><?php echo $data['gender']??''; ?></td>
<td><?php echo $data['email']??''; ?></td>
<td><?php echo $data['mobile']??''; ?></td>
<td><?php echo $data['address']??''; ?></td>
<td><?php echo $data['city']??''; ?></td>
<td><?php echo $data['state']??''; ?></td>
</tr>
<?php
$sn++;}}else{ ?>
<tr>
<td colspan="8">
<?php echo $fetchData; ?>
</td>
<tr>
<?php
}?>
</tbody>
</table>
</div>
</div>
</div>
</div>
</body>
</html>
5. Test Yourself to insert data
After Implementing previous steps, You can test it yourself to open the following URL in your web browser
https://localhost/codingstatus/table.php
More Methods to display Data in HTML Table with PHP
Now, You should know more methods to display data in the database from the next step. After learning those methods, you can use one of them in your project.
From the next step, I have shared only the source code without a custom function. So that you can directly paste it into your project where you need to implement it.
Display Data using MySQLi Procedure
Display data with MySQLi Procedure
<?php
include('database.php');
$query = "SELECT fullName, gender, email, mobile, address, city, state FROM developers";
$result = mysqli_query($conn, $query);
?>
<table border ="1" cellspacing="0" cellpadding="10">
<tr>
<th>S.N</th>
<th>Full Name</th>
<th>Gender</th>
<th>Email</th>
<th>Mobile No</th>
<th>Address</th>
<th>City</th>
<th>State </th>
</tr>
<?php
if (mysqli_num_rows($result) > 0) {
$sn=1;
while($data = mysqli_fetch_assoc($result)) {
?>
<tr>
<td><?php echo $sn; ?> </td>
<td><?php echo $data['fullName']; ?> </td>
<td><?php echo $data['gender']; ?> </td>
<td><?php echo $data['email']; ?> </td>
<td><?php echo $data['mobile']; ?> </td>
<td><?php echo $data['address']; ?> </td>
<td><?php echo $data['city']; ?> </td>
<td><?php echo $data['state']; ?> </td>
<tr>
<?php
$sn++;}} else { ?>
<tr>
<td colspan="8">No data found</td>
</tr>
<?php } ?>
</table
Display Data Using MySQLi Object-Oriented
Display data with MySQLi Object-Oriented
<?php
include('database.php');
$query = "SELECT fullName, gender, email, mobile, address, city, state FROM developers";
$result = $conn->query($query);
?>
<table border="1" cellspacing="0" cellpadding="10">
<tr>
<th>S.N</th>
<th>Full Name</th>
<th>Gender</th>
<th>Email</th>
<th>Mobile No</th>
<th>Address</th>
<th>City</th>
<th>State </th>
</tr>
<?php
if ($result->num_rows > 0) {
$sn=1;
while($data = $result->fetch_assoc()) {
?>
<tr>
<td><?php echo $sn; ?> </td>
<td><?php echo $data['fullName']; ?> </td>
<td><?php echo $data['gender']; ?> </td>
<td><?php echo $data['email']; ?> </td>
<td><?php echo $data['mobile']; ?> </td>
<td><?php echo $data['address']; ?> </td>
<td><?php echo $data['city']; ?> </td>
<td><?php echo $data['state']; ?> </td>
<tr>
<?php
$sn++;}} else { ?>
<tr>
<td colspan="8">No data found</td>
</tr>
<?php } ?>
</table
Display Data Using PDO
Connect Database with PDO
Display Data with PDO
<?php
// include("database.php");
$hostName = "localhost";
$username = "root";
$password = "";
$databaseName = "codingstatus";
$conn = new PDO("mysql:host=$hostName;dbname=$databaseName", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try {
$query = "SELECT fullName, gender, email, mobile, address, city, state FROM developers";
$result = $conn->query($query);
?>
<table border="1" cellpadding="10" cellspacing="0">
<?php
$sn=1;
while($data = $result->fetch(PDO::FETCH_ASSOC)) {
?>
<tr>
<td><?php echo $sn; ?> </td>
<td><?php echo $data['fullName']; ?> </td>
<td><?php echo $data['gender']; ?> </td>
<td><?php echo $data['email']; ?> </td>
<td><?php echo $data['mobile']; ?> </td>
<td><?php echo $data['address']; ?> </td>
<td><?php echo $data['city']; ?> </td>
<td><?php echo $data['state']; ?> </td>
</tr>
<?php
}
?>
</table>
<?php
} catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}
Display Data Using Prepared Statement
Display data using Prepared Statement with MySQLi –
<?php
include('database.php');
$query = "SELECT fullName, gender, email, mobile, address, city, state FROM developers";
$prepared = $conn->prepare($query);
$prepared->execute();
$result = $prepared->get_result();
?>
<table border="1" cellspacing="0" cellpadding="10">
<tr>
<th>S.N</th>
<th>Full Name</th>
<th>Gender</th>
<th>Email</th>
<th>Mobile No</th>
<th>Address</th>
<th>City</th>
<th>State </th>
</tr>
<?php
if ($result->num_rows > 0) {
$sn=1;
while($data = $result->fetch_assoc()) {
?>
<tr>
<td><?php echo $sn; ?> </td>
<td><?php echo $data['fullName']; ?> </td>
<td><?php echo $data['gender']; ?> </td>
<td><?php echo $data['email']; ?> </td>
<td><?php echo $data['mobile']; ?> </td>
<td><?php echo $data['address']; ?> </td>
<td><?php echo $data['city']; ?> </td>
<td><?php echo $data['state']; ?> </td>
<tr>
<?php
$sn++;}
} else {
?>
<tr>
<td colspan="8">No data found</td>
</tr>
<?php } ?>
</table
Display data using Prepared Statement with PDO –
<?php
$hostName = "localhost";
$username = "root";
$password = "";
$databaseName = "codingstatus";
$conn = new PDO("mysql:host=$hostName;dbname=$databaseName", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try {
$query = "SELECT fullName, gender, email, mobile, address, city, state FROM developers";
$prepared = $conn->prepare($query);
$prepared->execute();
$result = $prepared -> fetchAll(PDO::FETCH_ASSOC);
?>
<table border="1" cellpadding="10" cellspacing="0">
<?php
$sn=1;
foreach($result as $data) {
?>
<tr>
<td><?php echo $sn; ?> </td>
<td><?php echo $data['fullName']; ?> </td>
<td><?php echo $data['gender']; ?> </td>
<td><?php echo $data['email']; ?> </td>
<td><?php echo $data['mobile']; ?> </td>
<td><?php echo $data['address']; ?> </td>
<td><?php echo $data['city']; ?> </td>
<td><?php echo $data['state']; ?> </td>
</tr>
<?php
}
?>
</table>
<?php
} catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}
?>