Export Mysql data to CSV – PHP tutorial

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.

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

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;
 
}
 
?>
134 Comments
  1. Why not using fputcsv PHP function? is this method safer?

    Reply
  2. John says:

    Hello,
    I used the function (with some changes) all was working as it should on a windows xp system, however not on a windows 7.

    function exportMysqlToCsv($filename = ‘Upload.csv’)

    {

    $csv_terminated = “\n”;

    $csv_separator = “,”;

    $csv_enclosed = ‘”‘;

    $csv_escaped = “\\”;

    $fields_cnt = 5;

    $schema_insert = ”;

    for ($i = 0; $i < $fields_cnt; $i++)

    {

    if($i=="0"){$temp="column 1";}

    if($i=="1"){$temp="column 2";}

    if($i=="2"){$temp="column 3";}

    if($i=="3"){$temp="column 4";}

    if($i=="4"){$temp="column 5";}

    $l = $csv_enclosed . $temp . $csv_enclosed;

    $schema_insert .= $l;

    $schema_insert .= $csv_separator;

    } // end for

    $out = trim(substr($schema_insert, 0, -1));

    $out .= $csv_terminated;

    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;

    }
    Does somebody knows how to solve this problem ???
    Thanks in advance, John

    Reply
  3. John says:

    @John
    I, John, solved the problem it was a language isue, I changed Control Panel>Regional and
    Language options to English.

    Reply
  4. Hi
    Im using ur code but im getting dis error msgs.

    Warning: mysql_num_fields(): supplied argument is not a valid MySQL result resource in /home/omangolf/public_html/admin/DownloadDetails.php on line 67

    Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/omangolf/public_html/admin/DownloadDetails.php on line 84

    Warning: Cannot modify header information – headers already sent by (output started at /home/omangolf/public_html/admin/DownloadDetails.php:11) in /home/omangolf/public_html/admin/DownloadDetails.php on line 115

    Warning: Cannot modify header information – headers already sent by (output started at /home/omangolf/public_html/admin/DownloadDetails.php:11) in /home/omangolf/public_html/admin/DownloadDetails.php on line 116

    Warning: Cannot modify header information – headers already sent by (output started at /home/omangolf/public_html/admin/DownloadDetails.php:11) in /home/omangolf/public_html/admin/DownloadDetails.php on line 118

    Warning: Cannot modify header information – headers already sent by (output started at /home/omangolf/public_html/admin/DownloadDetails.php:11) in /home/omangolf/public_html/admin/DownloadDetails.php on line 121

    Reply
  5. lala says:

    @John
    install office;)

    Reply
  6. Shaun Santa Cruz says:

    Thanks, this was exactly what I was looking for.

    Reply
  7. johny says:

    Thank’s man it’s work

    Reply
  8. Pearl says:

    Worked Perfectly! And it was just what I needed. Thank you so much for sharing- I just “bought” you a beer- wish I could afford more, I’m hoping that with enough “beers” you can pay for your time :)

    Reply
  9. Michelle-lee says:

    This is a great post it really help me finish up my final year project at school. I also learnt PHP amd Mysql at Tutorial arena

    Reply
  10. sundarjaykumar says:

    This code is working perfectly with little changes which are mandatory. Itz cool try………

    Reply
  11. rr says:

    Is there a way to make one html button calling this php function export 2 csv files from 2 separate tables with one click? Or do they have to be separate button.

    Reply
  12. sets says:

    Hi. Excellent script and has helped out more than I could ever say. Thanks.

    One thing I’m trying to do is relating to JT’s post on October 31st, 2009 at 3:22 where I’d like to change each column heading to begin with v_
    so something like product_id column heading would save as v_product_id and product_ model column heading would output v_product_model column heading and so on.

    I’ve tried JT’s example but it errors for me.

    Please could someone help me to get v_ at the start of each column heading?

    Thank you.

    Reply
  13. thanks for this!!!

    Reply
  14. Thanks! That was quick and painless. I like that.

    Reply
  15. PHP Coder says:

    Excellent. I changed all the database connection and queries to fit my own database class. Everything works superb. Final output was perfect. Thank you for the code.

    Reply
  16. Thanks!

    I used your code as a part of complete example package about manipulating MySql database with PHP: http://www.sanaracreations.fi/web/an-example-of-manipulating-mysql-database-with-php-and-downloading-it-as-csv

    Reply
  17. Shamim says:

    Thanks! its great;

    If want to search my list and export only search result then what can i do…

    Reply
  18. Bashar says:

    Thank you very much, you just made my day

    Reply
  19. BeverlyJ says:

    Same basic question as Shamim: Works great to dump all records in table, but how can I substitute a query result for the $table var? Thank you for your great contribution in this site!

    Reply
  20. Bruno says:

    Congrats.

    Your code works perfect to me…. thx a lot

    Reply
  21. Manish says:

    Great Work. Worked for me too.

    Reply
  22. maven says:

    great article indeed. used and got it working in the first shot. excellent work. thank you.

    and God bless.

    Reply
  23. Paulraj says:

    Good Work… Thanks !

    Reply
  24. Mike says:

    When i use this, the first row is empty and then the column names show up on row 2 and the results below that… what should i do to get rid of the empty first row (sorry if i missed this somewhere).

    Reply
  25. amit says:

    thanks dear…. useful script 4 me..

    Reply
  26. Thank you for your post.Really thank you! Fantastic.

    Reply
  27. daniel says:

    this is really nice piece of code thanks

    Reply
  28. Jack says:

    missing all the zero “0″ from the excel field. if my record is 0001, i only get 1 in the excel, how to remain the 0001?

    Reply
  29. Hi,
    I found your code extermely useful thanks a lot but the problem is it works only with simple SQL query not the complicated query that has passing variables, something like this:

    SELECT tbl1.field1, tbl1.field2, tbl3.field3, tbl2.field1, tbl2.field2, tbl2.field3 FROM tbl1 INNER JOIN tbl2 ON tbl1.id = tbl2.id WHERE tbl1.field1 = %s AND tbl2.field1 = %s”, mysql_real_escape_string($Var1),mysql_real_escape_string($Var2)

    in fact I was testing a lot and I found it works with the join too but it turnes empty cvs file when adding the variables, so do you have an idea why is that???

    Reply
  30. Hi,

    Thanks for the fast reply, you are right, the problem was with my script, the variable’s value are lost after submitting the form (I added a button in a form to export the data). Thanks for your help and thanks for the great script, I will spread the word about you in my blog and add a link to your post if you don’t mind.

    Reply
Trackbacks / Pings
Leave a Reply




XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


5 − one =