How to Export Data Into CSV Using PHP & Mysql

Hello Friends , after a long time I am writing this tutorial . Hopefully, in future, I will write regularly in future at least twice a week.

In this tutorial, we are going to learn how to export data from database in CSV format. It is a very necessary tutorial it can be beneficial in many of the project.


These are the certain conditions where I have used these code :-
1) Extracting all our available users from the database and giving details to the customer or client.
2) Extracting the product details in our warehouse.
there is numerous situation in which we can use this code in our project.

how to export data in csv using php and mysql

Let’s Start the tutorial . I have divided the tutorial into mainly four parts

1) Connection to the database.

2) Extracting the client data from database using the above connection.

3) Showing the saved data into the tabular format and giving a button to the export data.

4) Writing the code for the exporting the data and showing in the CSV format.

 

 

1) Connection to the database

In this step, we will simply write code for connection to the database using PHP PDO connection using the class in the config file. Code for connection are as follows:-

<?php /** * @author Abhay Pandey (abhayhk29@gmail.com) * Date : 27 OCT 2016 * Config class containing the database connectivity */ class DBConnection{ //Please specify your own database credentials private $host = "localhost"; private $db_name = "blog"; private $user_name = "root"; private $password = ""; public $connection = null; public function getConnectData(){ $this-&amp;gt;connection = null; try { $this-&amp;gt;connection = new PDO("mysql:host=". $this-&amp;gt;host . ";dbname=".$this-&amp;gt;db_name, $this-&amp;gt;user_name, $this-&amp;gt;password); } catch (Exception $exc) { echo "connection error please check your credentials ". $exc-&amp;gt;getTraceAsString(); } return $this-&amp;gt;connection; } public static function disconnection(){ self::$connection = null; } } ?>

If you want to use this code simply change database parameter with your credentials.

Extracting Data From Database

In this step, we will extract data from the database using our class. We will create a class in which simply fetch all the records from the database. We have used the above database connection to fetch the data from the database . Code for this class is as given below.

&amp;lt;?php

/**
 * @author Abhay Pandey &amp;lt;abhayhk29@gmail.com&amp;gt;
 * Date : 14 MAy 2016
 */

class ProductData{
    
    // DataBase connection Name and table NAme
    private $conn;
    private $tableName = 'product_details';
    
    
    public function __construct($db) {
        $this-&amp;gt;conn = $db;
    }

    public function readAllProductDetails(){
        $query = "select * from " . $this-&amp;gt;tableName . " ORDER BY product_id ";
        $stmt = $this-&amp;gt;conn-&amp;gt;prepare($query);
        $stmt-&amp;gt;execute();
        return $stmt;
    }
    
    //used for counting the element 
    
    public function countAllProduct(){
        $query = "select product_id from ".$this-&amp;gt;tableName;
        $stmt = $this-&amp;gt;conn-&amp;gt;prepare($query);
        $stmt-&amp;gt;execute();
        $num = $stmt-&amp;gt;rowCount();
        return $num;
    }
    
    /**
     * Fetching the data from the data base
     * @return array
     */
    public function exportDataFromDatabase(){
        $query = "select product_id,product_name,product_price, product_price , product_weight, product_sku, product_color, product_category from " . $this-&amp;gt;tableName;
        $stmt = $this-&amp;gt;conn-&amp;gt;prepare($query);
        $stmt-&amp;gt;execute();
        $row = $stmt-&amp;gt;fetchAll(PDO::FETCH_ASSOC);
        return $row;
    }
    
}

3) Showing the data into the view Part

In this part, we will show data into the tabular form after fetching the data from the database. Here we are providing an export button by using this button user can download the CSV. It will look like this:-

csv mysql php data

HTML for this page is as follows.

<html>
&amp;lt;!DOCTYPE html&amp;gt;
&amp;lt;!--
@email abhayhk29@gmail.com
--&amp;gt;
&amp;lt;html&amp;gt;
    &amp;lt;head&amp;gt;
        &amp;lt;meta charset="UTF-8"&amp;gt;
        &amp;lt;meta charset="utf-8"&amp;gt;
        &amp;lt;script src="bootstrap/js/jquery.min.js"&amp;gt;&amp;lt;/script&amp;gt;
        &amp;lt;link href="bootstrap/css/bootstrap.min.css" rel="stylesheet" media="screen"&amp;gt;
        &amp;lt;script src="bootstrap/js/bootstrap.min.js"&amp;gt;&amp;lt;/script&amp;gt;
        &amp;lt;title&amp;gt;How to Export Data into CSV from Mysql&amp;lt;/title&amp;gt;
    &amp;lt;/head&amp;gt;
        &amp;lt;?php
         include 'includes/config.php';
         include 'includes/dataProduct.php';
         
         $databaseConnection = new DBConnection();
         $db = $databaseConnection-&amp;gt;getConnectData();  
         $productDetails = new ProductData($db);
         
         $stmt = $productDetails-&amp;gt;readAllProductDetails();
         $num = $stmt-&amp;gt;rowCount();
        ?&amp;gt;
    &amp;lt;body&amp;gt;
        &amp;lt;form class="form-horizontal js-validation-material" id="export_client_information" name="export_client_information"  action="exportCSV.php" method="post"&amp;gt;
        &amp;lt;div class="container"&amp;gt;
            &amp;lt;div class="row"&amp;gt;
                &amp;lt;h3&amp;gt;Export Data Into CSV from Mysql&amp;lt;/h3&amp;gt;
            &amp;lt;/div&amp;gt;
            &amp;lt;p&amp;gt;
                &amp;lt;button class="btn btn-sm btn-primary" type="button" onclick="exportClientInformation()"&amp;gt;Export Data&amp;lt;/button&amp;gt;
            &amp;lt;/p&amp;gt;
            &amp;lt;div class="row"&amp;gt;
            &amp;lt;?php 
               if($num &amp;gt; 0){
            ?&amp;gt;
                &amp;lt;table class="table table-striped table-bordered"&amp;gt;
                  &amp;lt;thead&amp;gt;
                    &amp;lt;tr&amp;gt;
                      &amp;lt;th&amp;gt;ID&amp;lt;/th&amp;gt;
                      &amp;lt;th&amp;gt;Product Name&amp;lt;/th&amp;gt;
                      &amp;lt;th&amp;gt;Product SKU&amp;lt;/th&amp;gt;
                      &amp;lt;th&amp;gt;Product Price&amp;lt;/th&amp;gt;
                      &amp;lt;th&amp;gt;Product Weight&amp;lt;/th&amp;gt;
                      &amp;lt;th&amp;gt;Product Category&amp;lt;/th&amp;gt;
                    &amp;lt;/tr&amp;gt;
                  &amp;lt;/thead&amp;gt;
                  &amp;lt;tbody&amp;gt;
                  &amp;lt;?php
                            while ($row = $stmt-&amp;gt;fetch(PDO::FETCH_ASSOC)){
                                    extract($row);
                                    ?&amp;gt;
                                    &amp;lt;tr&amp;gt;
                                    &amp;lt;?php echo "&amp;lt;td&amp;gt;{$product_id}&amp;lt;/td&amp;gt;" ?&amp;gt;
                                    &amp;lt;?php echo "&amp;lt;td&amp;gt;{$product_name}&amp;lt;/td&amp;gt;" ?&amp;gt;
                                    &amp;lt;?php echo "&amp;lt;td&amp;gt;{$product_sku}&amp;lt;/td&amp;gt;" ?&amp;gt;
                                    &amp;lt;?php echo "&amp;lt;td&amp;gt;{$product_price}&amp;lt;/td&amp;gt;" ?&amp;gt;
                                    &amp;lt;?php echo "&amp;lt;td&amp;gt;{$product_weight}&amp;lt;/td&amp;gt;" ?&amp;gt;
                                    &amp;lt;?php echo "&amp;lt;td&amp;gt;{$product_category}&amp;lt;/td&amp;gt;" ?&amp;gt;
                                    &amp;lt;/tr&amp;gt;
                            &amp;lt;?php } ?&amp;gt;
                  &amp;lt;/tbody&amp;gt;
            &amp;lt;/table&amp;gt;
            &amp;lt;?php
            }
            ?&amp;gt;
        &amp;lt;/div&amp;gt;
    &amp;lt;/div&amp;gt;
        &amp;lt;/form&amp;gt;
        &amp;lt;script&amp;gt;
        function exportClientInformation(){
             $( "#export_client_information" ).submit();
        }
        
        &amp;lt;/script&amp;gt;
    &amp;lt;/body&amp;gt;
&amp;lt;/html&amp;gt;

4) Exporting the data into CSV

This will be the final step in this step we will extract the data from the database using the above database connection parameter and will display into CSV format . After exporting data will look this:-

CSV EXPORT DATA

The output will look the above picture according to the data stored in your table . If we want to give any filter option we can provide that also, for example, we want to export data only of the October month. For that we can provide a date picker , from that user will select the range of dates. And we will output the data according to that. I will add this functionality in future.
For CSV exporting code will be like this :-

&amp;lt;?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

include '/includes/config.php';
include '/includes/dataProduct.php';


$dataBaceConnection = new DBConnection();
$db = $dataBaceConnection-&amp;gt;getConnectData();

$productDetails = new ProductData($db);
$cols = array('Product ID','Product Name', 'Product Price', 'Product Weight','Product SKU', 'Product Color','Product Category');
$fileName = 'DataInCSV-' . date('YmdHi') . '.csv';
$data = $productDetails-&amp;gt;exportDataFromDatabase();
if (count($data) &amp;gt; 0) {
    header("Content-type: application/octet-stream");
    header("Content-Disposition: attachment; filename=\"$fileName\"");

    $fp = fopen('php://output', 'w');
    fputcsv($fp, $cols);

    foreach ($data as $row) {
        fputcsv($fp, $row);
    }
    fclose($fp);
    exit;
}else{
    header("Content-type: application/octet-stream");
    header("Content-Disposition: attachment; filename=\"$fileName\"");

    $fp = fopen('php://output', 'w');
    $cols1 = array('No Data');
    fputcsv($fp, $cols1);
    $data = array('No data in this range');
        fputcsv($fp, $data);
    fclose($fp);
    exit;
}

Hopefully, you have all enjoyed the tutorial . Please give your valuable comment for improving the tutorials. Thanks

13

No Responses

Write a response