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

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

score 4 · Accepted Answer · edited Oct 04 '13 at 17:58

4

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/

edited Oct 04 '13 at 17:58

Sammitch

25,490
6
42
70

answered Oct 04 '13 at 17:54

Anton Bessonov

6,992
3
29
33

would you mind summarizing that for me, the syntax and how it works. thanks – magicianiam Oct 04 '13 at 17:56
It export selected data to a file named result.csv in /tmp/ folder (*nix). Block between FIELDS and TERMINATED BY is configuration to format your csv file. You can read more on http://dev.mysql.com/doc/refman/5.1/en/select-into.html about it. I think it doen't make sense to rewrite it here. – Anton Bessonov Oct 04 '13 at 18:03
can i make it so to automatic download the file? – magicianiam Oct 04 '13 at 18:28
No, it stores file on machine where mysql run. You can serve it with php or as static file by webserver or throught ftp/scp. Look at http://teddy.fr/2007/11/28/how-serve-big-files-through-php/ for php. – Anton Bessonov Oct 04 '13 at 18:38
tried though i get access denied in mysql – magicianiam Oct 04 '13 at 19:31
Do you have write access to location where you will store file? – Anton Bessonov Oct 05 '13 at 09:48
got it to work though, I was wondering if I can get the headers from mysql to be included at export? – magicianiam Oct 06 '13 at 07:56
Look at comment 'Posted by Jorge Lumbreras on November 18 2011 6:42pm'. It's not elegant and not DRY way, but schould work in mysql. – Anton Bessonov Oct 06 '13 at 08:24
where did he post that? thank you – magicianiam Oct 06 '13 at 08:34
http://dev.mysql.com/doc/refman/5.1/en/select-into.html – Anton Bessonov Oct 07 '13 at 22:30

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

1 Answers1