How to Efficiently Create CSV Files from MySQL Queries in PHP?

Creating CSV from MySQL Queries in PHP

In PHP, one can efficiently convert a MySQL query into a CSV file. Avoiding temporary files enhances portability by eliminating the need for setting file system permissions and managing directory paths. Additionally, the generated CSV file should include a header row with field names.

To execute this task, one can employ the following approaches:

Option 1: Using the INTO OUTFILE Method

MySQL provides the INTO OUTFILE method to directly output query results to a CSV file. For instance, the following query exports data from the my_table table to a CSV file named c:/mydata.csv:

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

Option 2: Custom PHP Script

Alternatively, one can use a custom PHP script to fetch the query results and generate the CSV. This involves:

1. Executing the SQL query using mysql_query().
2. Determining the number of fields using mysql_num_fields().
3. Iterating over each row and field to construct the CSV data.
4. Setting appropriate HTTP headers for the CSV file.
5. Outputting the header and data to the user's browser or download interface.

The following code snippet demonstrates this approach:

$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";

By employing these methods, one can efficiently convert MySQL query results into CSV files while ensuring portability and header inclusion.

The above is the detailed content of How to Efficiently Create CSV Files from MySQL Queries in PHP?. For more information, please follow other related articles on the PHP Chinese website!