Table of Contents
Tutorial: Export MySQL Database into CSV File using PHP/MySQLi with Source Code
Getting Started on How to Export MySQL Database into CSV File
This is a tutorial on how to Export MySQL Database into CSV File. I’ve used bootstrap to improve the design of the presentation of this tutorial. This bootstrap is included in the downloadable of this tutorial but, if you want, you may download bootstrap using this link.
Related Tutorials: Fetch Data from MySQL Database using Vuejs in PHP, How to Fetch MySQL Data Using AngularJS in PHP
Creating a Database
Next, we create our MySQL database that we are going to export into CSV file.
I’ve included a .sql file in the downloadable of this tutorial which is a MySQL database file. All you have to do is import the said file. If you have no idea on how to do this, please refer to my tutorial, How import .sql file to restore MySQL database.
You should be able to create a database with tables named database.
Creating a Link to Export
Next, we are going to create a link that directs to our export code. We can do this by creating our index file named index.php. Also, I’ve included a table that shows the data that we are going to export.
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Export MySQL Database into CSV File using PHP/MySQLi</title>
<link rel="stylesheet" type="text/css" href="bootstrap/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<h1 class="page-header text-center">Export MySQL Database into CSV File</h1>
<div class="row">
<div class="col-sm-8 col-sm-offset-2">
<div class="row">
<div class="col-sm-1">
<a href="export.php" class="btn btn-primary btn-sm">Export</a>
</div>
<div class="col-sm-11">
<?php
session_start();
if(isset($_SESSION['message'])){
echo $_SESSION['message'];
unset($_SESSION['message']);
}
?>
</div>
</div>
<table class="table table-bordered table-striped" style="margin-top:20px;">
<thead>
<th>UserID</th>
<th>Firstname</th>
<th>Lastname</th>
<th>Address</th>
</thead>
<tbody>
<?php
//connection
$conn = new mysqli('localhost', 'root', '', 'mydatabase');
$sql = "SELECT * FROM members";
$query = $conn->query($sql);
while($row = $query->fetch_array()){
?>
<tr>
<td><?php echo $row['id']; ?></td>
<td><?php echo $row['firstname']; ?></td>
<td><?php echo $row['lastname']; ?></td>
<td><?php echo $row['address']; ?></td>
</tr>
<?php
}
?>
</tbody>
</table>
</div>
</div>
</div>
</body>
</html>Creating an Export Script
Lastly, we create our export script by creating a new file, name it as export.php and paste the below code.
<?php
session_start();
//connection
$conn = new mysqli('localhost', 'root', '', 'mydatabase');
$sql = "SELECT * FROM members";
$query = $conn->query($sql);
if($query->num_rows > 0){
$delimiter = ',';
//create a download filename
$filename = 'members.csv';
$f = fopen('php://memory', 'w');
$headers = array('Id', 'Firstname', 'Lastname', 'Address');
fputcsv($f, $headers, $delimiter);
while($row = $query->fetch_array()){
$lines = array($row['id'], $row['firstname'], $row['lastname'], $row['address']);
fputcsv($f, $lines, $delimiter);
}
fseek($f, 0);
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="' . $filename . '";');
fpassthru($f);
exit;
}
else{
$_SESSION['message'] = 'Cannot export. Data empty';
header('location:index.php');
}
?>That ends this tutorial. Happy Coding!
