Hello friends in this post we are going to discuss how can we create csv file from mysql table data by using php script. Now a days comma separated values file data is most commonly supported file format for export tabular records between web applications. Exporting of Mysql table data to csv format file is useful feature in your web application and it will become increasingly common in all type of web based application. This is because this type of file consume more data and gain less space. We can store more data in csv file format and it consume less web space as compare to excel file format. So, in this video we are going to learn how can we export mysql table data to csv file. So here we have write php script that make csv file from mysql table data. In php script first we have set the http header for define content type for csv file and we have also set header for download file as attachment and we have also define the name of file, then after we have open file from php output stream and then after we have fetch data from mysql table and by using file put csv function we have write mysql table data into csv file line by line. This way we can export mysql table data to csv file by using php script.
Source Code
index.php
<?php
$connect = mysqli_connect("localhost", "root", "", "testing");
$query ="SELECT * FROM tbl_employee ORDER BY id desc";
$result = mysqli_query($connect, $query);
?>
<!DOCTYPE html>
<html>
<head>
<title>Webslesson Tutorial | Export Mysql Table Data to CSV file in PHP</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>
<body>
<br /><br />
<div class="container" style="width:900px;">
<h2 align="center">Export Mysql Table Data to CSV file in PHP</h2>
<h3 align="center">Employee Data</h3>
<br />
<form method="post" action="export.php" align="center">
<input type="submit" name="export" value="CSV Export" class="btn btn-success" />
</form>
<br />
<div class="table-responsive" id="employee_table">
<table class="table table-bordered">
<tr>
<th width="5%">ID</th>
<th width="25%">Name</th>
<th width="35%">Address</th>
<th width="10%">Gender</th>
<th width="20%">Designation</th>
<th width="5%">Age</th>
</tr>
<?php
while($row = mysqli_fetch_array($result))
{
?>
<tr>
<td><?php echo $row["id"]; ?></td>
<td><?php echo $row["name"]; ?></td>
<td><?php echo $row["address"]; ?></td>
<td><?php echo $row["gender"]; ?></td>
<td><?php echo $row["designation"]; ?></td>
<td><?php echo $row["age"]; ?></td>
</tr>
<?php
}
?>
</table>
</div>
</div>
</body>
</html>
export.php
<?php
//export.php
if(isset($_POST["export"]))
{
$connect = mysqli_connect("localhost", "root", "", "testing");
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=data.csv');
$output = fopen("php://output", "w");
fputcsv($output, array('ID', 'Name', 'Address', 'Gender', 'Designation', 'Age'));
$query = "SELECT * from tbl_employee ORDER BY id DESC";
$result = mysqli_query($connect, $query);
while($row = mysqli_fetch_assoc($result))
{
fputcsv($output, $row);
}
fclose($output);
}
?>
Its really very helpful. Thanks u guy.
ReplyDeletewell broken link but thanks lol
ReplyDeletein my case only csv file download but htere is no data what i do
ReplyDeletehello dear webslesson..
ReplyDeletethe file is not available anymore in zippyshare...
pls upload on your google drive & share...
thats which simple & elegant....
thanks for all your efforts...
Great tutorial even though i couldn't download source code...
ReplyDeleteHi,
ReplyDeleteThank you for your clean and clear code. But I have one problem, When I hit the export button It printed the output to the screen and didn't download any csv file!
What do you think my problem is?
Thank you much in advance :)
Hii How can i get the date in csv file it's giving me ####
ReplyDeletehey. The ### means your field is to small too show data. That was my problem.
DeleteHello is there any solution with date
ReplyDeletehow can i export mysql table date into CSV file
link fall
ReplyDeleteI liked it. Have put it into practice. Do you have a tutorial to do the opposite? In other words, if I have a CSV file and want to export it with PHP to my table.
ReplyDeleteAmazing! Awesome and all other good stuff there is ... very helpful! Thankyou!!!
ReplyDeleteit says cannot modify headers
ReplyDeleteThis is awesome. Thank you.
ReplyDeleteVery Helpful... Thanks a lot...
ReplyDeleteif i want print data from 1 tables so can print data
ReplyDeleteThis helped alot.
ReplyDeleteTHANK YOU SO SO MUCH. Keep up the good work. You are simply awesome.
the exported file showed a blank line at the top before the headers.. why is that?
ReplyDeleteHello, Your code works perfectly! thanks for that.. But I'm facing one issue when I;m opening the exported CSV file the prompt window comes up and it says its a SYLK file. Can you help out in that?
ReplyDeleteinclude("export.php");
ReplyDeleteThank you sir for the explain this and sharing code
ReplyDelete