Export huge amount of data to CSV

[adwairi]

Hi all

I want to export data to CSV file, the data contain more than 20000 records, and an errors appear said:
"Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 80 bytes)"
OR
"Fatal error: Maximum execution time of 30 seconds exceeded".
I already increase the memory size and I wouldn't increase the execution time, I just want to export the data in multiple CSV files under .zip file.

note: I already use header() function in my code.

Thank you.

[Nullsig]

Where is the data stored?

[adwairi]

@ Nullsig
data returned from database as multidimensional array, this array contain more than 20000 nodes and each node consider as record

[adwairi]

really I need your help, just give me an Ideas .. no need for code.

[Nullsig]

Why don't you just dump the data directly from the database?

If that isn't possible because you have to process the data with a script before dumping it then you could do this to segregate the information over multiple files:

Code: Select all

$sql = "SELECT blah FROM blah WHERE blah blah blah";
if($rs = mysql_query($sql)){
   $i = 1; //this will be appended to the end of the file name.
   //This will be used to detect when to create a new file
   //initialize at zero to trigger the file creation at the beginning
   $j = 0;
   while($row = mysql_fetch_assoc($rs)){
      if($j % 10000 == 0){ //The 10000 here means that each file will contain 10000 rows
         if(isset($fp)){
            //first iteration the $fp variable will not be set,
            //therefore we will not try to close it.
            fclose($fp);
         }
         
         //initialize the current file
         $fp = fopen('randomFile' . $i . ".csv", w+);
         $i++;
         
         //technically you don't need to do this but for
         //REALLY large datasets you may overflow the integer value.
         //This also helps if you aren't iterating $j on every row
         $j = 1;
      }
      
      
      //Process your row and insert the processed data here
      
      
      $j++;//last line will iterate your current row count. This can be iterated on successful write to the file too.
   }
}
 

[adwairi]

@Nullsig
Yes I have to process the data with a script before.

Thank you Nullsig, I will try it now.