PHP导入CSV未定义的偏移量

Question

我想将 CSV 文件上传到我的 SQL 数据库中，但收到“未定义的偏移量”错误。（偏移量：1-20）。不知道如何解决该错误。我希望你们能为我指出正确的方向。提前致谢。

<?php

// Load the database configuration file

include_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 linewhile(($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]) : '';...这样，如果键不存在，您还可以定义默认值（在我的示例中它是空字符串“”）。