In PHP, how do I make appropriate data conversions on items in an array so that they suit MariaDB syntax?

47 Views Asked by At

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!

0

There are 0 best solutions below