How to Export and Download the Mysql Table data to CSV File in PHP

Hi, You will learn in this tutorial How to Export and Download the Mysql Table data to CSV File in PHP. Sometimes you need to Export the user's data to CSV file because if a client wants to send the emails from the Autoresponders like Aweber, GetResponse, Mailchimp, etc..

In this case, you need to provide the user's contact information in the form of CSV file. It is a comma separated values so the human can read easily and exported CSV file can be imported on the Auto responders easily.

Related to Read : How to Upload CSV File into Database Using PHP and MYSQL

MySql Users Table

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL,
  `user_first_name` varchar(100) NOT NULL,
  `user_last_name` varchar(100) NOT NULL,
  `user_email` varchar(255) NOT NULL,
  `user_country` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `users`
  ADD PRIMARY KEY (`user_id`);

ALTER TABLE `users`
  MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT;

Exporting the MySql table data to CSV file follow the step by step process

Step #1: Connect to the MySql Database in PHP
First let's connect to the database using the mysqli() object.

 <?php  
 $servername = "localhost";  
 $username = "root";  
 $password = "";  
 $dbname = "leads";  
 // Create connection  
 $con = new mysqli($servername,$username, $password, $dbname);  
 // Check connection  
 if ($con->connect_error) {  
 die("Connection failed: " . $con->connect_error);  
 }  
 ?>

Step #2: Select the table and its columns from the MySql database
After connecting to the database, we need to fetch the records from the table.
If you want to export the entire table then execute this SQL Query "SELECT * FROM `users`"
otherwise, you need the custom columns then specify the column names in the Query "SELECT `user_first_name`,`user_email` FROM `users`". Here I am taking the entire table 'users'.

 <?php  
 // Fetch Records From Table  
 $sql = "SELECT * FROM `users`";  
 $result = $con->query($sql);  
 ?>

Step #3: Converting the MySql Results to CSV file format
Now we have to convert the fetched records into CSV file one by one. First, we need to write the table headings after that write the each row of data into the CSV file.

 <?php  
 $output = "";  
 // Get The Field Names from the table  
 while ($fieldinfo=$result->fetch_field())  
 {  
 $output .= '"'.$fieldinfo->name.'",';  
 }  
 $output .="\n";  
 // Get Records from the table  
 while ($row = $result->fetch_array()) {  
 for ($i = 0; $i < $columns_total; $i++) {  
 $output .='"'.$row["$i"].'",';  
 }  
 $output .="\n";  
 }  
 ?>

Here each and every table records are saved in the form of the comma separated value format in the $output.

Step #4: Download the Exported CSV file.
Here we need to specify the CSV file name and add the PHP headers application/csv and then add the attachment header this will force to downloaded the CSV file in the Browser.

 <?php   
 // Download the CSV file  
 $filename = "myFile.csv";  
 header('Content-type: application/csv');  
 header('Content-Disposition: attachment; filename='.$filename);  
 echo $output;  
 ?>

Complete PHP code to Export and Download the MySql Table data to CSV File

 <?php  
 $servername = "localhost";  
 $username = "root";  
 $password = "";  
 $dbname = "leads";  
 // Create connection  
 $con = new mysqli($servername,$username, $password, $dbname);  
 // Check connection  
 if ($con->connect_error) {  
 die("Connection failed: " . $con->connect_error);  
 }  
 // Fetch Records From Table  
 $output = "";  
 $sql = "SELECT * FROM `users`";  
 $result = $con->query($sql);  
 $columns_total = mysqli_num_fields($result);  
 // Get The Field Names from the table  
 while ($fieldinfo=$result->fetch_field())  
 {  
 $output .= '"'.$fieldinfo->name.'",';  
 }  
 $output .="\n";  
 // Get Records from the table  
 while ($row = $result->fetch_array()) {  
 for ($i = 0; $i < $columns_total; $i++) {  
 $output .='"'.$row["$i"].'",';  
 }  
 $output .="\n";  
 }  
 // Download the CSV file  
 $filename = "myFile.csv";  
 header('Content-type: application/csv');  
 header('Content-Disposition: attachment; filename='.$filename);  
 echo $output;  
 exit;  
 ?>

Here you can download the Full Source code and check the demo.

Download Demo

* If you like this post please don’t forget to subscribe Techies Badi - programming blog for more useful stuff

Share this