我正在尝试创建一个表单，该表单上载CSV文件，然后将数据插入MYSQL数据库。对于我的代码，我没有收到任何错误消息，只是没有插入。这是我的代码：
这是表单代码：
<!DOCTYPE html>
<html>
<head>
<title>CSV Upload</title>
</head>
<body>
<form method="POST" enctype="multipart/form-data" action="import.php">
<div align="center">
<p>Select CSV file: <input type="file" name="file" /></p>
<p><input type="submit" name="csv_upload_btn" value="Upload" /></p>
</div>
</form>
</body>
</html>
//Process form
if(isset($_POST["csv_upload_btn"])){
if($_FILES['file']['name']){
$filename = explode("",$_FILES['file']['name']);
if($filename[1] == "csv"){
$handle = fopen($_FILES['file']['tmp_name'], "r");
while($data = fgetcsv($handle)){
$item1 = mysqli_real_escape_string($connection, $data[0]);
$item2 = mysqli_real_escape_string($connection, $data[1]);
$item3 = mysqli_real_escape_string($connection, $data[2]);
$item4 = mysqli_real_escape_string($connection, $data[3]);
$item5 = mysqli_real_escape_string($connection, $data[4]);
$query = " INSERT INTO data(softwareID,districtID,statusID,date_approved,date_expired) VALUES('$item1', '$item2', '$item3', '$item4', '$item5') ";
$run_query = mysqli_query($connection, $query);
}
fclose($handle);
if($run_query == true){
echo "File Import Successful";
}else{
echo "File Import Failed";
}
}
}
}
//Close Connection
mysqli_close($connection);
?>
您当前的代码容易受到SQL注入的攻击，我建议使用准备好的语句或参数化查询，这可能也会解决您的问题。我将向您展示一个关于如何连接数据库(使用PDO(的示例：
# You can also set this up in a function, but this is how I use it as it works best for me.
# Also best if you keep this class (or function if you choose to change it) out of the `public_html` folder and just include/require it.
class DB extends PDO{
public $connect;
public function __construct(string $db_name){
try{
# Declare your mysql credentials
$cred = [
"db_user" => "localhost",
"db_user" => "root",
"db_pass" => "xxx"
];
$this->connect = new PDO("mysql:host=".$cred['db_host'].";dbname=".$db_name, $cred['db_user'], $cred['db_pass']);
$this->connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}catch(PDOException $e){
# You can include the $e variable from above in the echo below to show the error, but I chose not to
# just incase someone is trying to attack your website. That error can give them a lot of information
# about your SQL query, which can be very useful to an attacker; giving them an idea on how to formulate
# an injection (if possible).
echo("Error");
}
}
}
# Start a connection to the declared database name
$db = new DB("database_name");
# Prepare the query but refrain from inputting variables directly into it. Instead put a :placeholder in its spot like so:
$queryPrep = $db->connect->prepare("INSERT INTO `data` (softwareID, districtID, statusID, date_approved, date_expired) VALUES (:softwareID, :districtID, :statusID, :date_approved, :date_expired)");
# You then bind your value(s) into your query like so (make sure to declare what datatype your variable is in the 3rd parameter):
$queryPrep->bindValue(':softwareID', $softwareID, PDO::PARAM_STR);
$queryPrep->bindValue(':districtID', $districtID, PDO::PARAM_STR);
$queryPrep->bindValue(':statusID', $statusID, PDO::PARAM_STR);
$queryPrep->bindValue(':date_approved', $date_approved, PDO::PARAM_INT);
$queryPrep->bindValue(':date_expired', $date_expired, PDO::PARAM_INT);
# Full list of PDO::PARAM_ Predefined Constants
# https://www.php.net/manual/en/pdo.constants.php
# Now you can finally execute your query
$queryPrep->execute();
# Check to see if any rows have been added to the database from the last SQL statement
if($queryPrep->rowCount() > 0) echo "true - Row Added";
else echo "false - Row Not Added";
我还创建了一个函数，用于将CSV文件/字符串解析为更容易使用的数组(不过总是假设第一行是列名(：
function csvParse($input, $callback = false){
$results = [];
$raw_array = (is_file($input)) ? array_map('str_getcsv', file($input)) : array_map('str_getcsv', explode("n", $input));
$array = array_splice($raw_array, 1, count($raw_array));
foreach($raw_array[0] as $c) $columns[] = $c;
foreach($array as $key0 => $val0) foreach($val0 as $key1 => $val1) $results[$key0][$columns[$key1]] = $val1;
if(is_callable($callback) && !empty($results)) call_user_func_array($callback, array($results));
elseif(!empty($results)) return $results;
else throw new Exception("Results Empty: Can not read the string or open file.");
}
# Can also be file location
$input = "animal,name,agen
goat,crimin4l,24n
deer,henry,11n
bear,teddy,15";
csvParse($input, function ($arr){
print_r($arr);
});
输出：
Array
(
[0] => Array
(
[animal] => goat
[name] => crimin4l
[age] => 24
)
[1] => Array
(
[animal] => deer
[name] => henry
[age] => 11
)
[2] => Array
(
[animal] => bear
[name] => teddy
[age] => 15
)
)
你可以把它们放在一起，像这样成功地完成你的任务：
$db = new DB("database_name");
if(isset($_POST["csv_upload_btn"]) && !empty($_FILES['file'])){
$file['base'] = basename($_FILES['file']['name']);
$file['path'] = $_FILES['file']['tmp_name'];
$file['mime'] = strtolower(pathinfo($file['base'], PATHINFO_EXTENSION));
if($file['mime'] === "csv" || $file['mime'] === "txt"){
csvParse($file['path'], function ($arr){
# Start the $queryPrep here;
# If for each CSV row you want to add a MySQL row then
# you will need a foreach loop to iterate through each
# of the array(s).
});
}else echo("Error: File must be .CSV or .TXT");
}