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(); 
} 

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(); 
}