I am trying to populate a MySQL database by reading from a CSV file using PHP and parsing values from a row into an SQL query. I keep getting errors saying that there's an error in my SQL syntax, and I think it is because all of the items in the CSV file are stored as strings only, whereas I am trying to insert them as multiple different data types.
Here is my code I am using to convert the data types
// 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 . "'";
}
}
}
And here it is in the context of the program
$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();
}
I keep getting this error or something similar to this error:
Fatal error: Uncaught mysqli_sql_exception: 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 '32ovJ67AxXydzmuYbuflOp']', '1', '0.781', '0.697', '4', '-10.337', '0', '0.054...' at line 1 in C:\xampp\htdocs\databaseLoader.php:108 Stack trace: #0 C:\xampp\htdocs\databaseLoader.php(108): mysqli->query('INSERT INTO tra...') #1 {main} thrown in C:\xampp\htdocs\databaseLoader.php on line 108
Line 108 corresponds to a close bracket so I am extra confused by this.
Any help or direction will be much appreciated. Thank you!