importing 20k details in php using csv

Question

I'm trying to insert a csv file with at least 20k lines, and it prompts my browser to kill it self, I guess its taking up a lot of memory and its processing it really slow, I'm also doing this for export and still the same problem it takes too much time that the browser wanna kill it self. Do you have any suggestions on how am I supposed to make it easier and faster?

here are my codes:

Import:

if($_POST) {
        $error = 0;

        $tmpName = $_SESSION["csv_file"];
        $fileExtension = $_SESSION["csv_ext"];

        $fieldset = explode(",", trim($_REQUEST["csv_listfields"], ","));

        unset($_SESSION["csv_file"]);   unset($_SESSION["csv_ext"]);        

        if($tmpName){
            if($fileExtension == 'csv'){
                $fp = fopen($tmpName, 'r');
                $fr = fread($fp, filesize($tmpName));
                $line = explode("\n", $fr);
                $field_pairs = array();
                $csvpos=array();
                $csvpos=$_POST['csv_pos'];
                $getCsvPos=array(); 
                $ifNotempty=0;

                for($i=0;$i<count($csvpos);$i++){
                    if($csvpos[$i]!=-1){
                        $getCsvPos[$ifNotempty] = $csvpos[$i];
                        $ifNotempty++;
                    }
                }
                $fldcolumns         = $line[0];
                $fldcolumns         = array_map("trim_field", explode(",", $fldcolumns));

                $forIndexValue=0; 
                foreach($fieldset as $fld){

                    $f = explode("=", $fld);

                    list($dbcol, $colcsv) = explode("=",$fld);
                    $field_pairs[$dbcol] = $getCsvPos[$forIndexValue] ;

                    $forIndexValue++;
                }
                $csvfile = fopen($tmpName, 'r');
                $ctr = 0;               
                $total_uploaded = 0;

                while (($datax = fgetcsv($csvfile, 1000, ",")) !== FALSE) {
                    $insert_crm = array();
                    $row_hascrm_assigned = false;
                    if($ctr != 0){
                        $ins_tbl = array(); 
                        $has_val = false;
                        foreach($field_pairs as $field => $colkey){ 
                            if( $datax[$colkey] != '' ) $has_val = true;

                            if($field != 'crm_group'){
                                if($field == 'password'){
                                    $ins_tbl[$field] = (strlen($datax[$colkey]) != 64) ? hash("sha256", $datax[$colkey]) : $datax[$colkey];
                                }elseif($field == 'birthdate' || $field == 'dateIN'){

                                    if($field=="dateIN"){
                                        if($datax[$colkey] == ""){ 
                                            $date = date("Y-m-d");      
                                        }else{
                                             $date = $datax[$colkey];
                                        }   
                                        $ins_tbl[$field] = $date;
                                    }
                                }elseif($field == 'email'){
                                    $ins_tbl[$field] = strtolower($datax[$colkey]);
                                }else{
                                    $ins_tbl[$field] = mysql_real_escape_string($datax[$colkey]);
                                }
                                if($field != "dateIN"){
                                    $ins_tbl["dateIN"] = date("Y-m-d");
                                }
                                if($field == "birthdate"){
                                    $ins_tbl[$field] = $datax[$colkey];
                                }

                            }else{  
                                foreach( explode(";", $datax[count($fldcolumns) - 1]) as $cg ){
                                    $cg = ($cg == "")?$datax[$colkey]:$cg;
                                    $cg = htmlentities($cg);
                                    $crm_sql = mysql_query("SELECT crm_gid FROM tbl_crm_groups WHERE crm_group_name = '".trim($cg, "'")."'");

                                    if(mysql_num_rows($crm_sql) < 1){
                                        mysql_query("INSERT INTO tbl_crm_groups (crm_group_name, crm_date_created, custom) VALUES ('".$cg."', '".date('Y/m/d H:i:s')."', 1)") or die("</br>Error Message: ".mysql_error());
                                        $crm_gid =  mysql_insert_id();
                                    }else{
                                        $crm_gid = ($cg != "" && mysql_num_rows($crm_sql) > 0) ? mysql_result($crm_sql, 0) : 1;
                                    }
                                    if(mysql_num_rows(mysql_query("SELECT * FROM tbl_crm_members WHERE crm_groupid = {$crm_gid} AND crm_uid = {$crm_uid}")) < 1){
                                        if(!in_array("INSERT INTO tbl_crm_members(crm_groupid, crm_uid, datejoined) VALUES('{$crm_gid}', '[give_me_uid]', '".date("Y-m-d")."')", $insert_crm))
                                            $insert_crm[] = "INSERT INTO tbl_crm_members(crm_groupid, crm_uid, datejoined) VALUES('{$crm_gid}', '[give_me_uid]', '".date("Y-m-d")."')";
                                    }
                                }

                            }
                        }
                        if($has_val){   
                            if(mysql_query("INSERT INTO tbl_members (".implode(',',array_keys($ins_tbl)).") VALUES (\"".implode('","',$ins_tbl)."\")")){

                                $last_member_inserted = mysql_insert_id();
                                $total_uploaded++;
                                if(count($insert_crm) > 0){
                                    foreach($insert_crm as $ic){
                                        mysql_query( str_replace("[give_me_uid]", $last_member_inserted, $ic) );    
                                    }
                                }else{
                                    mysql_query( "INSERT INTO tbl_crm_members(crm_groupid, crm_uid, datejoined) VALUES('1', ".mysql_insert_id().", '".date("Y-m-d")."')" ); 
                                }
                            }   
                        }                       
                    }
                    $ctr++;
                }
                fclose($fp);
                echo "<div style='color: green; margin: 10px;'>STATUS: ".$total_uploaded." record(s) successfully imported. <br/>This page will reload in a couple of seconds.</div>";              
            }else{
                exit("Not a valid csv file uploaded.");
            }
            unlink($tmpName);

            echo "<script type='text/javascript'>setTimeout(function(){parent.location.reload(true);}, 2000);</script>";
        }else{
            exit("File uploaded improperly.");
        }   
    }

Export:

if(IS_AJAX){
            $output = array();

            $sql_getcustomers = $_POST['val'];

            /* CREATE CSV FILE FOR DOWNLOAD */  
            $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);

                $output['data'] = 1;
                $output['file'] = $filename2;
            }else{
                $output['data'] = 0;
            }
        }else{
            $output['data'] = 0;
        }

Answer 1

Use the MySQL import function LOAD DATA and let the database take care of the importing. It's a lot faster!

LOAD DATA LOCAL INFILE '/some/file/path' 
INTO TABLE table_name 
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n'
IGNORE 1 LINES