Memory and File size issue with writing 12,000 records from mysql table to csv file using fputcsv() function

Question

I have a database of 12,000 records which I want to write to csv file I have tried below php code, but after file size becomes 980kb data starts removing and file size starts decreasing thereafter. Data is successfully fetched from mysql table. Issue with csv file write. Please see my code below.

<?php
include './connection.php';

set_time_limit(0);
ini_set('memory_limit', '1024M');
$connection;
$db_link;

    $output_file = "db_data.csv";

    try {

        $csv_file = fopen($output_file,"b");
        $value = array('Name','Address','Phone Number','International Phone Number','Website');
            if(!empty($value)){
                fpassthru($csv_file);
                fputcsv($csv_file, $value,',');
                fclose($csv_file);
            }       
    } catch (Exception $e) {
        echo $message = $e->getMessage();
    }



        $connection = new Connection('1.2.3.8','admin','pass','automtn');
        $db_link = $connection->connect();
        $low = 0;
        $high = 100;
        $query = "SELECT name,formatted_address,formatted_phone_number,international_phone_number,website "
                . " FROM `fetch_data` ORDER BY id ASC LIMIT $low OFFSET $high";

        while($result = mysqli_query($db_link,$query)){
            $data = array();
            while ($row = mysqli_fetch_row($result)) {                
                $data[] = $row;                
            }
            write_to_csv($data);
            unset($data);
            $low+=100;
            $high+=100;
            $query = "SELECT name,formatted_address,formatted_phone_number,international_phone_number,website "
                . " FROM `fetch_data` ORDER BY id ASC LIMIT $low OFFSET $high";

        }

function write_to_csv($results) {

    global $output_file;
    $row = 0;
    try {

        $csv_file = fopen($output_file,"b");

        $fetched_data = $results;
        unset($results);
        foreach ($fetched_data as $value) {
            if(!empty($value)){
                fpassthru($csv_file);
                fputcsv($csv_file, $value, ',');
            }            
        }
        fclose($csv_file);
        unset($fetched_data);
    } catch (Exception $e) {
        echo $message = $e->getMessage();
    }
}


?>

Maybe you open/close too quickly your file. After 1Mo data, it takes longer to open and close file. Try increasing the low / high gap. Or maybe add a sleep after fclose and/or fopen ? You can also try to raise the memory_limit (even if it looks fairly enough...) — Random, May 18 '15 at 14:45

score 1 · Accepted Answer · answered May 18 '15 at 15:14

you are writing to the beginning of the file each time. frankly I'm surprised mode "b" by itself doesn't throw an error; unless the documentation is out of date.

use append mode writing "a" (or "ab")

$csv_file = fopen($output_file,"ab");

Since you are keeping a global filename, why not save yourself some trouble and keep a global file handle instead. you won't have to spend time opening and closing the file every time you write.

score 0 · Answer 2 · answered May 18 '15 at 15:19

Issue was with my low high logic, I have changed it with $limit which will remain constant and $offset increment by 100 everytime, now working fine

<?php
include './connection.php';

set_time_limit(0);
ini_set('memory_limit', '1024M');
$connection;
$db_link;

    $output_file = "db_data.csv";

    try {

        $csv_file = fopen($output_file,"a+");
        $value = array('Name','Address','Phone Number','International Phone Number','Website');
            if(!empty($value)){
                fpassthru($csv_file);
                fputcsv($csv_file, $value,',');
                fclose($csv_file);
            }       
    } catch (Exception $e) {
        echo $message = $e->getMessage();
    }



        $connection = new Connection('1.2.3.8','admin','pass','automtn');
        $db_link = $connection->connect();
        $limit = 100;
        $offset = 0;

        $query = "SELECT name,formatted_address,formatted_phone_number,international_phone_number,website "
                . " FROM `fetch_data` ORDER BY id ASC LIMIT $limit OFFSET $offset ";

        while($result = mysqli_query($db_link,$query)){
            $data = array();
            while ($row = mysqli_fetch_row($result)) {                
                $data[] = $row;                
            }
            write_to_csv($data);
            unset($data);

            $offset +=100;
            $query = "SELECT name,formatted_address,formatted_phone_number,international_phone_number,website "
                . " FROM `fetch_data` ORDER BY id ASC LIMIT $low OFFSET $offset ";

        }

function write_to_csv($results) {

    global $output_file;
    $row = 0;
    try {

        $csv_file = fopen($output_file,"a+");

        $fetched_data = $results;
        unset($results);
        foreach ($fetched_data as $value) {
            if(!empty($value)){
                fpassthru($csv_file);
                fputcsv($csv_file, $value, ',');
            }            
        }
        fclose($csv_file);
        unset($fetched_data);
    } catch (Exception $e) {
        echo $message = $e->getMessage();
    }
}


?>

Memory and File size issue with writing 12,000 records from mysql table to csv file using fputcsv() function

2 Answers2