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

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,

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