Export Data to CSV Using PHP and MySQL

The mostly used and common file format for storing data in plain text is CSV (comma-separated values). Many times, We use a CSV file to import and export data between programs. In web applications, Data import and Data export is very common feature and we mostly use CSV for this common feature. It is also used for transferring tabular data between applications. In this article, We will learn about how we export data to CSV using PHP and how to download CSV file.

Step-by-Step Guide

To achieve Export data to CSV feature, we will follow some points, which is written below:

1. Database Connection

First, we need to establish a database connection to get data from MySQL using PHP. So, Create a file name “db_connect.php”

<?php
$dbHost     = "localhost"; 
$dbUsername = "root"; 
$dbPassword = "root"; 
$dbName     = "employee_details"; 
 
// Create database connection 
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName); 
 
// Check connection 
if ($db->connect_error) 
{ 
    die("Connection failed: " . $db->connect_error); 
}

?>

Here, employee_details is our database. You can change with your database name.

2. Get Data From Database

Now, we are going to fetch our emplyees records from database employee_details and displaying on our web page in tabular format.

In this web page, we will make a button, where we can click and export this tabular data in CSV file.

Create a file “index.php”

<?php
include_once 'dbconfig.php';
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Export Data to CSV Using PHP</title>
	<!-- Bootstrap library -->
	<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
</head>
<body>
	<div class="row">
		<div class="col-md-12 head">
			<div class="float-right">
				<a href="exportData.php" class="btn btn-success"><i class="dwn"></i> Export CSV</a>
			</div>
		</div>
	</div>
	
	<!-- Data list table --> 
	<table class="table table-striped table-bordered">
		<thead class="thead-dark">
			<tr>
				<th>#ID</th>
				<th>Name</th>
				<th>Email</th>
				<th>City</th>
				<th>State</th>
				<th>Created On</th>
			</tr>
		</thead>
		<tbody>
			<?php 
			// Fetch records from database 
			$result = $db->query("SELECT * FROM employee ORDER BY id ASC"); 
			if($result->num_rows > 0)
			{ 
				while($row = $result->fetch_assoc())
				{ 
			?>
			<tr>
					<td><?= $row['id']; ?></td>
					<td><?= $row['name']; ?></td>
					<td><?= $row['email']; ?></td>
					<td><?= $row['city']; ?></td>
					<td><?= $row['state']; ?></td>
					<td><?= $row['created_on']; ?></td>
			</tr>
			<?php 
				} 
			}
			else
			{ 
			?>
				<tr><td colspan="6">No employee found...</td></tr>
			<?php 
			} 
			?>
		</tbody>
	</table>
	
</body>
</html>

Also read about Google PSI

3. Export CSV Button Click

Now, make a file export_csv.php to handle export these data to CSV file and after click on export csv button, it will download.

<?php
// Load the database configuration file 
include_once 'dbconfig.php'; 
$query = $db->query("SELECT * FROM employee ORDER BY id ASC"); 
 
if($query->num_rows > 0)
{ 
    $delimiter = ","; 
    $filename = "employee_records.csv"; 
     
    // Create a file pointer 
    $f = fopen($filename, 'w');
     
    // Set column headers 
    $fields = array('ID', 'NAME', 'EMAIL', 'CITY', 'STATE', 'CREATED ON'); 
    fputcsv($f, $fields, $delimiter); 
     
    // Output each row of the data, format line as csv and write to file pointer 
    while($row = $query->fetch_assoc())
	{         
        $lineData = array($row['id'], $row['name'], $row['email'], $row['city'], $row['state'], $row['created_on']); 
        fputcsv($f, $lineData, $delimiter); 
    } 
     
    // Move back to beginning of file 
    fseek($f, 0); 
     
    // Set headers to download file rather than displayed 
    header('Content-Type: text/csv'); 
    header('Content-Disposition: attachment; filename="' . $filename . '";'); 
	header("Pragma: no-cache");
    header("Expires: 0");
    //output all remaining data on a file pointer 
    fpassthru($f); 
}  
 
?>

Now, we opens our browser to load index.php page. After that, we click on Export CSV link. Our browser will download a CSV file where our all records will show, which we get from our MySQL database.

The procedure of exporting data to CSV with MySQL and PHP is simple. This article will walk you through the process of writing a script that will export your database entries into a CSV file, making data editing and sharing simple. Adapt the script as necessary to your own database schema and needs.

Categorized in:

PHP