So many time we need to export data from MySql to Excel or CSV. So many time we need to send reports in excel and we require to export data from MySql to Excel.
We can perform this task very easily in PHP. Here in this article I'm going to explain how we can export the MySql data into excel or CSV in PHP.
I will be writing single PHP script which will work for excel and CSV both. We can do it by just changing the headers of file.
Here I'm creating the users table in 'csv' MySql database. The 'users' table contains the data of users and we need to export this users data.
Here is MySql queries you just need to execute these queries in you database panel like phpmyadmin.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
CREATE DATABASE 'csv';
use 'csv';
CREATE TABLE `users` (
`id` int(10) UNSIGNED NOT NULL,
`name` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`phone` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `users` (`id`, `name`, `email`, `phone`) VALUES
(1, 'Coding 4 Developers', 'info@www.coding4developers.com', 99999999),
(2, 'Jobs Website', 'jobswebsite.in', 55555555),
(3, 'Saurabh', 'info@saurabh.com', 475454686);
ALTER TABLE `users`
ADD PRIMARY KEY (`id`);
ALTER TABLE `users`
MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
|
Here is the database connection file. Here we are creating MySql database connection with PHP.
1
2
3
4
5
6
7
8
9
|
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "csv";// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
|
The below code is used to list all the users.
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
|
<?php
include_once 'connection.php';
$sql = mysqli_query($conn,"SELECT `name`,`email`,`phone` FROM `users`");
$conn->close();
?>
<table border="1">
<tr>
<th>Sr NO.</th>
<th>Name</th>
<th>Email</th>
<th>Phone</th>
</tr>
<?php
$i=1;
while($data = mysqli_fetch_row($sql))
{
echo '
<tr>
<td>'.$i.'</td>
<td>'.$data[0].'</td>
<td>'.$data[1].'</td>
<td>'.$data[2].'</td>
</tr>
';
$i++;
}
?>
<a href="export_user.php"><h2> Export To Excel</h2> </a>
</table>
|
Below code is used to export the MySql data to Excel or CSV files.
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
|
<?php
include_once 'connection.php';
$exportType = '';
//$exportType = 'CSV';
$sql = mysqli_query($conn,"SELECT `name`,`email`,`phone` FROM `users`");
$conn->close();
$srNo=1;
$rowData ='';
$setData='';
while($rec = mysqli_fetch_row($sql))
{
$rowData = '"' . $srNo . '"' . "\t";
foreach($rec as $value)
{
$value = '"' . $value . '"' . "\t";
$rowData .= $value;
}
$setData .= trim($rowData)."\n";
$srNo++;
}
$columnHeader ='';
$columnHeader = "Sr NO"."\t"."Name"."\t"."Email"."\t"."Phone"."\t";
if($exportType=='CSV'){
// if you wanted export file of csv type then used this code
header("Content-type: text/x-csv");
header("Content-Disposition: attachment; filename=Users_Data.csv");
}else{
// if you wanted to export file of excel type then used this code
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=Users_Data.xls");
}
header("Pragma: no-cache");
header("Expires: 0");
echo ucwords($columnHeader) . "\n" . $setData . "\n";
|
Here if you are exporting the Excel file then we need to using following code:
1
2
3
|
// if you wanted to export file of excel type then used this code
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=Users_Data.xls");
|
And if you wanted to export file of 'CSV' type then use following code:
1
2
3
|
// if you wanted export file of csv type then used this code
header("Content-type: text/x-csv");
header("Content-Disposition: attachment; filename=Users_Data.csv");
|