Export Data to CSV and Download Using PHP and MySQL

in previous tutorial we have learn How To Export Data to Excel,here in this article we will learn how to export data into CSV (comma-separated values) file and download it, CSV is a very common format for transferring tabular data between applications and databases.

You can also check other tutorial of Export Data with PHP,

Here we have sample result set from MySQL and want to export in CSV file.

Step 1: Get MySql data in key value pair.

Select Code
1
2
3
4
5
6
7
8
$data = array(
             '0' => array('Name'=> 'Parvez', 'Status' =>'complete', 'Priority'=>'Low', 'Salary'=>'001'),
             '1' => array('Name'=> 'Alam', 'Status' =>'inprogress', 'Priority'=>'Low', 'Salary'=>'111'),
             '2' => array('Name'=> 'Sunnay', 'Status' =>'hold', 'Priority'=>'Low', 'Salary'=>'333'),
             '3' => array('Name'=> 'Amir', 'Status' =>'pending', 'Priority'=>'Low', 'Salary'=>'444'),
             '4' => array('Name'=> 'Amir1', 'Status' =>'pending', 'Priority'=>'Low', 'Salary'=>'777'),
             '5' => array('Name'=> 'Amir2', 'Status' =>'pending', 'Priority'=>'Low', 'Salary'=>'777')
            );

Step 2: PHP code to get options type and force to browser download file instead of display.

Select Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
if(isset($_POST["ExportType"]))
{
     
    switch($_POST["ExportType"])
    {
        case "export-to-csv" :
            // Submission from
            $filename = $_POST["ExportType"] . ".csv";       
            header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
            header("Content-type: text/csv");
            header("Content-Disposition: attachment; filename=\"$filename\"");
            ExportCSVFile($data);
            //$_POST["ExportType"] = '';
            exit();
        default :
            die("Unknown action : ".$_POST["action"]);
            break;
    }
}
function ExportCSVFile($records) {
    // create a file pointer connected to the output stream
    $fh = fopen( 'php://output', 'w' );
    $heading = false;
        if(!empty($records))
          foreach($records as $row) {
            if(!$heading) {
              // output the column headings
              fputcsv($fh, array_keys($row));
              $heading = true;
            }
            // loop over the rows, outputting them
             fputcsv($fh, array_values($row));
             
          }
          fclose($fh);
}

Here we are setting header information to tell the browser that we are producing a CSV file and that the file should be offered for download.

Step 3: Define html layout for display data in table and button to fire export-to-csv action.

Select Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<div><a href="javascript:void(0)" id="export-to-csv">Export to csv</a></div>
 <form action="<?php echo $_SERVER["PHP_SELF"]; ?>" method="post" id="export-form">
                        <input type="hidden" value='' id='hidden-type' name='ExportType'/>
                      </form>
                  <table id="" class="table table-striped table-bordered">
                    <tr>
                        <th>Name</th>
                        <th>Status</th>
                        <th>Priority</th>
                        <th>Salary</th>
                  </tr>
                <tbody>
                  <?php foreach($data as $row):?>
                  <tr>
                  <td><?php echo $row ['Name']?></td>
                  <td><?php echo $row ['Status']?></td>
                  <td><?php echo $row ['Priority']?></td>
                  <td><?php echo $row ['Salary']?></td>
                  </tr>
                  <?php endforeach; ?>
                </tbody>
              </table>

Step 3: Now we will use jQuery code to get click event.

Select Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<script  type="text/javascript">
$(document).ready(function() {
jQuery('#export-to-csv').bind("click", function() {
var target = $(this).attr('id');
switch(target) {
    case 'export-to-csv' :
    $('#hidden-type').val(target);
    //alert($('#hidden-type').val());
    $('#export-form').submit();
    $('#hidden-type').val('');
    break
}
});
    });
</script>

Result:

How to export data using php

I hope its help you!.

Demo and Download Source Code Of Export to CSV