How Can I Efficiently Convert MySQL Query Results to CSV in PHP Without Temporary Files?

Efficient CSV Conversion from MySQL Queries in PHP

Converting MySQL query results to CSV is a common task in PHP development. While there are various approaches, some methods may suffer from portability issues related to temporary file handling. This article explores methods that provide an efficient and portable solution for CSV conversion without the hindrance of temporary files.

Method 1: SELECT INTO OUTFILE

MySQL provides a built-in mechanism called SELECT INTO OUTFILE that allows direct CSV export into a specified file path:

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

This command creates a CSV file at the specified path with proper field delimiters and optional field enclosure.

Method 2: PHP Query Manipulation and String Concatenation

Alternatively, PHP can be leveraged to manipulate the query results and construct a CSV string:

$select = "SELECT * FROM table_name";
$export = mysql_query ( $select ) or die ( "Sql error : " . mysql_error( ) );
$fields = mysql_num_fields ( $export );

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

// Iterate and construct CSV string
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 );

The constructed CSV string can then be output to the user via HTTP headers:

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 methods provide efficient CSV conversion options in PHP. The SELECT INTO OUTFILE approach is a direct solution from MySQL, while the PHP-based method utilizes query manipulation and string concatenation for CSV construction. Depending on the specific scenario and performance requirements, either method can be employed to achieve the desired CSV export.

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