Dec 16

Export Mysql data to CSV - PHP tutorial

PHP

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.

?[Copy to clipboard]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

?[Copy to clipboard]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;
 
}
 
?>
  1. raymonn Said,

    tnx… this is really useful

  2. Jay Said,

    Very handy,

    Is there a method to concatenate particular row data (denoted by a unique identifier) from several tables into one CSV file?

    Thanx

  3. Bernard Said,

    Thanks for this great info! Is there a way to save the .csv file to your server after export, and not have it as a download? Or is it done already?

  4. Stevet540 Said,

    Hi, new to PHP so not too sure what i am doing. Script works great, and just what i need but i would like to change the field headings to match outlook 2007. I am using 2 fields, currently ‘username’ and ‘e-mail’, and would like to name them ‘First Name’ and ‘E-mail Address’. Have tried for hours now and can’t figure it out. Help please.

  5. naughty Said,

    is there any way to bold the title of exported CSV file. Kind Regards, Naughty

  6. Fred Said,

    I might be wrong, as I only had a quick look at the code. You can expand this function by passing the function a query in stead of a table. This should enable you to pass it a join query and thus join multiple tables (Jay).

    You can then also use the ‘as’ identifier to rename the fields (Stevet540)

    If you want to know how, just replace the line
    $sql_query = “select * from $table”;
    with
    $sql_query = $table;
    and then pass the query when calling the function:
    exportMysqlToCsv(”select * from some_table”);

    This is not neat, or good code, but should work.

    Thanks for the tut. I found it very useful.

  7. Praveena Said,

    Thank u very much ,It’s very useful for me.

  8. Rishi Said,

    Thanks for this Great Code!

  9. Paul Said,

    Perfect. Excellent, simple solution.

    Rather than require the other file, I just combined the two php files into one. Any problems with that?

  10. Neetu Said,

    I want to this csv file as email attachment to mail on single address.
    Plz hlp me. Send me code on my email as soon as possible.

  11. shivaganesh Said,

    good stuff! thanks a lot..

  12. shivaganesh Said,

    Nice function. Thanks a lot

  13. jms Said,

    i’m trying to use your script here; looks to be pretty solid. however, i’m encountering some issuesw/ the headers…any help would be appreciated…i’m still a noob.

  14. Bảo Trân Said,

    Thanks a lot . It’s useful for me :-)

  15. jms Said,

    so, i left a comment about how i was having problems w/ the headers.
    (don’t see it here)

    people may run into the same issue i did (noob-sauce for reals) but here’s a tutorial/explaination for what was happening.

    http://www.kirupa.com/forum/showpost.php?p=2149084&postcount=3

    with the script provided here (awesome, btw) and that tutorial, you have everything (as a noob) to get this export to csv going. cheers.

  16. Piya Said,

    Hiii …
    This is very nice n useful code..
    but I actually want to store file on localhost after exporting ..
    can u tell me wht can I do for it??

    Thanks in advance

  17. nard Said,

    what if i want to export only some fields from the table?

  18. Neetu Said,

    plz help me to attach csv file in as emailattachment to send on specified email address only.

  19. tech Said,

    Nard, if you want to export some fields, replace the line

    $sql_query = “select * from $table”;

    with

    $sql_query = “select id, Name, Email from $table”;

    (where id, Name, Email are require fields)

  20. nard Said,

    thank you very much tech! solved my problem.

  21. Tiaan Said,

    I am having some problems:
    At first I had a problem with my database connection so the .csv download was presented but no data was in it.
    I have since fixed the connection string but now the data is just dumped into the browser instead of providing me with the download.
    Can someone please help!

  22. Ranvir Said,

    Thanks a lot for the post!! Really helped me.

  23. Casey Wise Said,

    Very nice! Thanks for taking the time to put this up!

  24. Memo457 Said,

    Greetings, Thank you so much for the scripts. I have been working for weeks trying to figure out how to get the array to work and then make it where it would take the CSV and place the field names at the top with seperators. If whoever created it were standing right next to me they would have a whopper from BK sitting on their desk for lunch and a big hug to boot. Thanks again.

  25. tulika Said,

    if i want to add some value in action of this file
    than what can i do

    this not work properly by me
    for example id of table in query

  26. Joseph Said,

    There is a issue with microsoft explorer downloading files from
    a site using SSL (secure socket layer). In order to get it to work
    you must add this additional header:

    Header(’Pragma: public’);

  27. JuJuBean Said,

    This just dumps to the browser display - no prompt for filesave or anything.

  28. likki Said,

    thanks for that code. is there any way to format the cell in csv(ex. from numeric to text)

  29. krishna raya Said,

    Thanks a ton!! it was very useful

  30. CC Said,

    How do you export text fields which contain blank lines?
    When exporting in windows (IIS), the blank lines are converted into a funny symbol.
    Thanks

  31. Laura Said,

    Can anyone explain how to save the file on the server instead of downloading?? Please help!!

    thanks…

  32. MC@llivon Said,

    wow its great thank you very much

  33. sagar Said,

    thank u very much but how can we export multiple table data into bone excel sheet

  34. saneesh k Said,

    i have tested the code available in this site..iam using php code..so how to implement this code
    for getting the output form of the data that has been stored in the database..that is the whole datastored in the perticular table….

Add A Comment