Home >Database >Mysql Tutorial >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:
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!