How to Export Data from MySQL Table to CSV File in PHP

On 5/29/2015

Hi! We'll see in this PHP Tutorial, How to Export Data from MySQL to CSV File in PHP. It's not uncommon to import and export data from database management system. Sometimes you may want to export the data from database into CSV format as the term CSV stands for comma separated value and it's a good human readable format like JSON. PHP 5.1 and above supports the csv formatting function fputcsv(), and combining it with PHP's file handling functions we can very easily convert the mysql table to a csv file.

Related Read: How to Import CSV File into MySQL Database using PHP

Create Example MySQL Database Table

First of all run this sql statement to create mysql table to use as an example for this tutorial.

-- Database: `db_books`

CREATE TABLE IF NOT EXISTS `tbl_books` (
  `id` int(8) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `author` varchar(30) NOT NULL,
  `isbn` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

INSERT INTO `tbl_books` (`id`, `name`, `author`, `isbn`) VALUES
(1, 'Learning PHP, MySQL & JavaScript', 'Robin Nixon', 'ISBN-13: 978-1491918661'),
(2, 'PHP and MySQL for Dynamic Web Sites', 'Larry Ullman', 'ISBN-13: 978-0321784070'),
(3, 'PHP Cookbook', 'David Sklar', 'ISBN-13: 978-1449363758'),
(4, 'Programming PHP', 'Kevin Tatroe', 'ISBN-13: 978-1449392772'),
(5, 'Modern PHP: New Features and Good Practices', 'Josh Lockhart', 'ISBN-13: 978-1491905012');

Export MySQL Data to CSV File using PHP

To convert the mysql data to csv file go through the below steps one by one.

Step-1: Establish MySQL DB Connection in PHP

First let's establish the database connection to mysql using php function mysqli_connect().

<?php
    // mysql database connection details
    $host = "localhost";
    $username = "admin";
    $password = "root";
    $dbname = "db_books";

    // open connection to mysql database
    $connection = mysqli_connect($host, $username, $password, $dbname) or die("Connection Error " . mysqli_error($connection));
?>

Step-2: Select Database Records from MySQL Table

Once we established the connection to the database, we have to fetch the records from the mysql table which we want to export in csv format. Say we want to export the books list from the table ‘tbl_books’, it can be done using php function mysqli_query().

<?php
    // fetch mysql table rows
    $sql = "select * from tbl_books";
    $result = mysqli_query($connection, $sql) or die("Selection Error " . mysqli_error($connection));
?>

Step-3: Convert MySQL Resultset to CSV File

Next we have to write down the mysql resultset records one by one to a csv file. For that we have to create a csv file, loop through the database resultset we got from step-2 and write one row at a time to the file.

<?php
    $fp = fopen('books.csv', 'w');

    while($row = mysqli_fetch_assoc($result))
    {
        fputcsv($fp, $row);
    }

    fclose($fp);
?>

fopen('books.csv', 'w') will create a csv file with the name ‘books’ and set to writing mode.

fputcsv($fp, $row) will format the data contained in ‘$row’ as CSV and write down it to the file handle ‘$fp’. Learn more about fputcsv() here.

fclose($fp) will close the opened file handler.

Step-4: Close MySQL Database Connection

After completely writing up the result set data to the csv file, finally close the database connection we have established in step-1.

<?php
    //close the db connection
    mysqli_close($connection);
?>

Here is the complete php snippet to convert mysql into csv format.

Complete PHP Code to Export MySQL Data to CSV File
<?php
    // mysql database connection details
    $host = "localhost";
    $username = "admin";
    $password = "root";
    $dbname = "db_books";

    // open connection to mysql database
    $connection = mysqli_connect($host, $username, $password, $dbname) or die("Connection Error " . mysqli_error($connection));
    
    // fetch mysql table rows
    $sql = "select * from tbl_books";
    $result = mysqli_query($connection, $sql) or die("Selection Error " . mysqli_error($connection));

    $fp = fopen('books.csv', 'w');

    while($row = mysqli_fetch_assoc($result))
    {
        fputcsv($fp, $row);
    }
    
    fclose($fp);

    //close the db connection
    mysqli_close($connection);
?>

You will get a ".csv" file with comma separated field values like this,

export-mysql-to-csv-file-example

With the help of the above php code, you can very easily Export MySQL Database to CSV File. If you want to learn the reverse process of importing the csv file into mysql database then check out this tutorial.

Recommended Read: How to Export MySQL Data to JSON File in PHP

Also Read: How to Insert JSON File Data into MySQL Database in PHP

11 comments:

  1. Checkout Great beginning php tutorials Very clear and helpful for beginners.

    ReplyDelete
  2. There is problem with this code in conversion from mysql data to the csv , the error shown are as follows..

    Warning: fopen(books.csv): failed to open stream: Permission denied in /opt/lampp/htdocs/mysql2csv.php on line 15

    Warning: fputcsv() expects parameter 1 to be resource, boolean given in /opt/lampp/htdocs/mysql2csv.php on line 19

    Warning: fputcsv() expects parameter 1 to be resource, boolean given in /opt/lampp/htdocs/mysql2csv.php on line 19

    Warning: fputcsv() expects parameter 1 to be resource, boolean given in /opt/lampp/htdocs/mysql2csv.php on line 19

    Warning: fputcsv() expects parameter 1 to be resource, boolean given in /opt/lampp/htdocs/mysql2csv.php on line 19

    Warning: fputcsv() expects parameter 1 to be resource, boolean given in /opt/lampp/htdocs/mysql2csv.php on line 19

    Warning: fclose() expects parameter 1 to be resource, boolean given in /opt/lampp/htdocs/mysql2csv.php on line 22

    ReplyDelete
    Replies
    1. Hi, it seems you don't have write access to the file. Please change the folder permission to 777 or similar (read & write) as administrator and try.

      Cheers :-)

      Delete
  3. i got the csv file but the data gets duplicating.

    ReplyDelete
    Replies
    1. The code works fine. Please make sure you don't have duplicate entries in the database. In such case, if you want to remove duplicated rows, you can filter out in the sql query.

      $sql = "select distinct(*) from tbl_books";

      Hope this helps.
      Cheers.

      Delete
  4. Hello,

    Thanks for the explanation. I am trying to put a header row before the rows that contain the data. I put fputcsv('SKU', 'Qty'); before the while loop and it doesn't seem to work. Any clue?

    ReplyDelete
    Replies
    1. Hi! Use it like this before while loop to add header row...

      fputcsv($fp, array('SKU', 'QTY'));

      Delete
  5. Hi,

    I have a problem. I have 5 columns. But i don't get line breaks. Everything is comma separated. And, the first row fifth column's data concatenates with the second row first column data. Please help.

    ReplyDelete
  6. It works completely fine but if i want to show the data in csv format on a webpage then what to do ?

    ReplyDelete
    Replies
    1. Hey! Your question is not clear. Do you want to embed csv file in the webpage directly or to just display the records with columns separated with ; ?

      If it's latter just loop through the resultset and use echo statement.

      Delete
  7. Hi, I tried to put this exporting function into button so user can click on button and export it but it is not working. Did i do smth wrong?





    ReplyDelete

Contact Form

Name

Email *

Message *