Re: single quote in data breaks file load
Yep, except that we don't want to write a replacement function ourselves, we want to use mysql_real_escape_string (from the mysql extension) because that will take care of any other nasty characters messing up your insert as well.
Your snippet now reads:
while ( ( $data = fgetcsv($Handle, 384) ) !== FALSE) {
list($AccCode, $Src, $Dest, $DestContext, $CLID, $Channel, $DstChannel,
$LastApp, $LastData, $Start, $Answer, $Stop, $Duration,
$BillSec, $Outcome, $AmaFlags) = $data;
...
}
well, you just acquired data from the csv file in the array $data using your call to fgetcsv(), so now we want to ensure that all these values are escaped properly.
You could set up a foreach loop over your $data array, but the builtin array_walk function willl do it for us:
while ( ( $data = fgetcsv($Handle, 384) ) !== FALSE) {
array_walk(&$data, 'name of our escape function');
list(...) = $data;
...
}
Now, array_walk accepts an array and the name of a function that will be applied to each array element. When this function is applied, array_walk will pass both the value and the key of the array element (in that order). Unfortunately, mysql_real_escape_string accepts only one argument. So we should not write:
array_walk(&$data, 'mysql_real_escape_string');
So, to solve this in a clean matter, we wrap mysql_real_escape_string like so:
function my_mysql_real_escape_string($val,$key){
return mysql_real_escape_string($val);
}
this function is of the right signature for array_walk. Of course, the $key argument is never used. We don't need to. So, make sure this function is in scop ewith your load bit, which would now read:
while ( ( $data = fgetcsv($Handle, 384) ) !== FALSE) {
array_walk(&$data, 'my_mysql_real_escape_string');
list(...) = $data;
...
}
Hope its clear now...
btw, If you have the chance of unsing the LOAD DATA INFILE syntax instead (as opposed to handling it yourself in php), I would certainly try that. It's bound to be much faster than this. See the mysql reference or details.