A step-by-step guide to export data to CSV from MySQL using PHP
It is a basic task for any application that needs a reporting feature to CSV; here I am going to explain how to generate a CSV file from the MySQL records.
Let’s get started:
Step 1 – Create Sample Database and Required Table along with the data:
Create Database table with sample records, here I am going to use a users table, which is going to have users details, go ahead and use following SQL statements to create table and insert the records. (Note: you can skip this step if you have your existing database tables)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(40) NOT NULL,
`last_name` varchar(40) NOT NULL,
`email` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO `users` (`id`, `first_name`, `last_name`, `email`) VALUES
(1, 'Jerry', 'McKenny', 'jerrymckenny@gmail.com'),
(2, 'Lillian', 'Evans', 'lillianevans@gmail.com'),
(3, 'Eva', 'Osborne', 'evaosborne@gmail.com'),
(4, 'Darlene', 'Edwards', 'darleneedwards@gmail.com'),
(5, 'Bill', 'White', 'billwhite@gmail.com');
|
Users Table Structure
Existing Mysql Records to Export
Step 2 Create PHP to MySQL Database Connection Script:
Create Database configuration file, which is going help us to connect to MySQL from PHP file.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
<?php
/*
* iTech Empires: Export Data from MySQL to CSV Script
* Version: 1.0.0
* Page: DB Connection
*/
// Connection variables
$host = "localhost"; // MySQL host name eg. localhost
$user = "root"; // MySQL user. eg. root ( if your on localserver)
$password = ""; // MySQL user password (if password is not set for your root user then keep it empty )
$database = "test"; // MySQL Database name
// Connect to MySQL Database
$con = new mysqli($host, $user, $password, $database);
// Check connection
if ($con->connect_error) {
die("Connection failed: " . $con->connect_error);
}
?>
|
Step 3 – Add Sample Index Page to List out the Records:
Create index.php file and list records.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
|
<?php
/*
* iTech Empires: Export Data from MySQL to CSV Script
* Version: 1.0.0
* Page: Index
*/
// Database Connection
require("db_connection.php");
// List Users
$query = "SELECT * FROM users";
if (!$result = mysqli_query($con, $query)) {
exit(mysqli_error($con));
}
if (mysqli_num_rows($result) > 0) {
$number = 1;
$users = '<table class="table table-bordered">
<tr>
<th>No.</th>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
</tr>
';
while ($row = mysqli_fetch_assoc($result)) {
$users .= '<tr>
<td>'.$number.'</td>
<td>'.$row['first_name'].'</td>
<td>'.$row['last_name'].'</td>
<td>'.$row['email'].'</td>
</tr>';
$number++;
}
$users .= '</table>';
}
?>
<!doctype html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Export Data from MySQL to CSV Tutorial | iTech Empires</title>
<!-- Bootstrap CSS File -->
<link rel="stylesheet" type="text/css" href="bootstrap/css/bootstrap.min.css"/>
</head>
<body>
<div class="container">
<!-- Header -->
<div class="row">
<div class="col-md-12">
<h2>Export Data from MySQL to CSV</h2>
</div>
</div>
<!-- /Header -->
<!-- Content -->
<div class="form-group">
<?php echo $users ?>
</div>
<div class="form-group">
<button onclick="Export()" class="btn btn-primary">Export to CSV File</button>
</div>
<!-- /Content -->
<script>
function Export()
{
var conf = confirm("Export users to CSV?");
if(conf == true)
{
window.open("export.php", '_blank');
}
}
</script>
</div>
</body>
</html>
|
Step 4- Exporting MySQL Data to CSV file PHP Script:
Create export.php file to export data from Mysql to CSV.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
<?php
/*
* iTech Empires: Export Data from MySQL to CSV Script
* Version: 1.0.0
* Page: Export
*/
// Database Connection
require("db_connection.php");
// get Users
$query = "SELECT * FROM users";
if (!$result = mysqli_query($con, $query)) {
exit(mysqli_error($con));
}
$users = array();
if (mysqli_num_rows($result) > 0) {
while ($row = mysqli_fetch_assoc($result)) {
$users[] = $row;
}
}
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=Users.csv');
$output = fopen('php://output', 'w');
fputcsv($output, array('No', 'First Name', 'Last Name', 'Email'));
if (count($users) > 0) {
foreach ($users as $row) {
fputcsv($output, $row);
}
}
?>
|
Click OK to export the data to CSV and your file get’s downloaded
If you check download file, you get records listed, as showing below:
Records from CSV files
We are done, you can download the complete source for reference use following links to download or checkout live demo.
