my code is
<?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>
error is:
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.
Advertisement
Answer
The syntax of CSV import by LOAD DATA is (if csv is named SOURCE.CSV and table name is TABLE_NAME and you want to ignore the 1st line which are the column names):
LOAD DATA LOCAL INFILE ‘SOURCE.CSV’ INTO TABLE TABLE_NAME FIELDS TERMINATED BY ‘,’LINES TERMINATED BY ‘n’ IGNORE 1 LINES”;
You may refer to the official documentation here: https://dev.mysql.com/doc/refman/8.0/en/load-data.html
(1) So, please quote the filename (by single quotation marks)
(2) Please note that the uploaded file will be saved as $_FILES["myfile"]["tmp_name"] temporarily, in order for LOAD DATA to access the uploaded file, please move the uploaded file to your path (say of the same path of the PHP script) like this:
move_uploaded_file($_FILES["myfile"]["tmp_name"], "./". $uploadedFile);
(3) the path concerned must be writable for step 2 above to succeed.
Hence, assuming that you are going to upload the file to the same directory of the script, make sure that the directory is writable, then use the following code:
<?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>