Why we Export MySQL database data into Excel file?
This export procedure we use for taking database backup. We can use this backup in the future for numerous purposes. For example, this will help you to reuse backup for restoring the database in the future. Database backup and restore is the most important part of maintaining software. Backup files can be in any format. You may see many of the database clients supports SQL, Excel, or CSV format.
In this tutorial, we are going to learn about how to export MySQL database data into Excel file using PHP Each PHP developer need to deal with export MySQL database data into Excel file. We can also achieve this export procedure through programming In PHP, the code operation of creating a database export tool is simple.
In this example, we are going to build PHP code for taking backup by exporting database data into an excel file.
An example image of exporting MySQL database Data into Excel File.
To export data from MySQL Database table to excel file. I am doing the following steps using PHP.
STEPS
We need to follow the following steps below.
Create directory.
Example image for the directory.
It is a good practice to create the required folders on your computer so you can proceed and manage it easily.
- CSS folder for custom styling of HTML structure.
- Includes has the database connection file with name conn.php.
- The classes’ folder has the Libraries to render table data with export options.
Once you have created the required folders it is time to create your HTML structure and writing the script to export database data into the Excel file.
- Launch your text editor and create a new file and save it as "index.php" in your main folder.
- Create a new file and save it as "style.css" in your CSS folder.
Create a Database and a Table with name students.
Open your phpMyAdmin and create a database with the name (school) and create a table (students) in it using the below query.
CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(255) NOT NULL,
`last_name` varchar(255) NOT NULL,
`age` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
We have created a simple table in the database with the columns like id,first_name, last_name, and age. After creating the table in the database you need to add some dummy entries in it.
Create a Database connection.
Create a conn.php file and write a below code for database connection and save it in your includes folder.
<?php
$con=mysqli_connect("localhost","root","","school");
?>
Make sure to replace the placeholders with the actual values.
Now we are going to fetch records from MySQL database using select query in (index.php) file and an html table to display the fetched records of students.
<?php
// creat conection with database for getting records.
include "includes/conn.php";
?>
<div class="bootstrap-data-table-panel">
<div class="table-responsive">
<!-- Data list table -->
<table class="table datatable table-striped table-bordered">
<thead>
<tr>
<th>Id</th>
<th>First Name</th>
<th>Last Name</th>
<th>age</th>
</tr>
</thead>
<tbody>
<?php
// fetch data from the database
$query= mysqli_query($con,"SELECT * from students");
foreach($query as $val){
?>
<tr>
<td><?php echo $val['id']; ?></td>
<td><?php echo $val['first_name']; ?></td>
<td><?php echo $val['last_name']; ?></td>
<td><?php echo $val['age']; ?></td>
</tr>
<?php
}
?>
</tbody>
</table>
</div>
</div>
Table Image after fetching the records from the Database.
Now we are going to create HTML form to Export MySQL database data into Excel file.
<div class="col-lg-6">
<form action="" method="post">
<input type="submit" value="Export Data to Excel" name="export" class="btn btn-success" />
</form>
</div>
Image after creating HTML table and HTML form.
Now we are going to write a php code to export MySQL database table data into Excel file. But we have some security issues to write this PHP script here. you can download this script by clicking the below link.
Click me to download The PHP Script
After downloading the PHP script you need to copy this script and paste it before opening <html> Tag in your index.php file.
After adding the PHP script you can export your MySQL database data into an excel file by click on (export to excel) button that we have already made in the HTML form section. After exporting the data into excel file your excel file looks like the below image.
Excel file Image.
Here is a complete script to Export MySQL database data into Excel file using PHP including HTML form and HTML table to display the records that we have Export.
<?php
// creat conection with database
include "includes/conn.php";
// Here you need to add the downloaded PHP script.
?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>How to import Excel file data into MySQL database using PHP</title>
<!-- Bootstrap library -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
<!-- Stylesheet file -->
<link rel="stylesheet" href="css/style.css">
<div class="content-wrap">
<div class="main">
<div class="container-fluid">
<!-- /# row -->
<div id="main-content" >
<div class="row">
<div class="col-lg-12">
<div class="card alert">
<div class="card-header">
<h4>Export Data To Excel File</h4>
<div class="card-header-right-icon">
</div>
</div>
<div class="bootstrap-data-table-panel">
<div class="table-responsive">
<!-- Data list table -->
<table class="table datatable table-striped table-bordered">
<thead>
<tr>
<th>Id</th>
<th>First Name</th>
<th>Last Name</th>
<th>age</th>
</tr>
</thead>
<tbody>
<?php
// fetch data from the database
$query= mysqli_query($con,"SELECT * from students");
foreach($query as $val){
?>
<tr>
<td><?php echo $val['id']; ?></td>
<td><?php echo $val['first_name']; ?></td>
<td><?php echo $val['last_name']; ?></td>
<td><?php echo $val['age']; ?></td>
</tr>
<?php
}
?>
</tbody>
</table>
</div>
</div>
<div class="col-lg-6">
<form action="" method="post">
<input type="submit" value="Export Data To Excel" name="export" class="btn btn-success" />
</form>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</body>
</html>
The Bootstrap library is used to styling the Table, Form, and Buttons.
Other Related Posts
How to Import CSV File data into MySQL Database
How to Import Excel File Data into MySQL Database
How to Export database table data into CSV file
After adding the above code here is the final output after exporting the database data into Excel file.
Final Output Image.
We hope you may learn how to export MySQL database data into Excel file using PHP. Please share your thoughts and queries in the contact section.
Below is the Download link of the above tutorial.