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


?>

Answer 1

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.