This article will show you how to produce a CSV file from MySQL data. We may build a CSV file with data using several built-in PHP functions as PHP fopen() and fputcsv(). This guide will come in handy for any future tasks that require CSV exportation.
We’ll make a small PHP application with dummy MySQL data and an anchor/button that causes the data to be exported to a CSV file.
The First Steps
To execute our PHP script, install XAMPP as your local web server. Open the XAMPP Control Panel after installing the virtual server and start the Apache Server and MySQL.
For the UI design of the app that we’ll be making, download Bootstrap v5. Then, on your end, copy the library directory to the location where you’ll keep the source code.
Developing a Database
Open a new tab in your browser and go to the PHPMyAdmin section of XAMPP. Create a new database called dummy_db next. Then, navigate to the SQL Tab/Page and paste the SQL Script below into the text space given. Finally, press the Go Button to run the script.
CREATE TABLE `authors` (
`id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`first_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`last_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`email` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`birthdate` date NOT NULL,
`added` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES
(1, 'Emily', 'Hegmann', 'janis.walker@example.org', '1979-03-16', '1976-04-05 11:59:54'),
(2, 'Miracle', 'Anderson', 'grady.arlo@example.net', '1975-02-02', '2006-04-13 11:34:40'),
(3, 'Kendra', 'Raynor', 'lexi.tremblay@example.net', '1979-03-19', '1987-11-17 14:51:03'),
(4, 'Simone', 'Huel', 'nikolas.little@example.net', '1971-09-30', '2006-05-13 21:56:04'),
(5, 'Ena', 'Harris', 'vicky.flatley@example.org', '2010-10-27', '2004-12-06 06:04:25'),
(6, 'Emiliano', 'Kshlerin', 'sigmund.schoen@example.org', '1979-04-20', '2009-12-10 05:42:56'),
(7, 'Jedediah', 'Hermiston', 'jankunding@example.org', '1976-08-01', '1997-09-06 03:35:39'),
(8, 'Garfield', 'Terry', 'darrion15@example.net', '1974-02-09', '1979-07-15 10:44:20'),
(9, 'Rhea', 'Keebler', 'brandon81@example.net', '2020-08-27', '2007-11-01 16:06:43'),
(10, 'Reid', 'Hegmann', 'torphy.erika@example.net', '2008-03-15', '1983-07-07 20:01:45');Creating The Database Connection
Next, create a new PHP File and save it as dummy_db.sql. This file will contain the PHP Script that connects to the MySQL Database.
<?php
$host = 'localhost';
$username = 'root';
$password = '';
$dbname ='dummy_db';
$conn = new mysqli($host, $username, $password, $dbname);
if(!$conn){
die("Cannot connect to the database.". $conn->error);
}Designing the User Interface
The Main Page’s Script, which contains the HTML Code for the items to be displayed to end-users, is shown below. It also has a link/button for exporting the data to CSV. This file should be saved as index.php.
<?php require_once('db-connect.php') ?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Export Data to CSV</title>
<link rel="stylesheet" href="./Font-Awesome-master/css/all.min.css">
<link rel="stylesheet" href="./css/bootstrap.min.css">
<script src="./js/bootstrap.min.js"></script>
<style>
:root {
--bs-success-rgb: 71, 222, 152 !important;
}
html,
body {
height: 100%;
width: 100%;
font-family: Apple Chancery, cursive;
}
.btn-info.text-light:hover,
.btn-info.text-light:focus {
background: #000;
}
</style>
</head>
<body class="bg-light">
<nav class="navbar navbar-expand-lg navbar-dark bg-dark bg-gradient" id="topNavBar">
<div class="container">
<a class="navbar-brand" href="https://sourcecodester.com">
Sourcecodester
</a>
<div>
<b class="text-light">Export Data to CSV</b>
</div>
</div>
</nav>
<div class="container py-5" id="page-container">
<h4 class="text-center"><b>List of Authors</b></h4>
<hr>
<div class="row justify-content-end my-2">
<div class="col-md-4 text-end">
<a href="export.php" class="btn btn-primary rounded-0 shadow" target="_blank">Export to CSV</a>
</div>
</div>
<!-- Authors Table -->
<table class="table table-striped table-bordered">
<colgroup>
<col width="5%">
<col width="20%">
<col width="20%">
<col width="20%">
<col width="20%">
<col width="15%">
</colgroup>
<thead>
<tr class="bg-gradient bg-primary text-light">
<th class="text-center">ID</th>
<th class="text-center">Firstname</th>
<th class="text-center">Lastname</th>
<th class="text-center">Email</th>
<th class="text-center">Birthday</th>
<th class="text-center">Date Added</th>
</tr>
</thead>
<tbody>
<?php
// Selecting all authors in the database
$qry = $conn->query("SELECT * FROM `authors` order by id asc ");
// looping list of authos into a table rows
while($row = $qry->fetch_assoc()):
?>
<tr>
<td class="text-center"><?= $row['id'] ?></td>
<td><?= ucwords($row['first_name']) ?></td>
<td><?= ucwords($row['last_name']) ?></td>
<td><?= $row['email'] ?></td>
<td><?= date("M d, Y",strtotime($row['birthdate'])) ?></td>
<td><?= date("Y-m-d H:i",strtotime($row['added'])) ?></td>
</tr>
<?php endwhile; ?>
</tbody>
</table>
<!-- Authors Table -->
</div>
<?php
if(isset($conn)) $conn->close();
?>
</body>
</html>Developing The Main Function
Finally, create a new PHP file with the name export.php. This file contains the PHP Codes for exporting the MySQL Data into a CSV File.
<?php
// Load the database connection
require_once('db-connect.php');
//CSV Filename
$fname = 'author_list.csv';
// Header Row Data: Array
$header = ["ID","First Name", "Last Name", "Email", "Birthday", "Date Added"];
// Selecting all authors query
$qry = $conn->query("SELECT * FROM `authors` order by `id` asc");
if($qry->num_rows <= 0){
echo "<script> alert('No data has fetched.'); location.href = './'; </script>";
exit;
}
//Open a File
$file = fopen("php://memory","w");
// Attach Header
fputcsv($file, $header,',');
// Loop the authors and put it into the CSV file
while($row = $qry->fetch_assoc()){
fputcsv($file, [$row['id'], $row['first_name'], $row['last_name'], $row['email'], date("M d, Y",strtotime($row['birthdate'])), date("Y-m-d H:i",strtotime($row['added']))],',');
}
fseek($file,0);
// Add headers to download the file
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="'.$fname.'";');
// Read File
fpassthru($file);
exit;Done!
Itís difficult to find educated people in this particular topic, however, you seem like you know what youíre talking about! Thanks
I was very happy to discover this website. I want to to thank you for your time due to this fantastic read!! I definitely liked every little bit of it and i also have you book marked to check out new things on your blog.