PHP code to convert a MySQL query to CSV

Question

What is the most efficient way to convert a MySQL query to CSV in PHP please?

It would be best to avoid temp files as this reduces portability (dir paths and setting file-system permissions required).

The CSV should also include one top line of field names.

Cheers.

Answer 1

http://snipplr.com/view/2234/export-mysql-query-results-to-csv/

Answer 2

Look at the documentation regarding the SELECT ... INTO OUTFILE syntax.

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table;

Answer 3

SELECT * INTO OUTFILE "c:/mydata.csv"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "\n"
FROM my_table;

(the documentation for this is here: http://dev.mysql.com/doc/refman/5.0/en/select.html)

or:

$select = "SELECT * FROM table_name";

$export = mysql_query ( $select ) or die ( "Sql error : " . mysql_error( ) );

$fields = mysql_num_fields ( $export );

for ( $i = 0; $i < $fields; $i++ )
{
    $header .= mysql_field_name( $export , $i ) . "\t";
}

while( $row = mysql_fetch_row( $export ) )
{
    $line = '';
    foreach( $row as $value )
    {                                            
        if ( ( !isset( $value ) ) || ( $value == "" ) )
        {
            $value = "\t";
        }
        else
        {
            $value = str_replace( '"' , '""' , $value );
            $value = '"' . $value . '"' . "\t";
        }
        $line .= $value;
    }
    $data .= trim( $line ) . "\n";
}
$data = str_replace( "\r" , "" , $data );

if ( $data == "" )
{
    $data = "\n(0) Records Found!\n";                        
}

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=your_desired_name.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";

Answer 4

This poster is really too rude to deserver an answer, but I'd like to pretend I'm helping a more polite interested party who finds this on Google.

Basically, it all works around the fputcsv function. Your file pointer can either be an opened file or php://stdout. If you're writing to a temp file, you'll need to open it and read it again after you're done writing to it.

You do your query, get the result, get the fields (this is easy if you're using the mysqli features because you can ask the result what its fields are and it will return an array you can pass to fputscv). After that, you fetch the associative array for each row and pass it to fputscv. That's it.

Answer 5

Check out this question / answer. It's more concise than @Geoff's, and also uses the builtin fputcsv function.

$result = $db_con->query('SELECT * FROM `some_table`');
if (!$result) die('Couldn\'t fetch records');
$num_fields = mysql_num_fields($result);
$headers = array();
for ($i = 0; $i < $num_fields; $i++) {
    $headers[] = mysql_field_name($result , $i);
}
$fp = fopen('php://output', 'w');
if ($fp && $result) {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="export.csv"');
    header('Pragma: no-cache');
    header('Expires: 0');
    fputcsv($fp, $headers);
    while ($row = $result->fetch_array(MYSQLI_NUM)) {
        fputcsv($fp, array_values($row));
    }
    die;
}

Answer 6

If you'd like the download to be offered as a download that can be opened directly in Excel, this may work for you: (copied from an old unreleased project of mine)

These functions setup the headers:

function setExcelContentType() {
    if(headers_sent())
        return false;

    header('Content-type: application/vnd.ms-excel');
    return true;
}

function setDownloadAsHeader($filename) {
    if(headers_sent())
        return false;

    header('Content-disposition: attachment; filename=' . $filename);
    return true;
}

This one sends a CSV to a stream using a mysql result

function csvFromResult($stream, $result, $showColumnHeaders = true) {
    if($showColumnHeaders) {
        $columnHeaders = array();
        $nfields = mysql_num_fields($result);
        for($i = 0; $i < $nfields; $i++) {
            $field = mysql_fetch_field($result, $i);
            $columnHeaders[] = $field->name;
        }
        fputcsv($stream, $columnHeaders);
    }

    $nrows = 0;
    while($row = mysql_fetch_row($result)) {
        fputcsv($stream, $row);
        $nrows++;
    }

    return $nrows;
}

This one uses the above function to write a CSV to a file, given by $filename

function csvFileFromResult($filename, $result, $showColumnHeaders = true) {
    $fp = fopen($filename, 'w');
    $rc = csvFromResult($fp, $result, $showColumnHeaders);
    fclose($fp);
    return $rc;
}

And this is where the magic happens ;)

function csvToExcelDownloadFromResult($result, $showColumnHeaders = true, $asFilename = 'data.csv') {
    setExcelContentType();
    setDownloadAsHeader($asFilename);
    return csvFileFromResult('php://output', $result, $showColumnHeaders);
}

For example:

$result = mysql_query("SELECT foo, bar, shazbot FROM baz WHERE boo = 'foo'");
csvToExcelDownloadFromResult($result);

Answer 7

This is really good John. Thank you.

One question.

I can't seem to get each $result returned onto its own row within the csv file. Any suggestions?

TIA.

Answer 8

I just used your script John but when I executed the script it showed all data from the query on the screen. It did not result in a data file. What did I do wrong?

Answer 9

By the way, I realise I am replying about a year late to RonP's question, but it has not been answered yet, and others who found this page via Google, like me, may appreciate an answer.

In reply to RonP's question, if you want each $row output on a separate line, you need to add the line breaks in somewhere. From memory I think these are one of two options:

\n or \r\n

The reason for the confusion is that \r\n should work on windows, but linux line breaks are slightly different, and im guessing it was \n, but could be wrong. google it! :)

**Edit: See this for more info on linebreaks: http://stackoverflow.com/questions/255511/php-echo-line-breaks

From briefly looking at the code, you should replace the following line in csvFromResult():

fputcsv($stream, $row);

with:

fputcsv($stream, $row."\r\n");

If im wrong, please correct me :)