Good Morning,
I am taking a large CSV and inserting the records into SQL. I have it successfully entering over 3500 records now with a remaining 31 still causing errors.
On one of the records I am getting this error: Error description: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘11’, ‘7’11 1/2"X 11’10 1/2" RF ACCESS DWG 201083||’, ‘2’, ‘F’, ‘2020-03-24’,’ at line 2
Here is the entire SQL statement: INSERT into production_data (job_number, enterprise, part_number, description, qty, line_item, as400_ship_date, date_showed_on_report, shipping_method, notes, date_shown_complete, actual_ship_date, qty_shipped) values (‘23558241’, ‘TARGET’, ‘7’11’, ‘7’11 1/2"X 11’10 1/2" RF ACCESS DWG 201083||’, ‘2’, ‘F’, ‘2020-03-24’, ‘2020-02-18’, ‘’, ‘�2/18-Shows revised on production report. This line changed.’, ‘0000-00-00’, ‘0000-00-00’, ‘0’)
I have escaped the strings, etc…upon looking at this statement I can’t figure out where the issue is…Here is the code I have which is processing this…
//loop through csv file
while(($column = fgetcsv($file, 10000, ",")) !== FALSE) {
//setup col vars for easier editing
$job_num = str_replace("-", "", $column[6]);
$enterprise = $column[1];
$part_num = $column[2];
$desc = $conn -> real_escape_string($column[3]);
$qty = $column[4];
$line_item = $column[5];
$as400_ship_date = (empty($column[7]) ? '0000-00-00' : date("Y-m-d", strtotime($column[7])));
$date_showed_on_report = (empty($column[8]) ? '0000-00-00' : date("Y-m-d", strtotime($column[8])));
$shipping_method = $column[10];
$notes = $conn -> real_escape_string($column[11]);
$date_shown_complete = (empty($column[12]) ? '0000-00-00' : date("Y-m-d", strtotime($column[12])));
$actual_ship_date = (empty($column[14]) ? '0000-00-00' : date("Y-m-d", strtotime($column[14])));
$qty_shipped = (empty($column[15]) ? 0 : $column[15]);
//construct the sql statement
$sqlInsert = "INSERT into production_data (job_number, enterprise, part_number, description, qty, line_item, as400_ship_date, date_showed_on_report, shipping_method, notes, date_shown_complete, actual_ship_date, qty_shipped)
values ('" . $job_num . "', '" .
$enterprise . "', '" .
$part_num . "', '" .
$desc . "', '" .
$qty . "', '" .
$line_item . "', '" .
$as400_ship_date . "', '" .
$date_showed_on_report . "', '" .
$shipping_method . "', '" .
$notes . "', '" .
$date_shown_complete . "', '" .
$actual_ship_date . "', '" .
$qty_shipped . "')";
//perform sql transaction
if($conn -> query($sqlInsert)) {
$cntSuccess++;
//sql transaction failed
} else {
echo "FAILURE: Error description: " . $conn -> error . "<br><br>SQL: " . $sqlInsert . "<br><br>";
$cntFailure++;
}
}