Export Mysql Table Data to CSV file in PHP
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>IFIXNET 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);
}
?>