To export MySQL data to CSV using PHP, we need to follow the below process:
- Suppose you have a database with a table (let’s say a ‘user‘ table for an example).
- Create your project folder(e.g. ‘ExportCSV‘). If you are working on local not server then you can use XAMPP for windows and LAMP for Ubuntu. In XAMPP you will have a folder ‘htdocs‘, you need to place your project folder ‘ExportCSV‘ under ‘htdocs‘.
- Create three PHP files, one for database configurations i.e. ‘db_connection.php‘, second is ‘index.php‘ for initial page with fetched data and third is for exporting data into CSV i.e. ‘export.php‘. Put all these files under the folder ‘ExportCSV‘.
Step 1 : ‘User‘ table must have few data. Please see the example of table below:
ID | FullName | |
1 | John Doe 1 | johndoe1@thetechnicaldiaries.com |
2 | John Doe 2 | johndoe2@thetechnicaldiaries.com |
Step 2 : Open file ‘db_connection.php’ in code editor and write below configuration code:
<?php
// Database configuration
//$db_host : database host
//$db_username : database username
//$db_password : database password
//$db_name : database name
$db_host= "localhost";
$db_username= "root";
$db_password= "";
$db_name = "demo";
// Create database connection
$db = new mysqli($db_host, $db_username, $db_password, $db_name);
// Check connection
if ($db->connect_error) {
die("Connection failed: " . $db->connect_error);
}
Step 3 : Open file ‘index.php‘ in code editor and first include the ‘db_connection.php‘ file at the top, to include db-connection. And then write code to fetch data from ‘user‘ table to show in tabular format.
<?php
// include the database configuration file
include_once 'db_connection.php';
?>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
<!-- Export CSV link -->
<a href="export.php">Export CSV</a>
<!-- Data list in tabular format -->
<table class="table table-striped table-bordered">
<thead>
<tr>
<th>ID</th>
<th>FullName</th>
<th>Email</th>
</tr>
</thead>
<tbody>
<?php
// Fetch records from user table
$result = $db->query("SELECT * FROM user");
if($result->num_rows > 0){
while($row = $result->fetch_assoc()){
?>
<tr>
<td><?php echo $row['ID']; ?></td>
<td><?php echo $row['FullName']; ?></td>
<td><?php echo $row['Email']; ?></td>
</tr>
<?php } }else{ ?>
<tr><td colspan="3">No user...</td></tr>
<?php } ?>
</tbody>
</table>
Step 4 : Open file ‘export.php‘ and write following codes to export the data into csv:
<?php
// include the database configuration file
include_once 'db_connection.php';
// Fetch records from user table
$query = $db->query("SELECT * FROM user");
if($query->num_rows > 0){
$delimiter = ",";
$filename = "usercsv_" . date('Y-m-d') . ".csv";
// Create a file pointer
$f = fopen('php://memory', 'w');
// Set column headers
$fields = array('ID', 'FullName', 'EMAIL');
fputcsv($f, $fields, $delimiter);
while($row = $query->fetch_assoc()){
$data = array($row['ID'], $row['FullName'], $row['email']);
fputcsv($f, $data, $delimiter);
}
fseek($f, 0);
// Set headers to download file
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="' . $filename . '";');
fpassthru($f);
}
?>
In the above example, you will be able to export csv. You can customize it according to your logic and designing.