exporting 20k or more records from MY SQL to CSV using php [duplicate]

Question

I'm trying to export around 20k records from a table in mysql to csv and as expected it crashes my system, is there any alternative to doing this as to avoid crashes?

here's my current export code:

$filename2 = "csv/leads_".date("M-d-Y",time()).".csv";
            $fp2 = fopen($filename2, 'w') or die("can't open file");

            $sql2 = $sql_getcustomers;
            $res2 = mysql_query($sql2);

            // fetch a row and write the column names out to the file
            $row2 = mysql_fetch_assoc($res2);
            $line = "";
            $comma = "";
            if($row2){
                foreach($row2 as $name => $value) {
                    $line .= $comma . '"' . str_replace('"', '""', $name) . '"';
                    $comma = ",";
                }

                $line .= ",crm_group";
                $line .= "\n";
                fwrite($fp2, $line);

                // remove the result pointer back to the start
                mysql_data_seek($res2, 0);

                // and loop through the actual data
                while($row2 = mysql_fetch_assoc($res2)) {      
                    $line = "";
                    $comma = "";
                    foreach($row2 as $index => $value) {
                        $line .= $comma . '"' . str_replace('"', '""', utf8_decode($value)) . '"';
                        $comma = ",";
                    }

                    //** GET THE CRM GROUPS
                    $sql_get_group = "SELECT a.crm_group_name, b.* FROM tbl_crm_members b JOIN tbl_crm_groups a ON (a.crm_gid = b.crm_groupid) WHERE crm_uid = ".$row2["uid"];
                    $sql_get_groups = mysql_query($sql_get_group);
                    $res_get_groups = "";
                    while($sgg = mysql_fetch_object($sql_get_groups)) $res_get_groups .= $sgg->crm_group_name.";";
                    $line .= ",".trim($res_get_groups, ";");
                    $line .= "\n";
                    fwrite($fp2, $line);    

                }
                fclose($fp2);

Answer 1

Why not let mysql do it?

SELECT id, name, email INTO OUTFILE '/tmp/result.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM users WHERE 1

http://ariejan.net/2008/11/27/export-csv-directly-from-mysql/