fputcsv fails with queries over 20k records

Question

Simply put, when im exporting query results to a csv, it goes smooth as silk till the query results reach about 20k-25k records.

When I hit that mark, I get a blank white screen and the file thats created only has the header row and nothing else. I pull the query back a little and it goes off fine, building the csv file with all data.

I've tried various records, queries and tables to make sure it wasn't the data so I'm at a loss. I can query fine and paginate easily over 1million records but the export still fails.

$file_name = "new_file";

//Set field column names
$fields[] = 'Submit Date';
$fields[] = 'Submit Time';
$fields[] = 'Client STN';
$fields[] = 'Incident Type';
$fields[] = 'Level 1';
$fields[] = 'Level 2';
$fields[] = 'Level 3';
$fields[] = 'Employee ID';

//Create file
$fp = fopen('/var/www/html/data_exports/'.$file_name.'.csv', 'a');
if ($fp && $result) 
{
    fputcsv($fp, $fields);
    foreach ($result->result_array() as $row)
    {   
        $values = array();
        $values[] = $row['submit_date'];
        $values[] = date("H:i:s", $row['submit_time']);
        $values[] = $row['stn'];
        $values[] = $row['menu_text'];
        $values[] = $row['lvl1_menu_item'];
        $values[] = $row['lvl2_menu_item'];
        $values[] = $row['lvl3_menu_item'];     
        $values[] = $row['employee_id'];

        fputcsv($fp, $values);
    }
}

redirect('admin/data_export_files');

white page of death, error reporting\display are off, turn them on `error_reporting(E_ALL); ini_set('display_errors', 1);` — , Aug 24 '14 at 03:42
@Dagon Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 32 bytes) in /var/www/codeigniter/CodeIgniter_2.1.0/system/database/drivers/mysql/mysql_result.php on line 152 .... interesting but no idea what this means — Stephen F, Aug 24 '14 at 03:44
exactly what it says, your using more memory than php is allowed — , Aug 24 '14 at 03:48
ini_set("memory_limit","512M"); did the trick. thanks for the help! — Stephen F, Aug 24 '14 at 03:51
Instead of increasing memory you should by chunking the data with limit clauses. — pguardiario, Aug 24 '14 at 04:18
@Dagon Could you add your answer that OP can accept and close out this question? — zedfoxus, Aug 25 '14 at 02:39

score 0 · Answer 1 · answered Mar 04 '22 at 11:04

I'm summering the answers in comments

Step 1: Look for any errors by displaying the errors

error_reporting(E_ALL);
ini_set('display_errors', 1);

Step 2: if the error is something related to Allowed memory size of xxx bytes exhausted than you can increase it using

ini_set("memory_limit","512M"); 
// you can increase/decrease the memory from 512M to any value

Note: And again this is not recommended in most of the cases only for testing click here for more details

fputcsv fails with queries over 20k records

1 Answers1