Create a CSV File for a user in PHP

问题:

I have data in a MySQL database. I am sending the user a URL to get their data out as a CSV file.

I have the e-mailing of the link, MySQL query, etc. covered.

How can I, when they click the link, have a pop-up to download a CVS with the record from MySQL?

I have all the information to get the record already. I just don\'t see how to have PHP create the CSV file and let them download a file with a .csv extension.

回答1:

Try:

header(\"Content-type: text/csv\");
header(\"Content-Disposition: attachment; filename=file.csv\");
header(\"Pragma: no-cache\");
header(\"Expires: 0\");

echo \"record1,record2,record3\\n\";
die;

etc

Edit: Here\'s a snippet of code I use to optionally encode CSV fields:

function maybeEncodeCSVField($string) {
    if(strpos($string, \',\') !== false || strpos($string, \'\"\') !== false || strpos($string, \"\\n\") !== false) {
        $string = \'\"\' . str_replace(\'\"\', \'\"\"\', $string) . \'\"\';
    }
    return $string;
}

回答2:

header(\"Content-Type: text/csv\");
header(\"Content-Disposition: attachment; filename=file.csv\");

function outputCSV($data) {
  $output = fopen(\"php://output\", \"wb\");
  foreach ($data as $row)
    fputcsv($output, $row); // here you can change delimiter/enclosure
  fclose($output);
}

outputCSV(array(
  array(\"name 1\", \"age 1\", \"city 1\"),
  array(\"name 2\", \"age 2\", \"city 2\"),
  array(\"name 3\", \"age 3\", \"city 3\")
));

php://output
fputcsv

回答3:

Here is an improved version of the function from php.net that @Andrew posted.

function download_csv_results($results, $name = NULL)
{
    if( ! $name)
    {
        $name = md5(uniqid() . microtime(TRUE) . mt_rand()). \'.csv\';
    }

    header(\'Content-Type: text/csv\');
    header(\'Content-Disposition: attachment; filename=\'. $name);
    header(\'Pragma: no-cache\');
    header(\"Expires: 0\");

    $outstream = fopen(\"php://output\", \"wb\");

    foreach($results as $result)
    {
        fputcsv($outstream, $result);
    }

    fclose($outstream);
}

It is really easy to use and works great with MySQL(i)/PDO result sets.

download_csv_results($results, \'your_name_here.csv\');

Remember to exit() after calling this if you are done with the page.

回答4:

In addition to all already said, you might need to add:

header(\"Content-Transfer-Encoding: UTF-8\");

It\'s very useful when handling files with multiple languages in them, like people\'s names, or cities.

回答5:

The thread is a little old, I know, but for future reference and for noobs as myself:

Everyone else here explain how to create the CSV, but miss a basic part of the question: how to link. In order to link to download of the CSV-file, you just link to the .php-file, which in turn responds as being a .csv-file. The PHP headers do that. This enables cool stuff, like adding variables to the querystring and customize the output:

<a href=\"my_csv_creator.php?user=23&amp;othervariable=true\">Get CSV</a>

my_csv_creator.php can work with the variables given in the querystring and for example use different or customized database queries, change the columns of the CSV, personalize the filename and so on, e.g.:

User_John_Doe_10_Dec_11.csv

回答6:

Create your file then return a reference to it with the correct header to trigger the Save As - edit the following as needed. Put your CSV data into $csvdata.

$fname = \'myCSV.csv\';
$fp = fopen($fname,\'wb\');
fwrite($fp,$csvdata);
fclose($fp);

header(\'Content-type: application/csv\');
header(\"Content-Disposition: inline; filename=\".$fname);
readfile($fname);

回答7:

Here is a full working example using PDO and including column headers:

$query = $pdo->prepare(\'SELECT * FROM test WHERE id=?\');
$query->execute(array($id));    
$results = $query->fetchAll(PDO::FETCH_ASSOC);
download_csv_results($results, \'test.csv\'); 
exit();


function download_csv_results($results, $name)
{            
    header(\'Content-Type: text/csv\');
    header(\'Content-Disposition: attachment; filename=\'. $name);
    header(\'Pragma: no-cache\');
    header(\"Expires: 0\");

    $outstream = fopen(\"php://output\", \"wb\");    
    fputcsv($outstream, array_keys($results[0]));

    foreach($results as $result)
    {
        fputcsv($outstream, $result);
    }

    fclose($outstream);
}

回答8:

First make data as a String with comma as the delimiter (separated with \",\"). Something like this

$CSV_string=\"No,Date,Email,Sender Name,Sender Email \\n\"; //making string, So \"\\n\" is used for newLine

$rand = rand(1,50); //Make a random int number between 1 to 50.
$file =\"export/export\".$rand.\".csv\"; //For avoiding cache in the client and on the server 
                                     //side it is recommended that the file name be different.

file_put_contents($file,$CSV_string);

/* Or try this code if $CSV_string is an array
    fh =fopen($file, \'w\');
    fputcsv($fh , $CSV_string , \",\"  , \"\\n\" ); // \",\" is delimiter // \"\\n\" is new line.
    fclose($fh);
*/

回答9:

Hey It works very well....!!!! Thanks Peter Mortensen and Connor Burton

<?php
header(\"Content-type: application/csv\");
header(\"Content-Disposition: attachment; filename=file.csv\");
header(\"Pragma: no-cache\");
header(\"Expires: 0\");

ini_set(\'display_errors\',1);
$private=1;
error_reporting(E_ALL ^ E_NOTICE);

mysql_connect(\"localhost\", \"user\", \"pass\") or die(mysql_error());
mysql_select_db(\"db\") or die(mysql_error());

$start = $_GET[\"start\"];
$end = $_GET[\"end\"];

$query = \"SELECT * FROM customers WHERE created>=\'{$start} 00:00:00\'  AND created<=\'{$end} 23:59:59\'   ORDER BY id\";
$select_c = mysql_query($query) or die(mysql_error());

while ($row = mysql_fetch_array($select_c, MYSQL_ASSOC))
{
    $result.=\"{$row[\'email\']},\";
    $result.=\"\\n\";
    echo $result;
}

?>

回答10:

Simple method -

$data = array (
    \'aaa,bbb,ccc,dddd\',
    \'123,456,789\',
    \'\"aaa\",\"bbb\"\');

$fp = fopen(\'data.csv\', \'wb\');
foreach($data as $line){
    $val = explode(\",\",$line);
    fputcsv($fp, $val);
}
fclose($fp);

So each line of the $data array will go to a new line of your newly created CSV file. It only works only for PHP 5 and later.

回答11:

You can simply write your data into CSV using fputcsv function. let us have a look at the example below. Write the list array to CSV file

$list[] = array(\"Cars\", \"Planes\", \"Ships\");
$list[] = array(\"Car\'s2\", \"Planes2\", \"Ships2\");
//define headers for CSV 
header(\'Content-Type: text/csv; charset=utf-8\');
header(\'Content-Disposition: attachment; filename=file_name.csv\');
//write data into CSV
$fp = fopen(\'php://output\', \'wb\');
//convert data to UTF-8 
fprintf($fp, chr(0xEF).chr(0xBB).chr(0xBF));
foreach ($list as $line) {
    fputcsv($fp, $line);
}
fclose($fp);

回答12:

The easiest way is to use a dedicated CSV class like this:

$csv = new csv();
$csv->load_data(array(
    array(\'name\'=>\'John\', \'age\'=>35),
    array(\'name\'=>\'Adrian\', \'age\'=>23), 
    array(\'name\'=>\'William\', \'age\'=>57) 
));
$csv->send_file(\'age.csv\'); 

回答13:

Instead of:

$query = \"SELECT * FROM customers WHERE created>=\'{$start} 00:00:00\'  AND created<=\'{$end} 23:59:59\'   ORDER BY id\";
$select_c = mysql_query($query) or die(mysql_error()); 

while ($row = mysql_fetch_array($select_c, MYSQL_ASSOC))
{
    $result.=\"{$row[\'email\']},\";
    $result.=\"\\n\";
    echo $result;
}

Use:

$query = \"SELECT * FROM customers WHERE created>=\'{$start} 00:00:00\'  AND created<=\'{$end} 23:59:59\'   ORDER BY id\";
$select_c = mysql_query($query) or die(mysql_error()); 

while ($row = mysql_fetch_array($select_c, MYSQL_ASSOC))
{
    echo implode(\",\", $row).\"\\n\";
}

回答14:

Already very good solution came. I\'m just puting the total code so that a newbie get total help

<?php
extract($_GET); //you can send some parameter by query variable. I have sent table name in *table* variable

header(\"Content-type: text/csv\");
header(\"Content-Disposition: attachment; filename=$table.csv\");
header(\"Pragma: no-cache\");
header(\"Expires: 0\");

require_once(\"includes/functions.php\"); //necessary mysql connection functions here

//first of all I\'ll get the column name to put title of csv file.
$query = \"SHOW columns FROM $table\";
$headers = mysql_query($query) or die(mysql_error());
$csv_head = array();
while ($row = mysql_fetch_array($headers, MYSQL_ASSOC))
{
    $csv_head[] =  $row[\'Field\'];
}
echo implode(\",\", $csv_head).\"\\n\";

//now I\'ll bring the data.
$query = \"SELECT * FROM $table\";
$select_c = mysql_query($query) or die(mysql_error()); 

while ($row = mysql_fetch_array($select_c, MYSQL_ASSOC))
{
    foreach ($row as $key => $value) {
            //there may be separator (here I have used comma) inside data. So need to put double quote around such data.
        if(strpos($value, \',\') !== false || strpos($value, \'\"\') !== false || strpos($value, \"\\n\") !== false) {
            $row[$key] = \'\"\' . str_replace(\'\"\', \'\"\"\', $value) . \'\"\';
        }
    }
    echo implode(\",\", $row).\"\\n\";
}

?>

I have saved this code in csv-download.php

Now see how I have used this data to download csv file

<a href=\"csv-download.php?table=tbl_vfm\"><img title=\"Download as Excel\" src=\"images/Excel-logo.gif\" alt=\"Download as Excel\" /><a/>

So when I have clicked the link it download the file without taking me to csv-download.php page on browser.

回答15:

To have it send it as a CSV and have it give the file name, use header():

http://us2.php.net/header

header(\'Content-type: text/csv\');
header(\'Content-disposition: attachment; filename=\"myfile.csv\"\');

As far as making the CSV itself, you would just loop through the result set, formatting the output and sending it, just like you would any other content.

回答16:

Writing your own CSV code is probably a waste of your time, just use a package such as league/csv - it deals with all the difficult stuff for you, the documentation is good and it\'s very stable / reliable:

http://csv.thephpleague.com/

You\'ll need to be using composer. If you don\'t know what composer is I highly recommend you have a look: https://getcomposer.org/

回答17:

<?
    // Connect to database
    $result = mysql_query(\"select id
    from tablename
    where shid=3\");
    list($DBshid) = mysql_fetch_row($result);

    /***********************************
    Write date to CSV file
    ***********************************/

    $_file = \'show.csv\';
    $_fp = @fopen( $_file, \'wb\' );

    $result = mysql_query(\"select name,compname,job_title,email_add,phone,url from UserTables where id=3\");

    while (list( $Username, $Useremail_add, $Userphone, $Userurl) = mysql_fetch_row($result))
    {
        $_csv_data = $Username.\',\'.$Useremail_add.\',\'.$Userphone.\',\'.$Userurl . \"\\n\";
        @fwrite( $_fp, $_csv_data);
    }
    @fclose( $_fp );
?>

回答18:

How to write in CSV file using PHP script? Actually I was also searching for that too. It is kind of easy task with PHP. fputs(handler, content) - this function works efficiently for me. First you need to open the file in which you need to write content using fopen($CSVFileName, ‘wb’).

$CSVFileName = “test.csv”;
$fp = fopen($CSVFileName, ‘wb’);

//Multiple iterations to append the data using function fputs()
foreach ($csv_post as $temp)
{
    $line = “”;
    $line .= “Content 1″ . $comma . “$temp” . $comma . “Content 2″ . $comma . “16/10/2012″.$comma;
    $line .= “\\n”;
    fputs($fp, $line);
}

回答19:

Here is one I have done before, grabbing data between certain dates. Hope it helps.

<?php
    header(\"Content-type: application/csv\");
    header(\"Content-Disposition: attachment; filename=file.csv\");
    header(\"Pragma: no-cache\");
    header(\"Expires: 0\");

    ini_set(\'display_errors\',1);
    $private=1;
    error_reporting(E_ALL ^ E_NOTICE);

    mysql_connect(\"localhost\", \"user\", \"pass\") or die(mysql_error());
    mysql_select_db(\"db\") or die(mysql_error());

    $start = mysql_real_escape_string($_GET[\"start\"]);
    $end = mysql_real_escape_string($_GET[\"end\"]);

    $query = \"SELECT * FROM customers WHERE created>=\'{$start} 00:00:00\'  AND created<=\'{$end} 23:59:59\'   ORDER BY id\";
    $select_c = mysql_query($query) or die(mysql_error());

    while ($row = mysql_fetch_array($select_c, MYSQL_ASSOC))
    {
        $result.=\"{$row[\'email\']},\";
        $result.=\"\\n\";
        echo $result;
    }
?>

回答20:

Put in the $output variable the CSV data and echo with the correct headers

header(\"Content-type: application/download\\r\\n\");
header(\"Content-disposition: filename=filename.csv\\r\\n\\r\\n\");
header(\"Content-Transfer-Encoding: ASCII\\r\\n\");
header(\"Content-length: \".strlen($output).\"\\r\\n\");
echo $output;