PHP Classes

PHP MySQL Query CSV Export and Download: Export MySQL query results as a CSV file

Recommend this page to a friend!
  Info   View files Documentation   View files View files (3)   DownloadInstall with Composer Download .zip   Reputation   Support forum (1)   Blog    
Last Updated Ratings Unique User Downloads Download Rankings
2023-04-09 (10 months ago) RSS 2.0 feedNot yet rated by the usersTotal: 87 All time: 9,953 This week: 104Up
Version License PHP version Categories
heavycsvexport 1.0MIT/X Consortium ...5PHP 5, Databases, Files and Folders, U..., C...
Description 

Author

This class can export MySQL query results as a CSV file.

It can execute a given SQL query using the MySQL command line shell program to save the query results as a CSV file to avoid exceeding the PHP memory limit, especially when the query results return over 30,000 records.

The package can serve the generated CSV file to let users download it.

Picture of Ramesh Narayan Jangid
  Performance   Level  
Name: Ramesh Narayan Jangid <contact>
Classes: 6 packages by
Country: India India
Age: 48
All time rank: 3405225 in India India
Week rank: 40 Up6 in India India Up
Innovation award
Innovation award
Nominee: 2x

Winner: 1x

Instructions

Large CSV export

If you are facing difficulty in performing large MySQL query export to CSV operation in PHP; then this is the class for you.

Steps to perform this.

Configure your connection

define('HOSTNAME', '127.0.0.1'); 
define('USERNAME', 'username'); 
define('PASSWORD', 'password'); 
define('DATABASE', 'database'); 

Prepare your parameterised query

$sql = "
    SELECT
        column1 as COLUMN1,
        column2 as COLUMN2,
        column3 as COLUMN3,
        column4 as COLUMN4
    FROM
        TABLE_NAME
    WHERE
        column5 = :column5
        column6 LIKE CONCAT('%' , :column6, '%');
        column7 IN (:column7);
";

Prepare your parameterised params array

$params = [
    ':column5' => 'column5_value',
    ':column6' => 'column6_search_value',
    ':column7' => [
        'column7_value1',
        'column7_value2',
        'column7_value3'
    ]
];

Execute and export.

$csvFilename = 'export.csv'; 

try { 
  $mySqlCsv = new downloadCSV(); 
  $mySqlCsv->connect(HOSTNAME, USERNAME, PASSWORD, DATABASE);
  $mySqlCsv->useTmpFile = false; // defaults true for large data export.
  $mySqlCsv->initDownload($csvFilename, $sql, $params);
} catch (\Exception $e) { 
  echo $e->getMessage(); 
} 

Documentation

 

Details

When it comes to Download CSV, most developer faces the issue of memory limit, especially when supporting downloads of more than 30,000 records at a time.

This class solves the issue by executing a "MySQL Client" command on the server via the PHP script.

Using this class one can download the records without any issue. There is no limit to number of records returned by the SQL query. The CSV output is available for download or saved into filesystem depending on configuration.

For heavy CSV downloads one can enable compression on the web server. In NGINX, one can use the gzip_types directive to specify CSV mime-types should be compressed as below.

http { 
# ... 

gzip on; 
gzip_types text/plain text/csv; 

# ... 
} 

Above configuration will compress the content of the CSV file on fly, which can significantly reduce the time for downloading the CSV.

<?php 
//Example: 
define('HOSTNAME', '127.0.0.1'); 
define('USERNAME', 'username'); 
define('PASSWORD', 'password'); 
define('DATABASE', 'database'); 

// Non Parameterised query.

$sql = "
    SELECT
        column1 as COLUMN1,
        column2 as COLUMN2,
        column3 as COLUMN3,
        column4 as COLUMN4
    FROM
        TABLE_NAME
";
$csvFilename = 'export.csv'; 

try { 
  $mySqlCsv = new downloadCSV(); 
  $mySqlCsv->connect(HOSTNAME, USERNAME, PASSWORD, DATABASE);
  $mySqlCsv->useTmpFile = false; // defaults true for large data export.
  $mySqlCsv->initDownload($csvFilename, $sql);
} catch (\Exception $e) { 
  echo $e->getMessage(); 
} 

// Parameterised query.

$sql = "
    SELECT
        column1 as COLUMN1,
        column2 as COLUMN2,
        column3 as COLUMN3,
        column4 as COLUMN4
    FROM
        TABLE_NAME
    WHERE
        column5 = :column5
        column6 LIKE CONCAT('%' , :column6, '%');
        column7 IN (:column7);
";
$params = [
    ':column5' => 'column5_value',
    ':column6' => 'column6_search_value',
    ':column7' => [
        'column7_value1',
        'column7_value2',
        'column7_value3'
    ]
];
$csvFilename = 'export.csv'; 

try { 
  $mySqlCsv = new downloadCSV(); 
  $mySqlCsv->connect(HOSTNAME, USERNAME, PASSWORD, DATABASE);
  $mySqlCsv->useTmpFile = false; // defaults true for large data export.
  $mySqlCsv->initDownload($csvFilename, $sql, $params);
} catch (\Exception $e) { 
  echo $e->getMessage(); 
} 

// To initiate downlaod as well as save the CSV output in filesystem, one can use below code.

$csvAbsoluteFilePath = '/folder path where to export/export.csv'; 

try { 
  $mySqlCsv = new downloadCSV();
  $mySqlCsv->connect(HOSTNAME, USERNAME, PASSWORD, DATABASE);
  $mySqlCsv->initDownload($csvFilename, $sql, $params, $csvAbsoluteFilePath);
} catch (\Exception $e) { 
  echo $e->getMessage(); 
} 

// For creating a CSV file in filesystem and not browser download, one can use below code.

$csvAbsoluteFilePath = '/folder path where to export/export.csv'; 

try { 
  $mySqlCsv = new downloadCSV(); 
  $mySqlCsv->connect(HOSTNAME, USERNAME, PASSWORD, DATABASE);
  $mySqlCsv->saveCsvExport($csvAbsoluteFilePath, $sql, $params);
} catch (\Exception $e) { 
  echo $e->getMessage(); 
}

  Files folder image Files  
File Role Description
Plain text file downloadCSV.php Class Download CSV via shell.
Accessible without login HTML file documentation.html Doc. Documentation
Accessible without login Plain text file Readme.md Doc. Readme

 Version Control Unique User Downloads Download Rankings  
 0%
Total:87
This week:0
All time:9,953
This week:104Up