1. Connect to the database
Before you can fetch and display data from a database, you must first establish a connection to the database. This can be done using the PHP function mysqli_connect().
2. Create a query
Once you have established a connection to the database, you can create a query to fetch the data you need. This can be done using the PHP function mysqli_query().
3. Fetch the data
Once you have created a query, you can use the PHP function mysqli_fetch_assoc() to fetch the data from the database. This function will return an associative array containing the data from the query.
4. Display the data
Once you have fetched the data from the database, you can display it in a table using HTML and CSS. You can use the PHP function echo to output the HTML code for the table.
5. Close the connection
Once you have finished displaying the data, you should close the connection to the database using the PHP function mysqli_close(). This will ensure that the connection is closed properly and that no further queries can be made.
Retrieve or fetch the data from the MySQL database in PHP and display table; In this tutorial, we will show you how to fetch/select/retrieve the data from the database in PHP and display it in the bootstrap Table.
First of all, we will create one database connecting file, And create html table web page with display data from database in PHP.
PHP Code Retrieve Data from the Database and Display in HTML Table
To retrieve data from MySQL, the SELECT statement is used. That can retrieve data from a specific column or all columns of a table.
If you want to get selected column data from the database. Use the below SQL query is:
SELECT column_name,column_name FROM table_name;
If you want to get all the columns data from a table. Use the below SQL query is:
SELECT * FROM table_name;
PHP code to fetch data from MySQL database and display in HTML table
Use the below given two steps and fetch data from MySQL database in PHP and display in HTML table:
- Connecting to the database in PHP
- Fetch data from the database and display in table
1. Connecting to the database in PHP
In this step, you will create a file name db.php and update the below code into your file.
The below code is used to create a MySQL database connection in PHP. When we fetch, insert, update or delete data from MySQL database, there we will include this file:
<?php
$host='localhost';
$username='root';
$password='';
$dbname = "my_db";
$conn=mysqli_connect($host,$username,$password,"$dbname");
if(!$conn)
{
die('Could not Connect MySql Server:' .mysql_error());
}
?>
2. Fetch data from the database and display in table
In this step, we will fetch the data from the MySQL database in PHP and display data in an HTML table. So you can create a new file and update the below code into your file.
The below code is used to retrieve or receive data from the MySQL database in PHP. Additionally, we will display the fetched data in an HTML table.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<title>Bootstrap 4 Bordered Table</title>
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Roboto|Varela+Round|Open+Sans">
<link rel="stylesheet" href="https://fonts.googleapis.com/icon?family=Material+Icons">
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"></script>
<style type="text/css">
.bs-example{
margin: 20px;
}
</style>
<script type="text/javascript">
$(document).ready(function(){
$('[data-toggle="tooltip"]').tooltip();
});
</script>
</head>
<body>
<div class="bs-example">
<div class="container">
<div class="row">
<div class="col-md-12">
<div class="page-header clearfix">
<h2 class="pull-left">Users List</h2>
</div>
<?php
include_once 'db.php';
$result = mysqli_query($conn,"SELECT * FROM users");
?>
<?php
if (mysqli_num_rows($result) > 0) {
?>
<table class='table table-bordered table-striped'>
<tr>
<td>Name</td>
<td>Email id</td>
<td>Mobile</td>
</tr>
<?php
$i=0;
while($row = mysqli_fetch_array($result)) {
?>
<tr>
<td><?php echo $row["name"]; ?></td>
<td><?php echo $row["email"]; ?></td>
<td><?php echo $row["mobile"]; ?></td>
</tr>
<?php
$i++;
}
?>
</table>
<?php
}
else{
echo "No result found";
}
?>
</div>
</div>
</div>
</div>
</body>
</html>
Conclusion
To retrieve or fetch or get the data from the MySQL database in PHP. Here you have learned how to fetch and display data in an HTML table using PHP MySQL.
This is a very basic and easy example of fetching the data into a MySQL database using PHP script. In the next tutorial, we will show you how you can update from data into the MySQL database in PHP.
Recommended PHP Tutorials
If you have any questions or thoughts to share, use the comment form below to reach us.
