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.

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.

<?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 
    $serialize_user_arr = serialize($user_arr);
   ?>
  <textarea name='export_data' style='display: none;'><?php echo $serialize_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 returns 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 a MySQL database table you can view my earlier tutorial.
 
If you found this tutorial helpful then don't forget to share.