PHP Classes

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

Recommend this page to a friend!

  Author Author  
Picture of Ramesh Narayan Jangid
Name: Ramesh Narayan Jangid is available for providing paid consulting. Contact Ramesh Narayan Jangid .
Classes: 4 packages by
Country: India India
Age: 47
All time rank: 3732256 in India India
Week rank: 8 Up1 in India India Up
Innovation award
Innovation award
Nominee: 2x

Winner: 1x


  Detailed description   Download Download .zip .tar.gz  
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.

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

  Classes of Ramesh Narayan Jangid  >  PHP MySQL Query CSV Export and Download  >  Download Download .zip .tar.gz  >  Support forum Support forum (1)  >  Blog Blog  >  RSS 1.0 feed RSS 2.0 feed Latest changes  
Name: PHP MySQL Query CSV Export and Download
Base name: heavycsvexport
Description: Export MySQL query results as a CSV file
Version: -
PHP version: 5
License: MIT/X Consortium License
All time users: 84 users
All time rank: 9929
Week users: 1 user
Week rank: 257 Up
 
  Groups   Rate classes User ratings   Applications   Files Files  

  Groups  
Group folder image PHP 5 Classes using PHP 5 specific features View top rated classes
Group folder image Databases Database management, accessing and searching View top rated classes
Group folder image Files and Folders Listing, accessing and manipulating files and folders View top rated classes
Group folder image Unix Use of capabilities specific of POSIX, Unix like operating systems View top rated classes
Group folder image Console Command line and console utilities View top rated classes


  User ratings  
Not yet rated by the users

  Applications that use this package  
No pages of applications that use this class were specified.

Add link image If you know an application of this package, send a message to the author to add a link here.

  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

Download Download all files: heavycsvexport.tar.gz heavycsvexport.zip
NOTICE: if you are using a download manager program like 'GetRight', please Login before trying to download this archive.
  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

Download Download all files: heavycsvexport.tar.gz heavycsvexport.zip
NOTICE: if you are using a download manager program like 'GetRight', please Login before trying to download this archive.