代码之家  ›  专栏  ›  技术社区  ›  Milosharkey

在PHP中,如何对数组中的项进行适当的数据转换,使其符合MariaDB语法?[副本]

  •  0
  • Milosharkey  · 技术社区  · 1 年前

    我试图通过使用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行对应于一个右括号,所以我对此格外困惑。

    任何帮助或指导都将不胜感激。非常感谢。

    0 回复  |  直到 1 年前