我想將 CSV 文件上傳到我的 SQL 數據庫中，但收到“未定義的偏移量”錯誤。（偏移量：1-20）。不知道如何解決該錯誤。我希望你們能為我指出正確的方向。提前致謝。<?php// Load the database configuration fileinclude_once 'dbConfig.php';if(isset($_POST['importSubmit'])){? ??? ? // Allowed mime types? ? $csvMimes = array('text/x-comma-separated-values', 'text/comma-separated-values', 'application/octet-stream', 'application/vnd.ms-excel', 'application/x-csv', 'text/x-csv', 'text/csv', 'application/csv', 'application/excel', 'application/vnd.msexcel', 'text/plain');? ??? ? // Validate whether selected file is a CSV file? ? if(!empty($_FILES['file']['name']) && in_array($_FILES['file']['type'], $csvMimes)){? ? ? ??? ? ? ? // If the file is uploaded? ? ? ? if(is_uploaded_file($_FILES['file']['tmp_name'])){? ? ? ? ? ??? ? ? ? ? ? // Open uploaded CSV file with read-only mode? ? ? ? ? ? $csvFile = fopen($_FILES['file']['tmp_name'], 'r');? ? ? ? ? ??? ? ? ? ? ? // Skip the first line? ? ? ? ? ? fgetcsv($csvFile);? ? ? ? ? ??? ? ? ? ? ? // Parse data from CSV file line by line? ? ? ? ? ? while(($line = fgetcsv($csvFile)) !== FALSE){? ? ? ? ? ? ? ? // Get row data? ? ? ? ? ? ? ? $postcode? ?= $line[0];? ? ? ? ? ? ? ? $week1? = $line[1];? ? ? ? ? ? ? ? $week2? = $line[2];? ? ? ? ? ? ? ? $week3? = $line[3];? ? ? ? ? ? ? ? $week4? = $line[4];? ? ? ? ? ? ? ? $week5? = $line[5];? ? ? ? ? ? ? ? $week6? = $line[6];? ? ? ? ? ? ? ? $week7? = $line[7];? ? ? ? ? ? ? ? $week8? = $line[8];? ? ? ? ? ? ? ? $week9? = $line[19];? ? ? ? ? ? ? ? $week10? = $line[10];? ? ? ? ? ? ? ? $week11? = $line[11];? ? ? ? ? ? ? ? $week12? = $line[12];? ? ? ? ? ? ? ? $week13? = $line[13];? ? ? ? ? ? ? ? $week14? = $line[14];? ? ? ? ? ? ? ? $week15? = $line[15];? ? ? ? ? ? ? ? $week16? = $line[16];? ? ? ? ? ? ? ? $week17? = $line[17];? ? ? ? ? ? ? ? $week18? = $line[18];? ? ? ? ? ? ? ? $week19? = $line[19];? ? ? ? ? ? ? ? $week20? = $line[20];? ? ? ? ? ? }? ? ? ? }錯誤信息：
2 回答
慕碼人2483693
TA貢獻1860條經驗 獲得超9個贊
聽起來輸入文件有一些不太合適的行。
您可能需要在插入之前進行一些檢查，例如
$line_no = 1;
// Parse data from CSV file line by line
while(($line = fgetcsv($csvFile)) !== FALSE){
? ? $line_no++;
? ? if ( count($line) != 21 ) {??
? ? ? ? echo "$line_no needs to be looked at";
? ? ? ? continue;? ? ? ?// go to next iteration of the loop
? ? }
? ? // Get row data
? ? $postcode? ?= $line[0];
? ? $week1? = $line[1];
? ? $week2? = $line[2];
? ? $week3? = $line[3];
? ? $week4? = $line[4];
? ? $week5? = $line[5];
? ? $week6? = $line[6];
? ? $week7? = $line[7];
? ? $week8? = $line[8];
? ? $week9? = $line[19];
? ? $week10? = $line[10];
? ? $week11? = $line[11];
? ? $week12? = $line[12];
? ? $week13? = $line[13];
? ? $week14? = $line[14];
? ? $week15? = $line[15];
? ? $week16? = $line[16];
? ? $week17? = $line[17];
? ? $week18? = $line[18];
? ? $week19? = $line[19];
? ? $week20? = $line[20];
? ??
? ? $db->query("INSERT INTO nlbelevering?
? ? ? ? ? ? (Postcode, Week1, Week2, Week3, Week4, Week5, Week6,?
? ? ? ? ? ? Week7, Week8, Week9, Week10, Week11, Week12, Week13,?
? ? ? ? ? ? Week14, Week15, Week16, Week17, Week18, Week19,?
? ? ? ? ? ? Week20)?
? ? VALUES ('".$postcode."', '".$week1."', '".$week2."',?
? ? ? ? ? ? '".$week3."', '".$week4."', '".$week5."',?
? ? ? ? ? ? '".$week6."', '".$week7."', '".$week8."',?
? ? ? ? ? ? '".$week9."', '".$week10."', '".$week11."',?
? ? ? ? ? ? '".$week12."', '".$week13."', '".$week14."',?
? ? ? ? ? ? '".$week15."', '".$week16."', '".$week17."',?
? ? ? ? ? ? '".$week18."', '".$week19."', '".$week20."'");
? ? }
}
當然，您可以考慮在文件預解析中檢查所有行，如果任何行少于 21 列，則拒絕整個文件。這一切都取決于數據，以及數據庫中缺少幾行是否有意義。
重要提示?您的腳本容易受到SQL 注入攻擊。即使您轉義輸入，也不安全！您應該考慮在或API 中?使用準備好的參數化語句，而不是連接值
MYSQLI_PDO
準備好的語句不僅可以避免 SQL 注入問題，還可以使用準備好的語句加快處理速度，因為您只需準備（發送到數據庫、編譯和優化）查詢一次，而不是每行輸入準備一次。
// prepare once outside the loop
$stmt = $db->prepare(
? ? ? ? ? ? "INSERT INTO nlbelevering?
? ? ? ? ? ? ? ? ? ? (Postcode, Week1, Week2, Week3, Week4, Week5, Week6,?
? ? ? ? ? ? ? ? ? ? Week7, Week8, Week9, Week10, Week11, Week12, Week13,?
? ? ? ? ? ? ? ? ? ? Week14, Week15, Week16, Week17, Week18, Week19, Week20)?
? ? ? ? ? ? VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
? ? ? ? ? ? );
while(($line = fgetcsv($csvFile)) !== FALSE){
? ? // bind new values each time round the loop and execute the query
? ? $stmt->bind_param('sssssssssssssssssssss',
? ? ? ? ? ? ? ? ? ? $line[0], $line[1], $line[2], $line[3],
? ? ? ? ? ? ? ? ? ? $line[4], $line[5], $line[6], $line[7],
? ? ? ? ? ? ? ? ? ? $line[8], $line[19], $line[10], $line[11],
? ? ? ? ? ? ? ? ? ? $line[12], $line[13], $line[14], $line[15],
? ? ? ? ? ? ? ? ? ? $line[16], $line[17], $line[18], $line[19],line[20]
? ? ? ? ? ? ? ? );
? ? $stmt->execute();
}
看到 CSV 文件后更新。
CSV 代表“逗號分隔值”，因此fgetcsv()假設文件如下
aaa,bbb,ccc,ddd,.......
如果您有一個“分號分隔值”文件，您必須告訴您fgetcsv()期望有一個不同的選擇器，因此從看到您的文件更改您的代碼以使用
// parmeter 2 need to be there to use param 3,?
// pick a number larger than any of the line, lenght or Newline will?
// denote a line.
// parameter 3 says to expect `;` as the seperator instead of a `,`
fgetcsv($csvFile, 1000, ";");
胡子哥哥
TA貢獻1825條經驗 獲得超6個贊
當通過 php 中的索引訪問數組元素時，您應該始終檢查該索引是否確實存在。否則你會收到Undefined offset通知。你應該對每個元素都這樣做：
$postcode = isset($line[0]) ? strval($line[0]) : '';
$week1 = isset($line[1]) ? strval($line[1]) : '';
...
這樣，如果鍵不存在，您還可以定義默認值（在我的示例中它是空字符串“”）。
- 2 回答
- 0 關注
- 156 瀏覽
添加回答
舉報
0/150
提交
取消