Skip to main content

Export Data to CSV File with PHP and MySQL

Updated on: webdamn PHP, Tutorials

Comma Separated Values (CSV) is a popular file format to store tabular data into plain text file. The each line of the CSV file is a data record and each record consists of one or more fields, separated by commas. The data stored in CSV file can easily be imported to and exported from application.

If you’re thinking about to implement data export to CSV with PHP, then you’re here at right place. As in our previous tutorial you have learned how to export data to excel with PHP. In this tutorial you will learn how to Export data to CSV File using PHP and MySQL.

We will cover this tutorial in easy steps to implement live example to export data to csv using PHP and MySQL

Also, read:

So let’s start implementing data export to CSV using PHP and MySQL. Before we begin, take a look on files structure for this example:

  • index.php
  • export_data.php

Step1: Create MySQL Database Table

As we will implement live example to export data to CSV file, so first we will create table developers to store developer data.

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
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

We will insert some records into developers table to export. 


INSERT INTO `developers` (`id`, `name`, `skills`, `address`, `gender`, `designation`, `age`) VALUES
(1, 'Smith', 'Java', 'Newyork', 'Male', 'Software Engineer', 34),
(2, 'David', 'PHP', 'London', 'Male', 'Web Developer', 28),
(3, 'Rhodes', 'jQuery', 'New Jersy', 'Male', 'Web Developer', 30),
(4, 'Sara', 'JavaScript', 'Delhi', 'Female', 'Web Developer', 25),
(5, 'Shyrlin', 'NodeJS', 'Tokiyo', 'Female', 'Programmer', 35),
(6, 'Steve', 'Angular', 'London', 'Male', 'Web Developer', 28),
(7, 'Cook', 'MySQL', 'Paris', 'Male', 'Web Developer', 26),
(8, 'Root', 'HTML', 'Paris', 'Male', 'Web Developer', 28),
(9, 'William', 'jQuery', 'Sydney', 'Male', 'Web Developer', 23),
(10, 'Nathan', 'PHP', 'London', 'Male', 'Web Developer', 28),
(11, 'Shri', 'PHP', 'Delhi', 'Male', 'Web Developer', 38),
(12, 'Jay', 'PHP', 'Delhi, India', 'Male', 'Web Developer', 30);

Step2: Get Records from MySQL Database

First we will get records from MySQL database table developers and store records into an array to display developers records and also to export into CSV file.

<?php
include_once("inc/db_connect.php");
$sqlQuery = "SELECT name, gender, address, designation, age FROM developers";
$resultSet = mysqli_query($conn, $sqlQuery) or die("Database Error:". mysqli_error($conn));
$developers = array();
while( $developer = mysqli_fetch_assoc($resultSet) ) {
	$developers[] = $developer;
}	
?>

Step5: Display Records into HTML Table

In index.php file, we will display developers records into Bootstrap HTML table. We will also create Form with submit type data 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="csvExport" name='csvExport' 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($developers as $developer) { ?>
		   <tr>
		   <td><?php echo $developer ['name']; ?></td>
		   <td><?php echo $developer ['gender']; ?></td>
		   <td><?php echo $developer ['age']; ?></td>   
		   <td><?php echo $developer ['designation']; ?></td>
		   <td><?php echo $developer ['address']; ?></td>   
		   </tr>
		<?php } ?>
	</tbody>
</table>

Step3: Implement Data Export to CSV with PHP

Now in export_data.php file, we will implement data export functionality to export data to CSV file using developers array. The data export started when export Form submitted and exported data downloaded into CSV file.

<?php
if(isset($_POST["csvExport"])) {	
	$csvFile = "webdamn_csv_file_".date('Ymd') . ".csv";			
	header("Content-Type: text/csv");
	header("Content-Disposition: attachment; filename=\"$csvFile\"");	
	$FH = fopen( 'php://output', 'w' );
	$isColoumn = true;
	if(!empty($developers)) {
	  foreach($developers as $developer) {
		if($isColoumn) {		  	  
		  fputcsv($FH, array_keys($developer));
		  $isColoumn = false;
		}		
		fputcsv($FH, array_values($developer));
	  }
	   fclose($FH);
	}
	exit;  
}
?>

You may also like:

You can view the live demo from the Demo link and can download the script from the Download link below.
Demo Download