How to Export MySQL Table data as CSV file in PHP

The phpMyAdmin allows us the number of ways to export the Table data one of the ways is CSV(Comma Separated Value).

There are two ways to create a CSV file with PHP –

  • By comma separated string, or
  • using fputcsv() method

I am using the fputcsv() method (writes an array to the file line by line) in the demonstration, where I am listing MySQL table data in the Tabular format and a button to export data.

When the export button gets clicked then creating a new CSV file and downloading it.

http://makitweb.com/how-to-export-mysql-table-data-as-csv-file-in-php/


Contents

  1. Table structure
  2. Configuration
  3. HTML and PHP
  4. Create and Download CSV file
  5. Conclusion

 

1. Table structure

I am using users table in the tutorial example.

CREATE TABLE `users` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `username` varchar(80) NOT NULL,
  `name` varchar(50) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `email` varchar(70) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

2. Configuration

Create a new config.php file.

Completed Code

<?php

$host = "localhost";    /* Host name */
$user = "root";         /* User */
$password = "";         /* Password */
$dbname = "tutorial";   /* Database name */

$con = mysql_connect($host, $user, $password) or die("Unable to connect");

// selecting database
$db = mysql_select_db($dbname, $con) or die("Database not found");

3. HTML and PHP

Create a <form> that have a table layout, Textarea, and submit button.

Fetch and display users Table data in <table>. Create an Array variable which initializes while creating a new row.

Store the array value in the <textarea> after serializing the data using serialize() function.

Handle <form > submit on download.php page.

Completed code

<?php 
include "config.php";
?>

<div class="container">
 
 <form method='post' action='download.php'>
  <input type='submit' value='Export' name='Export'>
 
  <table border='1' style='border-collapse:collapse;'>
    <tr>
     <th>ID</th>
     <th>Username</th>
     <th>Name</th>
     <th>Gender</th>
     <th>Email</th>
    </tr>
    <?php 
     $query = "SELECT * FROM user3 ORDER BY id asc";
     $result = mysql_query($query);
     $user_arr = array();
     while($row = mysql_fetch_array($result)){
      $id = $row['id'];
      $uname = $row['username'];
      $name = $row['name'];
      $gender = $row['gender'];
      $email = $row['email'];
      $user_arr[] = array($id,$uname,$name,$gender,$email);
   ?>
      <tr>
       <td><?php echo $id; ?></td>
       <td><?php echo $uname; ?></td>
       <td><?php echo $name; ?></td>
       <td><?php echo $gender; ?></td>
       <td><?php echo $email; ?></td>
      </tr>
   <?php
    }
   ?>
   </table>
   <?php 
    $serailze_user_arr = serialize($user_arr);
   ?>
  <textarea name='export_data' style='display: none;'><?php echo $serailze_user_arr; ?></textarea>
 </form>
</div>

4. Create and Download CSV file

Create a new download.php file.

Unserialize the $_POST['export_data'] value using unserialize() function which return an array and using this to insert data in open file using fputcsv() method.

Within fputcsv() passing two parameters –

  • File variable and
  • An array value.

After successfully file creation preparing it for download and delete it after downloading using unlink() method.

Completed code

<?php
$filename = 'users.csv';
$export_data = unserialize($_POST['export_data']);

// file creation
$file = fopen($filename,"w");

foreach ($export_data as $line){
 fputcsv($file,$line);
}

fclose($file);

// download
header("Content-Description: File Transfer");
header("Content-Disposition: attachment; filename=$filename");
header("Content-Type: application/csv; "); 

readfile($filename);

// deleting file
unlink($filename);
exit();

 

5. Conclusion

I exported a MySQL table data for this created an array of the content and write to the open file with fputcsv().

You can use it to generate a CSV output of a report.

If you want to know How you can import a CSV file data into MySQL table using PHP, for this you can check out my earlier post on this – Import CSV file data to the MySQL using PHP.

Related Post

Spread the love

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *