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
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.