Let's say I have a database.... is there a way I can export what I have from the database to a CSV file (and text file [if possible]) via PHP?
8 Answers
I personally use this function to create CSV content from any array.
function array2csv(array &$array)
{
if (count($array) == 0) {
return null;
}
ob_start();
$df = fopen("php://output", 'w');
fputcsv($df, array_keys(reset($array)));
foreach ($array as $row) {
fputcsv($df, $row);
}
fclose($df);
return ob_get_clean();
}
Then you can make your user download that file using something like:
function download_send_headers($filename) {
// disable caching
$now = gmdate("D, d M Y H:i:s");
header("Expires: Tue, 03 Jul 2001 06:00:00 GMT");
header("Cache-Control: max-age=0, no-cache, must-revalidate, proxy-revalidate");
header("Last-Modified: {$now} GMT");
// force download
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
// disposition / encoding on response body
header("Content-Disposition: attachment;filename={$filename}");
header("Content-Transfer-Encoding: binary");
}
Usage example:
download_send_headers("data_export_" . date("Y-m-d") . ".csv");
echo array2csv($array);
die();
You can export the date using this command.
<?php
$list = array (
array('aaa', 'bbb', 'ccc', 'dddd'),
array('123', '456', '789'),
array('"aaa"', '"bbb"')
);
$fp = fopen('file.csv', 'w');
foreach ($list as $fields) {
fputcsv($fp, $fields);
}
fclose($fp);
?>
First you must load the data from the mysql server in to a array
Just for the record, concatenation is waaaaaay faster (I mean it) than fputcsv
or even implode
; And the file size is smaller:
// The data from Eternal Oblivion is an object, always
$values = (array) fetchDataFromEternalOblivion($userId, $limit = 1000);
// ----- fputcsv (slow)
// The code of @Alain Tiemblo is the best implementation
ob_start();
$csv = fopen("php://output", 'w');
fputcsv($csv, array_keys(reset($values)));
foreach ($values as $row) {
fputcsv($csv, $row);
}
fclose($csv);
return ob_get_clean();
// ----- implode (slow, but file size is smaller)
$csv = implode(",", array_keys(reset($values))) . PHP_EOL;
foreach ($values as $row) {
$csv .= '"' . implode('","', $row) . '"' . PHP_EOL;
}
return $csv;
// ----- concatenation (fast, file size is smaller)
// We can use one implode for the headers =D
$csv = implode(",", array_keys(reset($values))) . PHP_EOL;
$i = 1;
// This is less flexible, but we have more control over the formatting
foreach ($values as $row) {
$csv .= '"' . $row['id'] . '",';
$csv .= '"' . $row['name'] . '",';
$csv .= '"' . date('d-m-Y', strtotime($row['date'])) . '",';
$csv .= '"' . ($row['pet_name'] ?: '-' ) . '",';
$csv .= PHP_EOL;
}
return $csv;
This is the conclusion of the optimization of several reports, from ten to thousands rows. The three examples worked fine under 1000 rows, but fails when the data was bigger.
Works with over 100 lines, if you specify the size of the file in the headers simple call the get() method in your own class
function setHeader($filename, $filesize)
{
// disable caching
$now = gmdate("D, d M Y H:i:s");
header("Expires: Tue, 01 Jan 2001 00:00:01 GMT");
header("Cache-Control: max-age=0, no-cache, must-revalidate, proxy-revalidate");
header("Last-Modified: {$now} GMT");
// force download
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header('Content-Type: text/x-csv');
// disposition / encoding on response body
if (isset($filename) && strlen($filename) > 0)
header("Content-Disposition: attachment;filename={$filename}");
if (isset($filesize))
header("Content-Length: ".$filesize);
header("Content-Transfer-Encoding: binary");
header("Connection: close");
}
function getSql()
{
// return you own sql
$sql = "SELECT id, date, params, value FROM sometable ORDER BY date;";
return $sql;
}
function getExportData()
{
$values = array();
$sql = $this->getSql();
if (strlen($sql) > 0)
{
$result = dbquery($sql); // opens the database and executes the sql ... make your own ;-)
$fromDb = mysql_fetch_assoc($result);
if ($fromDb !== false)
{
while ($fromDb)
{
$values[] = $fromDb;
$fromDb = mysql_fetch_assoc($result);
}
}
}
return $values;
}
function get()
{
$values = $this->getExportData(); // values as array
$csv = tmpfile();
$bFirstRowHeader = true;
foreach ($values as $row)
{
if ($bFirstRowHeader)
{
fputcsv($csv, array_keys($row));
$bFirstRowHeader = false;
}
fputcsv($csv, array_values($row));
}
rewind($csv);
$filename = "export_".date("Y-m-d").".csv";
$fstat = fstat($csv);
$this->setHeader($filename, $fstat['size']);
fpassthru($csv);
fclose($csv);
}
pre-made code attached here. you can use it by just copying and pasting in your code:
https://gist.github.com/umairidrees/8952054#file-php-save-db-table-as-csv
<?php
// Database Connection
include "includes/db/db.php";
$query = mysqli_query($connection,"SELECT * FROM team_attendance JOIN team_login ON
team_attendance.attendance_user_id=team_login.user_id where
attendance_activity_name='Checked-In' order by
team_attendance.attendance_id ASC"); // Get data from Database from
demo table
$delimiter = ",";
$filename = "attendance" . date('Ymd') . ".csv"; // Create file name
//create a file pointer
$f = fopen('php://memory', 'w');
//set column headers
$fields = array('ID', 'Employee Name', 'Check In Time', 'Check Out Time', 'Date');
fputcsv($f, $fields, $delimiter);
//output each row of the data, format line as csv and write to file pointer
while($row = $query->fetch_assoc()){
$date=date('d-m-Y',$row['attendance_date']);
$lineData = array($row['attendance_id'], $row['user_name'], $row['attendance_time'],
$row['check_out_time'],$date);
fputcsv($f, $lineData, $delimiter);
}
//move back to beginning of file
fseek($f, 0);
//set headers to download file rather than displayed
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="' . $filename . '";');
//output all remaining data on a file pointer
fpassthru($f);
?>