How to Export MySQL Table data as CSV file in PHP

The phpMyAdmin allows us different ways to export the Table data. One of a method 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 download 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 to define database connection.

Completed Code

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

$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
 die("Connection failed: " . mysqli_connect_error());
}

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 users ORDER BY id asc";
     $result = mysqli_query($con,$query);
     $user_arr = array();
     while($row = mysqli_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 to import a CSV file data into MySQL database table you can view my eariler tutorial.

Related Post

Spread the love
  • 2
  •  
  •  
  •  
  • 1
  •  
  •  

Be First to Comment

Leave a Reply

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