MySQL: CSV Export as automatic Download

Often, you want to download a MySQL table as a CSV file, for example, to open it in Excel or to import it into another database. In this tutorial, I want to show you a PHP script, that allows you to save a MySQL table quickly and easily in a file, whose download will start automatically.

If you prefer to save the table as a file on your server, you should have a look into this other tutorial where exactly this is shown.

For our export as automatic download, first, we create a file called "export.php", containing the following code:

<?php
//MySQL Query
$database   = ""; //name of database
$connection = @mysql_connect("","",""); //server, user, password
$query      = "SELECT * FROM tab WHERE 1"; //table
$result     = mysql_db_query($database, $query, $connection);
 
//Send Header
header("HTTP/1.1 200 OK");
header("Content-Type: text/x-csv");
header('Content-Disposition: attachment; filename="export.csv"');
header("Content-Transfer-Encoding: binary");
header('Cache-Control: no-cache');
 
//Output Data
while($line = mysql_fetch_array($result, MYSQL_NUM)) {
   foreach($line as $value) {
      echo '"',str_replace('"', '""', utf8_decode($value)),'";'; 
   }
   echo "\r\n";
}
?>

At the top of this file, you have to enter the name of your database and your table as well as the server, the user name and the corresponding password, so that MySQL can connect properly.

Now, we want to walk through the function step by step:

MySQL Query

In the first part, a connection is established to the database, a request is carried out and the result of this request is stored in the variable $result. Here you can use any SELECT/WHERE/ORDER BY clause you want, depending on what data you want to export.

Send Header

It gets more interesting in the second part. Here the header is sent, which makes it possible that the following data will not be displayed as normal website but made available as CSV file for download. It this part, you can also specify how you want to name the file. In the example, we use "export.csv". Please note,  that no character can be output before the header. Otherwise, this will cause an error message.

Output Data

After the header, which tells the browser, that now a CSV file offered as a download comes, has been sent, we can simply output the data as it should be written in the CSV file. For this, we just go line by line through the MySQL result. In each line, we walk through the fields and put " as a boundary around the values and a ";" as delimiter in between. Finally, at the end of each line, we output a line break with "\r\n" as a limitation between the rows.

Within the fields, we double the quotes, so that they can not be interpreted erroneously as the beginning of a new field. In addition, we use the function utf8_decode() to remove the UTF8 encoding. We do this, because otherwise Excel has possibly problems to display the file in the correct formatting. Of course, we can also omit this and output the file directly as UTF-8, if needed.