Exporting MySQL table data to Excel using PHP

Wednesday 26th October 2011

When developing a website that uses a database, quite often there would be a requirement to store dynamic data in a table, whether that be contact form entries, activity logs, or even lists of registered users.

While that data can be easily extracted directly from the database using database software, you may need to export that data using a web application. The following code is some useful PHP that can be used to take all data from a MySQL table and export them in a CSV format, that can be downloaded by the user as a file.

// Create query to select as data from your table
$select = "SELECT * FROM table_name";

// Run mysql query and then count number of fields
$export = mysql_query ( $select ) 
        or die ( "Sql error : " . mysql_error( ) );
$fields = mysql_num_fields ( $export );

// Create csv header row, to contain table headers with database field names
for ( $i = 0; $i < $fields; $i++ ) {
    $header .= mysql_field_name( $export , $i ) . ",";
}

// Loop through the query results, and create a row for each
while( $row = mysql_fetch_row( $export ) ) {
    $line = '';
   // for each field in the row
    foreach( $row as $value ) {
       // if null, create blank field
        if ( ( !isset( $value ) ) || ( $value == "" ) ){
            $value = ",";
        }
       // else, assign field value to our data
        else {
            $value = str_replace( '"' , '""' , $value );
            $value = '"' . $value . '"' . ",";
        }
       // add this field value to our row
        $line .= $value;
    }
   // trim whitespace from each row
    $data .= trim( $line ) . "\n";
}
// Remove all carriage returns from the data
$data = str_replace( "\r" , "" , $data );


// Create a file and send to browser for user to download
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$file_name.".csv");
print "$header\n$data";
exit;

Posted by Kevin McMahon @ 12:10 AM