Export database record into csv file in PHP and MySQL

Export database record into csv file in PHP and MySQL Free Source code and Tutorial

Introduction

Data export is the process of converting data from one computer application to another. Data export is often used to move data from one database to another or from one format to another.

There are a number of reasons why you might want to export data. For example, you might want to move data from an old database to a new one , or you might want to migrate data from one system to another.

There are a number of different ways to export data. You can export data as a text file, an Excel file, or a CSV file. You can also export data in various formats, including SQL, XML, and JSON.

Exporting data from MySQL to a CSV file is a relatively simple process that can be accomplished in a number of ways. In this tutorial, we’ll discuss how to export data from a MySQL database to a CSV file using PHP.

Let’s get started with the development.

Objectives

By the end of this tutorial, you will be able to:

1. Create a PHP script that connects to MySQL database that allows the users to export the records from mysql server to .csv file.
2. Use PHP functions needed to export data from mysql to .csv file.
3. To integrate and apply the source code in your projects.

Relevant Source code

You’ll need a basic understanding of both PHP and MySQL to write a tutorial on exporting data using PHP and MySQL. You’ll also need access to a server that can run PHP and MySQL. Once you have these items, you may create your tutorial by following the instructions below.

• XAMPP
• Text editor (VS Code, Sublime, Brackets), download and install a text editor of your choice

Line 1-6 – first we need to connect to our mysql database server. For XAMPP, the default host is localhost, database username is root, there is no password and the name of the database is city.

<?php
$con = mysqli_connect("localhost","root","","city");
if(!$con){
die("Connection Failed: ".mysqli_connect_erro());
}
?>

city.sql

-- Table structure for table `tblcity`
CREATE TABLE `tblcity` (
`id` int(11) NOT NULL,
`city_name` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dumping data for table `tblcity`
INSERT INTO `tblcity` (`id`, `city_name`) VALUES
(1, 'Pasig'),
(2, 'Quezon'),
(3, 'Makati'),
(4, 'Taguig');

<a href="export.php" class="btn btn-success">Export to CSV</a>

Line 24 – this is the export button. We need to create a file and name it as export.php. The file contains the necessary functions to allow us to export the records from mysql to csv file.

Line 26-54 – The next thing to do is to populate our table. We will now retrieve the records from tblcity. For our table, we have two columns; id and city_name. This script will fetch the records from our database table and display it on our HTML table.

<table class="table table-striped table-bordered">
<thead>
<tr>
<th>#ID</th>
<th>City</th>
</tr>
</thead>
<tbody>
<?php
$result = $con->query("SELECT * FROM tblcity");
if($result->num_rows > 0){
while($row = $result->fetch_assoc()){?>
<tr>
<td><?php echo $row['id'];?></td>
<td><?php echo $row['city_name'];?></td>
</tr>
<?php 
}
}
else{ ?>
<tr>
<td colspan="7">No data Found...</td>
</tr>
</tbody>
<?php 
}
?>
</tbody>
</table>

Next step is to create the export.php file. Copy the source code below and paste it on the export.php file. Save it right next to the index.php. Please download the source code for the complete reference.

<?php
$con = mysqli_connect("localhost","root","","city");
if(!$con){
die("Connection Failed: ".mysqli_connect_erro());
}

$query = $con->query("SELECT * FROM tblcity");

if($query->num_rows > 0){
$delimiter = ",";
$filename = "exported-data_".date('Y-m-d').".csv";

$f = fopen('php://memory','w');

$fields = array('id','city_name');
fputcsv($f,$fields,$delimiter);

while($row = $query->fetch_assoc()){
$lineData = array($row['id'],$row['city_name']);
fputcsv($f,$lineData,$delimiter);
}

fseek($f,0);

header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="'. $filename .'";');

fpassthru($f);
}
exit;
?>

PHP functions used:

• fopen() – function opens a file or URL.
• array() – function is used to create an array.
• fputcsv() – function in PHP is an inbuilt function which is used to format a line as CSV(comma separated values) file and writes it to an open file.
• fseek() – function seeks in an open file. It returns 0 on success, else returns -1 on failure.
• fpassthru() – function reads from the current position in a file – until EOF, and then writes the result to the output buffer.header() – function sends a raw HTTP header to a client.

Video Demo

FREE DOWNLOAD SOURCE CODE

FREE DOWNLOAD PDF TUTORIAL

Summary

In computing, comma-separated values (CSV) file stores tabular data (numbers and text) in plain text. Each line of the file is a data record. Each record consists of one or more fields, separated by commas. The use of the comma as a field separator is the source of the name for the format. CSV files are commonly used for data transfers between programs and can be opened in read-only mode with a text editor, or in a spread sheet application.

In this tutorial, we have learned how to export data from a MySQL database table to a CSV file using PHP. We have first created a database table, insert some data into it, and then export the data to a CSV file using PHP.  We have also enumerated the different PHP functions used for us to export the mysql records into csv file.

We hope you found this tutorial to be helpful! Wishing you the best of luck with your projects! Happy Coding!

You may visit our Facebook page for more information, inquiries, and comments. Please subscribe also to our YouTube Channel to receive  free capstone projects resources and computer programming tutorials.

Hire our team to do the project.

Related Topics and Articles:

How to Generate Random Password in PHP Free Source code and Tutorial

Calculate Sum of Column in PHP and MySQL

Count Number of Characters in PHP Free Source code and Tutorial

Image Upload in PHP and MySQL Free Tutorial and Source code

Password Indicator in PHP Free Source code and Tutorial