我的代碼是
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "onlinepaydb";
$studentTable = "studentregtable";
if ($_SERVER["REQUEST_METHOD"] === "POST")
{
$uploadedFile = $_FILES["myfile"]["name"];
try
{
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password, array(PDO::MYSQL_ATTR_LOCAL_INFILE => true,));
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "LOAD DATA LOCAL INFILE " . $uploadedFile . " INTO TABLE " . $studentTable . " FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES";
$conn->exec($sql);
echo "table studentTable updated successfully";
}
catch (PDOException $e)
{
echo $sql . $e->getMessage();
}
}
?>
<!doctype html>
<html>
<head>
<meta charset = "utf-8">
<meta name="viewport" content="width = device-width, initial-scale = 1">
</head>
<body>
<form method = "post" action = "<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" enctype = "multipart/form-data">
<label for = "selectfile">Select File:</label>
<input type = "file" name = "myfile" /><br>
<input type = "submit" name = "save" value = "Save" class = "save" />
</form>
</body>
</html>
錯誤是：
LOAD DATA LOCAL INFILE student.csv INTO TABLE studentregtable FIELDS TERMINATED BY ',' LINES >>TERMINATED BY ' ' IGNORE 1 LINESSQLSTATE[42000]: Syntax error or access violation: 1064 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 'student.csv INTO TABLE studentregtable FIELDS TERMINATED BY ',' ...' at line 1
I have the above code. The csv file is on local computer. database table is on the server. I expect it to transfer data from the CSV table to mysql table.
uj5u.com熱心網友回復：
通過 LOAD DATA 匯入 CSV 的語法是（如果 csv 被命名為 SOURCE.CSV 并且表名是 TABLE_NAME 并且您想忽略作為列名的第一行）：
將本地資料檔案'SOURCE.CSV'加載到表TABLE_NAME欄位中，以'\n'終止的行，忽略1行";
你可以參考這里的官方檔案： https ://dev.mysql.com/doc/refman/8.0/en/load-data.html
(1) 所以，請參考檔案名（用單引號）
(2) 請注意上傳的檔案會被$_FILES["myfile"]["tmp_name"]臨時保存，為了讓 LOAD DATA 訪問上傳的檔案，請將上傳的檔案移動到你的路徑（比如 PHP 腳本的相同路徑），如下所示：
move_uploaded_file($_FILES["myfile"]["tmp_name"], "./". $uploadedFile);
(3) 相關路徑必須是可寫的，上述步驟 2 才能成功。
因此，假設您要將檔案上傳到腳本的同一目錄，請確保該目錄是可寫的，然后使用以下代碼：
<?php
$servername = "localhost";
$username = "dbuser";
$password = "xxxxxxxxx";
$dbname = "onlinepaydb";
$studentTable = "studentregtable";
if ($_SERVER["REQUEST_METHOD"] === "POST")
{
$uploadedFile = $_FILES["myfile"]["name"];
move_uploaded_file($_FILES["myfile"]["tmp_name"], "./". $uploadedFile);
try
{
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password, array(PDO::MYSQL_ATTR_LOCAL_INFILE => true,));
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "LOAD DATA LOCAL INFILE '" . $uploadedFile . "' INTO TABLE " . $studentTable . " FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES";
$conn->exec($sql);
echo "table studentTable updated successfully";
}
catch (PDOException $e)
{
echo $sql . $e->getMessage();
}
}
?>
<!doctype html>
<html>
<head>
<meta charset = "utf-8">
<meta name="viewport" content="width = device-width, initial-scale = 1">
</head>
<body>
<form method = "post" action = "<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" enctype = "multipart/form-data">
<label for = "selectfile">Select File:</label>
<input type = "file" name = "myfile" /><br>
<input type = "submit" name = "save" value = "Save" class = "save" />
</form>
</body>
</html>
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/447382.html