How Can I Efficiently Export MySQL Query Results to CSV Using PHP?

Converting MySQL Queries to CSV in PHP

In PHP, exporting data from MySQL queries to CSV can be efficiently achieved without the use of temporary files. Here are two effective methods to accomplish this task:

Using MySQL's SELECT ... INTO OUTFILE:

This query directly outputs the result into a CSV file:

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

Using PHP Code:

This code selects the data, formats it in CSV format, and sends it as a response:

$select = "SELECT * FROM table_name";

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

$fields = mysql_num_fields ( $export );

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

// Get data
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";
}

// Send response
$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";

Both of these methods effectively convert MySQL query results to CSV format in PHP, with the choice of approach depending on factors such as database size and desired flexibility.

The above is the detailed content of How Can I Efficiently Export MySQL Query Results to CSV Using PHP?. For more information, please follow other related articles on the PHP Chinese website!