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.

<?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

<?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;

}

?>

Related Posts

  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.

Add A Comment