Solved

[php, mysql] MySQL to CSV download too large

Posted on 2012-03-15
7
451 Views
Last Modified: 2012-03-16
I have a script that queries two tables in my database and grabs a majority of their columns of data and puts that data into a CSV file that is downloaded to a user's computer. Or at least that is what it is supposed to do. Below is my code:

$select = "SELECT foo.foo_id, serial, sku, foo_type, foo_mac, foo_sn, foo_mac, foo_sn, foo_vendor, foo_mdn, foo_hex, foo_dec, ship_to_foo, install_foo, foo.fooid, foo.chain, store, address, tech, bar.fooid, bar.chain, barid, address1, address2, city, state, zip     
    FROM foo, bar";
$export = mysql_query ( $select ) or die ( "Sql error : " . mysql_error( ) );
$fields = mysql_num_fields ( $export );

for ( $i = 0; $i < $fields; $i++ )
{
    $header .= mysql_field_name( $export , $i ) . "\t";
}

while( $row = mysql_fetch_row( $export ) )
{
    $line = '';
    foreach( $row as $value )
    {                                            
        if ( ( !isset( $value ) ) || ( $value == "" ) )
        {
            $value = "\t";
        }
        else
        {
            $value = str_replace( '"' , '""' , $value );
            $value = '"' . $value . '"' . "\t";
        }
        $line .= $value;
    }
    $data .= trim( $line ) . "\n";
}
$data = str_replace( "\r" , "" , $data );

if ( $data == "" )
{
    $data = "\n(0) Records Found!\n";                        
}

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=foobar.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";
?>

Open in new window


The error I am getting is this:

Fatal error: Allowed memory size of 104857600 bytes exhausted (tried to allocate 104333464 bytes) in /home/foobar/public_html/fbar/_csv.php on line 35

Open in new window


Not sure what to do to fix or address this. Thanks so much!
0
Comment
Question by:prileyosborne
7 Comments
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 37727315
You probably need to increase "memory_limit" in 'php.ini'.  If you are on shared hosting and can't do that, then you need to break your CSV export into smaller chunks that can be used after downloading them.
0
 
LVL 9

Expert Comment

by:rinfo
ID: 37728716
have you tried to put this line before start of the script
ini_set('memory_limit', '256M');
This should make php.ini memory limit to 256M temporarily and your script can execute
within this limit.
At present it seems to be set for 100M.
0
 
LVL 109

Assisted Solution

by:Ray Paseur
Ray Paseur earned 500 total points
ID: 37728723
Learn about this function.
http://php.net/manual/en/function.fputcsv.php

Your application design should make the SELECT, and use fputcsv() to write the rows of data to a temporary file.  Then when the file has been created and the query results set has been exhausted, you have your CSV file ready to go to the browser.  Issue the headers, then read and feed the contents of the file.  By doing it this way you will not need to hold the entire query results set in memory at the same time.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:prileyosborne
ID: 37730658
Thank you all so much for your responses. I tried to set the file size max to a higher number, but that didn't work.

I reviewed the link from Ray, and that seems to have worked best. I have code that is writing to a file on my server, and then the headers are downloading the file upon completion of the SQL statement.

The problem I am having is twofold:

1. The script is actually loading thousands of the exact same record, instead of thousands of individual records.

2. The downloaded file shows nothing in Excel, but when I open the live server version in something like Sublime Text, I can see all the record data.

Below is my updated script:

$csvfile = fopen("cs_data.csv",'w');  

$select = "SELECT foo.foo_id, serial, sku, foo_type, foo_mac, foo_sn, foo_mac, foo_sn, foo_vendor, foo_mdn, foo_hex, foo_dec, ship_to_foo, install_foo, foo.fooid, foo.chain, store, address, tech, bar.fooid, bar.chain, barid, address1, address2, city, state, zip     
    FROM foo, bar";
$result  = mysql_query ( $select ) or die ( "Sql error : " . mysql_error( ) );
$row_num = mysql_num_rows ( $result );
$db_data = mysql_fetch_array ( $result );

for ( $i = 1; $i <= $row_num; $i++ )  
{  
    if ($i <= $row_num) {
        // Write SELECT data to file
        fputcsv($csvfile, $db_data, ',', '"');
    }
}  

// Close File  
fclose($csvfile); 

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=cs_data.csv");
header("Pragma: no-cache");
header("Expires: 0");

Open in new window


Thanks for any help you can offer!
0
 
LVL 109

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 37730745
Maybe something more like this...  (Did you notice that you are selecting the same columns more than once?)
$csvfile = fopen("cs_data.csv",'w');  

$sql 
= "SELECT 
  foo.foo_id
, serial
, sku
, foo_type
, foo_mac
, foo_sn
, foo_mac
, foo_sn
, foo_vendor
, foo_mdn
, foo_hex
, foo_dec
, ship_to_foo
, install_foo
, foo.fooid
, foo.chain
, store
, address
, tech
, bar.fooid
, bar.chain
, barid
, address1
, address2
, city
, state
, zip 

  FROM 
  foo
, bar
"
;
$result  = mysql_query ( $select ) or die ( "FAIL $sql <br/>" . mysql_error( ) );

// ITERATE OVER THE RESULTS SET
while ($row = mysql_fetch_assoc($result))
{
    fputcsv($csvfile, $row, ',', '"');
}  

// Close File  
fclose($csvfile); 

Open in new window

Intstead of writing headers, you could give the client a URL link to the CSV file you have just written to the server.  The file extension CSV is almost universally associated with Excel.
http://filext.com/file-extension/CSV
0
 

Author Closing Comment

by:prileyosborne
ID: 37731071
That worked perfectly! Thanks so much!
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 37731275
Thanks for the points, and thanks for using EE!  It's a great question, ~Ray
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to change the link of an image using md5 in php ? 3 47
hbo knew my windows software 4 62
Email called spam 5 36
check mysql insert 12 27
Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question