php - 如何将Excel中的空白值插入具有唯一键的列并将其输入为NULL？

Question

我有一个库存程序，它接受 Excel 文件并将其转换为数据库，并且我有一个名为 hostname 的列名，它位于唯一键中。问题出在 Excel 文件中，如果有空白值，它将被插入到主机名中，但如果有另一个空白值，数据库将认为它不是唯一的。我搜索了为什么它不起作用显然只能一次又一次地插入 NULL 因为空白和 NULL 之间存在差异我希望将 excel 中的空白值转换为 NULL，以便我可以正确插入它进入数据库主机名。

$allowedFileType = ['application/vnd.ms- 
excel','text/xls','text/xlsx','application/vnd.openxmlformats- 
officedocument.spreadsheetml.sheet'];

if(in_array($_FILES["file"]["type"],$allowedFileType)){

    $targetPath = 'uploads/'.$_FILES['file']['name'];
    move_uploaded_file($_FILES['file']['tmp_name'], $targetPath);

    $Reader = new SpreadsheetReader($targetPath);

    $sheetCount = count($Reader->sheets());
    for($i=0;$i<$sheetCount;$i++)
    {

        $Reader->ChangeSheet($i);

        foreach ($Reader as $Row)
        {

            $cubicle = "";
            if(isset($Row[0])) {
                $cubicle = mysqli_real_escape_string($con,$Row[0]);
            }
            $hostname = "";
            if(isset($Row[1])) {
                $hostname = mysqli_real_escape_string($con,$Row[1]);
            }
            $groups = "";
            if(isset($Row[2])) {
                $groups = mysqli_real_escape_string($con,$Row[2]);
            }
            $batchfile = "";
            if(isset($Row[3])) {
                $batchfile = mysqli_real_escape_string($con,$Row[3]);
            }
            $macaddress = "";
            if(isset($Row[4])) {
                $macaddress = mysqli_real_escape_string($con,$Row[4]);
            }
            $ipaddress = "";
            if(isset($Row[5])) {
                $ipaddress = mysqli_real_escape_string($con,$Row[5]);
            }
            $operatingsystem = "";
            if(isset($Row[6])) {
                $operatingsystem = mysqli_real_escape_string($con,$Row[6]);
            }
            $build = "";
            if(isset($Row[7])) {
                $build = mysqli_real_escape_string($con,$Row[7]);
            }
            $manufacturer = "";
            if(isset($Row[8])) {
                $manufacturer = mysqli_real_escape_string($con,$Row[8]);
            }
            $physicalmemory = "";
            if(isset($Row[9])) {
                $physicalmemory = mysqli_real_escape_string($con,$Row[9]);
            }
            $model = "";
            if(isset($Row[10])) {
                $model = mysqli_real_escape_string($con,$Row[10]);
            }
            $serialnumber = "";
            if(isset($Row[11])) {
                $serialnumber = mysqli_real_escape_string($con,$Row[11]);
            }
            $assetnumber = "";
            if(isset($Row[12])) {
                $assetnumber = mysqli_real_escape_string($con,$Row[12]);
            }
            $ponumber = "";
            if(isset($Row[13])) {
                $ponumber = mysqli_real_escape_string($con,$Row[13]);
            }
            $deliverydate = "";
            if(isset($Row[14])) {
                $deliverydate = mysqli_real_escape_string($con,$Row[14]);
            }
            $division = "";
            if(isset($Row[15])) {
                $division = mysqli_real_escape_string($con,$Row[15]);
            }
            $warranty = "";
            if(isset($Row[16])) {
                $warranty = mysqli_real_escape_string($con,$Row[16]);
            }
            $lifecycle = "";
            if(isset($Row[17])) {
                $lifecycle = mysqli_real_escape_string($con,$Row[17]);
            }
            if (!empty($cubicle) || !empty($hostname) || !empty($groups) || !empty($batchfile) || !empty($macaddress) || !empty($ipaddress) || !empty($operatingsystem) || !empty($build) || !empty($manufacturer) || !empty($physicalmemory) || !empty($model) || !empty($serialnumber) || !empty($assetnumber) || !empty($ponumber) || !empty($deliverydate) || !empty($division) || !empty($warranty) || !empty($lifecycle)) {
                $query = "insert into computer(cubicle, hostname, groups, batchfile, macaddress, ipaddress, operatingsystem, build, manufacturer, physicalmemory, model, serialnumber, assetnumber, ponumber, deliverydate, division, warranty, lifecycle) values('".$cubicle."','".$hostname."', '".$groups."', '".$batchfile."', '".$macaddress."', '".$ipaddress."', '".$operatingsystem."', '".$build."', '".$manufacturer."', '".$physicalmemory."', '".$model."', '".$serialnumber."', '".$assetnumber."', '".$ponumber."', '".$deliverydate."', '".$division."', '".$warranty."', '".$lifecycle."')";

                $result = mysqli_query($con, $query);

Answer 1

转义+引用字符串(如果可用)。否则将其设置为字符串 "NULL"

$item = isset($Row[123])
      ? '"' . mysqli_real_escape_string($con,$Row[123]) . '"' 
      : "NULL";

然后，在 SQL 查询中:不要再次引用。