PHP导入CSV未定义的偏移量

Question

我想将 CSV 文件上传到我的 SQL 数据库中，但收到&ldquo;未定义的偏移量&rdquo;错误。（偏移量：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];            }        }错误信息：

Answer 1

听起来输入文件有一些不太合适的行。

您可能需要在插入之前进行一些检查，例如

$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, ";");

Answer 2

当通过 php 中的索引访问数组元素时，您应该始终检查该索引是否确实存在。否则你会收到Undefined offset通知。你应该对每个元素都这样做：

$postcode = isset($line[0]) ? strval($line[0]) : '';

$week1 = isset($line[1]) ? strval($line[1]) : '';

...

这样，如果键不存在，您还可以定义默认值（在我的示例中它是空字符串“”）。