Home > PHP > Export Mysql data to CSV – PHP tutorial

Export Mysql data to CSV – PHP tutorial

December 16th, 2007 Gertjan Leave a comment Go to comments

Want to export your Mysql data to a CSV(comma seperated value) file? In this tutorial i will show you how to export your data from Mysql into a CSV file.

Fist off we start by creating the connection to the Mysql database:

  • $host: this is the location for the Mysql server it can be a hostname or an ip adress. it is usualy localhost.
  • $db: this is the Mysql user account used to access the database.
  • $user: this is the password for the Mysql user account
  • $pass: this is the name of the Mysql database used.

Now we include the export function.

We will need to include the function ‘exportMysqlToCsv‘ in the file ‘exportcsv.inc.php‘

Now we declare what table should be exported from Mysql into the CSV file:

  • $table: The name of the table that should be exported from Mysql to csv.

Now we start the export to csv from mysql by calling the function ‘exportMysqlToCsv‘:

This function will export all data in the Mysql table. It will place the fieldnames on the first row of the CSV file.
The function will export all Mysql data to a file called ‘export.csv’ by default.
You can change this by adding an extra parameter to the exportMysqlToCsv: exportMysqlToCsv($tablename,$filename).
When the export finishes all your data will be inserted into the csv file and the file will be presented as a download.

?View Code PHP
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

<?php
 
$host = 'localhost'; // MYSQL database host adress
$db = ''; // MYSQL database name
$user = ''; // Mysql Datbase user
$pass = ''; // Mysql Datbase password
 
// Connect to the database
$link = mysql_connect($host, $user, $pass);
mysql_select_db($db);
 
require 'exportcsv.inc.php';
 
$table=""; // this is the tablename that you want to export to csv from mysql.
 
exportMysqlToCsv($table);
 
?>

The actual Mysql Export to csv function:

Feel free to alter this file to your needs:

To change the default filename of the exported data change the $filename variable to your likings.

file: exportcsv.inc.php

?View Code PHP
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73

<?php
 
function exportMysqlToCsv($table,$filename = 'export.csv')
{
    $csv_terminated = "\n";
    $csv_separator = ",";
    $csv_enclosed = '"';
    $csv_escaped = "\\";
    $sql_query = "select * from $table";
 
    // Gets the data from the database
    $result = mysql_query($sql_query);
    $fields_cnt = mysql_num_fields($result);
 
 
    $schema_insert = '';
 
    for ($i = 0; $i < $fields_cnt; $i++)
    {
        $l = $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed,
            stripslashes(mysql_field_name($result, $i))) . $csv_enclosed;
        $schema_insert .= $l;
        $schema_insert .= $csv_separator;
    } // end for
 
    $out = trim(substr($schema_insert, 0, -1));
    $out .= $csv_terminated;
 
    // Format the data
    while ($row = mysql_fetch_array($result))
    {
        $schema_insert = '';
        for ($j = 0; $j < $fields_cnt; $j++)
        {
            if ($row[$j] == '0' || $row[$j] != '')
            {
 
                if ($csv_enclosed == '')
                {
                    $schema_insert .= $row[$j];
                } else
                {
                    $schema_insert .= $csv_enclosed . 
					str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[$j]) . $csv_enclosed;
                }
            } else
            {
                $schema_insert .= '';
            }
 
            if ($j < $fields_cnt - 1)
            {
                $schema_insert .= $csv_separator;
            }
        } // end for
 
        $out .= $schema_insert;
        $out .= $csv_terminated;
    } // end while
 
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Content-Length: " . strlen($out));
    // Output to browser with appropriate mime type, you choose ;)
    header("Content-type: text/x-csv");
    //header("Content-type: text/csv");
    //header("Content-type: application/csv");
    header("Content-Disposition: attachment; filename=$filename");
    echo $out;
    exit;
 
}
 
?>
Categories: PHP Tags: , , , , , , ,
  1. Nic
    August 17th, 2010 at 16:28 | #1
    Reply | Quote

    Hi for some reason IE does not want to download the file. I have tried in FF and Chrome and both worked.
    IE just pops up an error :
    “Unable to download statsDisplay.php form testarea.mydomain.com

    Unable to open this internet site.”

    Is there a specific option to choose from the headers?
    // header(“Content-type: text/x-csv”);
    //header(“Content-type: text/csv”);
    //header(“Content-type: application/csv”);
    i have added an if statement to check if i’m using IE and then to have another header uncommented.

    Content-type: text/csv works for FF and Chrome but none of the others work for IE.

    Any Ideas?

    Thanks for the script so far.

  2. asd
    August 18th, 2010 at 12:37 | #2
    Reply | Quote

    wow, thx!
    works fine for me.

Comment pages
1 2 3 17
  1. June 5th, 2009 at 03:10 | #1
    Export Mysql data to CSV – Tutorial Collection
data recovery