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

This class solves the issue by executing a MySql command on the server via the PHP script.

Using this class one can download the records in one go. There is no limit to number of rows returned by SQL query.

A regex is applied to the SQL output for making a compatible CSV. 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 which MIME types should be compressed.

http { 
# ... 

gzip on; 
gzip_types text/plain text/csv; 

# ... 
} 

This 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'); 

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