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).

In this post, I show How you can export MySQL table data as CSV file in PHP.

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

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

In this tutorial, I am using the fputcsv() method which writes an array to the file line by line.

What are we making?

Listing MySQL table data in Tabular format and a button for exporting data. When the export button gets clicked then creating a new CSV file and downloading it.

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

For exporting I am using users table.

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

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

Displaying users Table data in <table> layout and while creating a new row for <table> initialize an Array. Serializing this array using serialize() function and storing it to <textarea>.

This is done because we don’t have the need to fetch data on more time in another page(download.php) after <form> submit.

Completed code

<?php 
include "config.php";
?>
<!doctype html>
<html>
 <head>
 <title>How to Export MySQL Table data as CSV file in PHP</title>
 <link href="style.css" rel="stylesheet" type="text/css">

 </head>
 <body>
 <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>
 </body>
</html>

 


4. Create and Download CSV file

Create a new download.php file.

Unserialize the $_POST[‘export_data’] using unserialize() function which takes serialize data and get an array format and using this for inserting data in open file using fputcsv() method.

Within fputcsv() passing two parameters, one is file-variable and another is array value.

After successfully created preparing it to 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

In this tutorial, I showed you How to create a CSV file and download it. I used fputcsv() method to add a new row in the file.

We exported a MySQL table data for this created an array of the content and looping through it and writing in the open file.

You can also use this in many other ways, for example, generating 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.

Spread the love

Related Post

Be First to Comment

Leave a Reply

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