我试图通过使用PHP读取CSV文件并将行中的值解析为SQL查询来填充MySQL数据库。我不断收到错误,说我的SQL语法中有错误,我认为这是因为CSV文件中的所有项目都只存储为字符串,而我试图将它们作为多种不同的数据类型插入。
这是我用来转换数据类型的代码
// Iterate over each value in $currentRow
foreach ($currentRow as $key => &$value) {
// Check if the data type is defined in $dataType
if (isset($columnDataTypes[$key])) {
// Get the desired data type
$Type = $dataType[$key];
// Perform the necessary conversion based on data type
if ($dataType === 'boolean') {
if ($currentRow[$key] == TRUE) {
$value = 1;
} else {
$value = 0;
}
} elseif ($dataType === 'integer') {
$value = (int) $value;
} elseif ($dataType === 'float') {
$value = (float) $value;
} else {
// Default case for strings
$value = "'" . $value . "'";
}
}
}
这是在项目的背景下
$dataType = ["string", "string", "string", "string", "boolean", "float", "float", "integer", "float", "integer", "float", "float", "float", "float", "float", "float", "integer", "integer", "integer"];
// Check that the file can be opened for reading
if (($handle = fopen("testTrackFeatures.csv", "r")) !== FALSE) {
// Loop through every row in the CSV file
// The fgetcsv operation will return FALSE if end end of file is reached
// The fgetcsv operation will also return FALSE if there is an error in reading the file
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
// Increments the row count
$row++;
// Creates an array to store the contents of one row
$currentRow = [];
// Loop through each column in the current row
for ($c=0; $c < $num; $c++) {
// For each data item stored in the corresponding column and row, append it to the array $currentRow
array_push($currentRow, $data[$c]);
}
print_r($currentRow);
// Iterate over each value in $currentRow
foreach ($currentRow as $key => &$value) {
// Check if the data type is defined in $dataType
if (isset($columnDataTypes[$key])) {
// Get the desired data type
$Type = $dataType[$key];
// Perform the necessary conversion based on data type
if ($dataType === 'boolean') {
if ($currentRow[$key] == TRUE) {
$value = 1;
} else {
$value = 0;
}
} elseif ($dataType === 'integer') {
$value = (int) $value;
} elseif ($dataType === 'float') {
$value = (float) $value;
} else {
// Default case for strings
$value = "'" . $value . "'";
}
}
}
// Creates an SQL function for inserting data into the table
// Uses the implode function to concatenate an entire array into a single string
// This means we do not have to store all of our data in seperate variables
$sql = "INSERT INTO trackfeatures(" . implode(", ", $syntaxHeaders) . ") VALUES ('" . implode("', '", $currentRow) . "')";
// Execute the SQL statement
if ($conn->query($sql) === TRUE) {
echo "Data inserted successfully for row $row" . "<br />\n";
} else {
echo "Error inserting data for row $row: " . $conn->error . "<br />\n";
}
}
//Closes the CSV file
fclose($handle);
// Close the MySQL connection
$conn->close();
}
我不断收到这个错误或类似的错误:
致命错误:未捕获的mysqli_sql_exception:您的
SQL语法;查看与MariaDB服务器对应的手册
在“32ovJ67AxXydzmuYbuflOp”附近使用正确语法的版本,
第1行处的“1”、“0.781”、“0.697”、“4”、“-10.337”、“0”、“0.054…”
C: \xampp\htdocs\databaseLoader.php:108堆栈跟踪:#0
C: \xampp\htdocs\databaseLoader.php(108):mysqli->查询('INSERT INTO
tra…”)#在C:\xampp\htdocs\databaseLoader.php中抛出1个{main}
线路108
第108行对应于一个右括号,所以我对此格外困惑。
任何帮助或指导都将不胜感激。非常感谢。