Export Data to CSV with PHP and MySQL

Recently we have published tutorial to Export Data to Excel with PHP and MySQL and get huge response from our readers. Many of our users requested to also publish tutorial to export data to CSV using PHP and MySQL. The CSV (comma separated values) format is the most popular file format to use for data export and import functionality. Exporting data in CSV file format is also useful to allow users to save data for offline use. So in this tutorial you will learn how to export data to CSV with PHP and MySQL. The tutorial explained in easy steps with live demo and link to download source code.



As we have covered this tutorial with live demo to export data to CSV file with PHP and MySQL, so the file structure for this example is following.

• index.php


• export.php

Steps1: Create MySQL Database Table
As we are going to handle this tutorial with example to export data in CSV format from MySQL database table. So first we will create MySQL database table developers using below query.

CREATE TABLE `developers` (
`id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`skills` varchar(255) NOT NULL,
`address` varchar(255) NOT NULL,
`gender` varchar(255) NOT NULL,
`designation` varchar(255) NOT NULL,
`age` int(11) NOT NULL,
`image` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


We will also insert few records into table to display these and export these into CSV file.

INSERT INTO `developers` (`id`, `name`, `skills`, `address`, `gender`, `designation`, `age`, `image`) VALUES
(1, 'Smith', '', 'Newyork', 'Male', 'Software Engineer', 34, ''),
(2, 'Steve', '', 'London', 'Female', 'Web Developer', 28, ''),
(3, 'Jhon', '', 'Delhi, India', 'Male', 'Web Developer', 30, '');



Steps2: Get Records from MySQL Database Table
We will get records from MySQL database table developers with PHP and store into an array to display records and also export records to CSV file.
$query = "SELECT name, gender, address, designation, age FROM developers LIMIT 10";
$result = mysqli_query($conn, $query) or die("database error:". mysqli_error($conn));
$records = array();
while( $rows = mysqli_fetch_assoc($result) ) {
$records[] = $rows;
}



Steps3: Display Records with Export Button
Now in index.php file, we will display developer records with export button.
<div class="well-sm col-sm-12">
<div class="btn-group pull-right">
<form action="<?php echo $_SERVER["PHP_SELF"]; ?>" method="post">
<button type="submit" id="export_csv_data" name='export_csv_data' value="Export to CSV" class="btn btn-info">Export to CSV</button>
</form>
</div>
</div>
<table id="" class="table table-striped table-bordered">
<tr>
<th>Name</th>
<th>Gender</th>
<th>Age</th>
<th>Designation</th>
<th>Address</th>
</tr>
<tbody>
<?php foreach($records as $record) { ?>
<tr>
<td><?php echo $record ['name']; ?></td>
<td><?php echo $record ['gender']; ?></td>
<td><?php echo $record ['age']; ?></td>
<td><?php echo $record ['designation']; ?></td>
<td><?php echo $record ['address']; ?></td>
</tr>
<?php } ?>
</tbody>
</table>



Steps4: Export Data to CSV with PHP
Now finally in export.php file, we will handle functionality to export records into CSV file using PHP.
if(isset($_POST["export_csv_data"])) {
$csv_file = "phpzag_csv_export_".date('Ymd') . ".csv";
header("Content-Type: text/csv");
header("Content-Disposition: attachment; filename=\"$csv_file\"");
$fh = fopen( 'php://output', 'w' );
$is_coloumn = true;
if(!empty($records)) {
foreach($records as $record) {
if($is_coloumn) {
fputcsv($fh, array_keys($record));
$is_coloumn = false;
}
fputcsv($fh, array_values($record));
}
fclose($fh);
}
exit;
}


You can view the live demo from the Demo link and can download the script from the Download link below.
Demo [sociallocker]Download[/sociallocker]