PHP - How to import csv file when mysql server doesnt allow load infile

here's another slick piece for my big-data pals

Most web host providers using shared hosting disable the mysql load infile feature due to security risks with all users basically on the same machine.

So, i came up with this quick little recursive function

• it can import a 10mb file with 30k rows (38 cols/row) in ~60 seconds

still not as fast as ~.5 seconds using load infile - but still WAY faster than doing 30k single "insert into"

So i use https://fatfreeframework.com/ - super small and super fast

you should be able to adapt this to your platform fairly easy.

/*

    @file[csv] - take a csv file(absolute path) - recursive batch insert
    @start[int] - row number to start chunk
    @chunk[int] - how many rows to insert

*/
public function batch( $file , $start=0 , $chunk=1024){
    $count = 0;

    $table = basename( $file , '.csv');

    $rows = []; //empty array for batch insert rows (quoted)

    $fp = @fopen($file , "r");
    while( 
        ($row = fgetcsv($fp)) !== false &&
        count($rows) < $chunk
    ){
        //this first line skips the header row - adjust for your csv
        if(stripos($row[0] , 'Item ID') === false && $count > $start){
        
            $quoted = [];   //empty array for column data
            
            //loop through cols(header) to truncate row (make sure row is the right size)
            //if you do import with crappy csv (extra columns) - it breaks everything
            //so we just get rid of un-needed data
            
            //make sure you have your table field names in an ordered array that matches the csv columns
            foreach($this->csv->fields(false) as $k => $field){   //false = don't load virtual fields
            
                $quoted[] = $this->db->quote($row[$k]); //make sure all entries are quoted
                
            }
            $rows[] = "(" . join("," , $quoted) . ")";  //create string for values ()
        }

        $count++;
    }//end - read $chunk rows
    fclose($fp);

    $row_count = count($rows);

    if( $row_count > 0){
        $query = "insert into `{$table}` values " . join("," , $rows);
        $this->db->exec( $query );
        return $this->batch( $file , $count);   //keep going until we run out of rows
    }

    return; //we only get this far if no more rows to read
}//end function

well that's it for today boys and girls - have fun.