What is CSV file And why we use it?
In this tutorial, we are going to learn how to export MySQL table data into CSV file using PHP. CSV (Comma Separated Values) file stores the plane data in plain text format. This export procedure we use for taking database backup. Every PHP developer needs to deal with Export MySQL table data into a CSV file for backup purposes. This will help you to reuse backup for restoring the database in the future.
In this example, we are going to build PHP code for taking backup by exporting database data into a CSV file.
An example image of exporting MySQL database Data into CSV File.
To export data from MySQL Database table to CSV 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.
Once you have created the required folders it is time to create your HTML structure and writing the script to export MySQL database data into CSV 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 in it 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
// create connection 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 table data into CSV file.
<!-- form to export data into csv-->
<div class="col-lg-12">
<form action="" method="post">
<input type="submit" value="Export As CSV" name="exp" 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 CSV 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.
Now you can export your MySQL database table data into CSV file by clicking on (export As CSV) button that we have already made in the HTML form section. After exporting the data into CSV file your CSV file looks like the below image.
CSV file Image.
Other Related Posts
How to Import CSV File data into MySQL Database
How to Import Excel File Data into MySQL Database
Here is a complete script to Export MySQL database table data into CSV file using PHP including HTML form and HTML table to display the records that we have Export.
<?php
// create connection with database
include "includes/conn.php";
// Here you need to write the downloaded PHP script
?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>How to Export MySQL database data into CSV file 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 CSV 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>
<!-- form to export data into csv-->
<div class="col-lg-12">
<form action="" method="post">
<input type="submit" value="Export As CSV" name="exp" 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.
After adding the above code here is the final output after exporting the database data into the CSV file.
Final Output Image.
We hope you may learn how to export MySQL database table data into CSV file using PHP. Please share your thoughts and queries in the contact section.
Below is the Download link of the above tutorial.
Click here to download full source code