我有一个愚蠢的问题。我正在尝试将csv文件中的批量数据上传到我的数据库表中。它运行良好。但我注意到csv文件中的许多行都包含单引号单词中的数据,如(That’s,It’s etc)。在数据库中的任何位置,单引号查询都会抛出语法错误。我看到很多帖子建议你保留这样的单引号数据,但我有1000多行,所以我不能检查每一行。有什么程序化的方法可以避免这个问题吗?
<?php
include "connection.php"; //Connect to Database
$deleterecords = "TRUNCATE TABLE tablename"; //empty the table of its current records
mysql_query($deleterecords);
//Upload File
if (isset($_POST['submit'])) {
if (is_uploaded_file($_FILES['filename']['tmp_name'])) {
echo "<h1>" . "File ". $_FILES['filename']['name'] ." uploaded successfully." . "</h1>";
echo "<h2>Displaying contents:</h2>";
readfile($_FILES['filename']['tmp_name']);
}
//Import uploaded file to Database
$handle = fopen($_FILES['filename']['tmp_name'], "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$import="";
mysql_query($import) or die(mysql_error());
}
fclose($handle);
print "Import done";
print ""
//view upload form
}else {
print "<h1>Upload new CSV</h1><br />'n";
print "<form enctype='multipart/form-data' action='upload.php' method='post'>";
print "File name to import:<br />'n";
print "<input size='50' type='file' name='filename'><br />'n";
print "<input type='submit' name='submit' value='Upload'></form>";
}
?>
首先,您需要停止使用mysql_*
函数,因为它在php 7.0中被省略了,而是使用mysqli_*
或PDO
您可以使用addslashes()
添加反斜杠来转义单引号和双引号,或者由于要插入数据库,所以需要使用mysql_real_escape_string()
mysql_real_escape_string($import);