For this simple tutorial, we will use an example where we want to export data from a SQL database to a CSV file. In this example, we will have a webpage http://www.example.com/stage.php. At this page, there is a link to export to CSV.
<html>
<body>
<a href="csv_export.php"> Export to CSV </a>
</body>
</html>
Notice the above page links to csv_export.php. If using a MySQL database, following is the minimal PHP needed for the csv_export.php page.
<?php
// Header is needed to export to CSV. If header is not include, the data will display in the Web browser
header("Content-Type: text/csv;charset=utf-8");
$con = new mysqli('domain','username','password','database');
$select = "select * from tablename";
$select_query = mysqli_query($con, $select);
$fp= fopen('php://output', 'w');
// While loop, create array named $row, Use fputcsv function
while ($row = mysqli_fetch_array($select_query,MYSQL_ASSOC)) {
fputcsv($fp, array_values($row));
}
// die is needed to prevent the data from displaying twice in the CSV file
die;
fclose($fp);
?>
If using Microsoft SQL Server database, following is the minimal PHP needed for the csv_export.php page.
An unique issue that occurs with SQL Server is that SQL Server is unable to automatically convert DateTime to a string. If the table being exported contains date, you will need to specific convert in the SQL statement. In this example, created_date is the name of the date column.
- $select = "select * from tablename";
- $select = "select convert (varchar(25), created_date, 101) created_date from tablename";
<?php
// Header is needed to export to CSV. If header is not include, the data will display in the Web browser
header("Content-Type: text/csv;charset=utf-8");
$ServerName = "domain";
$ConnectionString = array("Database"=>"Database", "UID"=>"username", "PWD"=>"password");
$con = sqlsrv_connect($ServerName, $ConnectionString);
$select = "select * from tablename";
$select_query = sqlsrv_query($con, $select);
$fp= fopen('php://output', 'w');
// While loop, create array named $row, Use fputcsv function
while ($row = sqlsrv_fetch_array($select_query, SQLSRV_FETCH_ASSOC)) {
fputcsv($fp, array_values($row));
}
// die is needed to prevent the data from displaying twice in the CSV file
die;
fclose($fp);
?>
Following is a more complete code set, with headers.
<?php
$file_name = 'aqi-';
$date = date('d-m-Y');
$csv = '.csv';
header("Content-Type: text/csv;charset=utf-8");
header("Content-Disposition: attachment;filename=\"$file_name$date$csv\"");
header("Content-Transfer-Encoding: binary");
header("Pragma: no-cache");
header("Expires: 0");
header("Cache-Control: max-age=0, no-cache, must-revalidate, proxy-revalidate");
header("Last-Modified: {$now} GMT");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
$con = new mysqli('domain','username','password','database');
$select = "select * from tablename";
$select_query = mysqli_query($con, $select);
$fp= fopen('php://output', 'w');
// Headers
fputcsv($fp, array('Header One', 'Header Two'));
// Data, Records
while ($row = mysqli_fetch_array($select_query,MYSQL_ASSOC)) {
fputcsv($fp, array_values($row));
}
die;
fclose($fp);
?>
Did you find this article helpful?
If so, consider buying me a coffee over at 