如何将多个文件存入数据库同一行？PHP上传代码适配需求

我用以下PHP代码实现多文件上传并写入数据库：

if(isset($_POST['submit'])){ 
    // File upload configuration 
    mkdir("public/$order_id", 0770, true);
    $targetDir = "public/$order_id/"; 
    $allowTypes = array('jpg','png','jpeg','gif'); 
     
    $statusMsg = $errorMsg = $insertValuesSQL = $errorUpload = $errorUploadType = ''; 
    $fileNames = array_filter($_FILES['files']['name']); 
    if(!empty($fileNames)){ 
        foreach($_FILES['files']['name'] as $key=>$val){ 
            // File upload path 
            $fileName = basename($_FILES['files']['name'][$key]); 
            $targetFilePath = $targetDir . $fileName; 
             
            // Check whether file type is valid 
            $fileType = pathinfo($targetFilePath, PATHINFO_EXTENSION); 
            if(in_array($fileType, $allowTypes)){ 
                // Upload file to server 
                if(move_uploaded_file($_FILES["files"]["tmp_name"][$key], $targetFilePath)){ 
                    // Image db insert sql 
                    $insertValuesSQL .= "('".$order_id."','".$fileName."', NOW()),"; 
                }else{ 
                    $errorUpload .= $_FILES['files']['name'][$key].' | '; 
                } 
            }else{ 
                $errorUploadType .= $_FILES['files']['name'][$key].' | '; 
            } 
        } 
         
        // Error message 
        $errorUpload = !empty($errorUpload)?'Upload Error: '.trim($errorUpload, ' | '):''; 
        $errorUploadType = !empty($errorUploadType)?'File Type Error: '.trim($errorUploadType, ' | '):''; 
        $errorMsg = !empty($errorUpload)?'<br/>'.$errorUpload.'<br/>'.$errorUploadType:'<br/>'.$errorUploadType; 
         
        if(!empty($insertValuesSQL)){ 
            $insertValuesSQL = trim($insertValuesSQL, ','); 
            // Insert image file name into database 
            $insert = $db->query("INSERT INTO idcardprint (orderid,img1, date) VALUES $insertValuesSQL"); 
            if($insert){ 
                $statusMsg = "Files are uploaded successfully.".$errorMsg; 
            }else{ 
                $statusMsg = "Sorry, there was an error uploading your file."; 
            } 
        }else{ 
            $statusMsg = "Upload failed! ".$errorMsg; 
        } 
    }else{ 
        $statusMsg = 'Please select a file to upload.'; 
    } 
} 
?>

目前执行后，数据库中每个文件对应一行数据，查询展示的表格为：

我希望实现将两个文件存入数据库的同一行，使展示表格变为：

请问如何修改代码实现该需求？

1. 调整数据库表结构 #

首先给idcardprint表新增img2字段，用于存储第二个文件的文件名，执行SQL语句：

ALTER TABLE idcardprint ADD COLUMN img2 VARCHAR(255) NULL;

2. 修改PHP代码逻辑 #

不再循环生成多条插入语句，而是收集上传成功的文件名，将前两个文件名作为同一行的img1和img2插入数据库：

if(isset($_POST['submit'])){ 
    // File upload configuration 
    mkdir("public/$order_id", 0770, true);
    $targetDir = "public/$order_id/"; 
    $allowTypes = array('jpg','png','jpeg','gif'); 
     
    $statusMsg = $errorMsg = $errorUpload = $errorUploadType = ''; 
    $uploadedFiles = []; // 存储上传成功的文件名
    $fileNames = array_filter($_FILES['files']['name']); 
    
    if(!empty($fileNames)){ 
        foreach($_FILES['files']['name'] as $key=>$val){ 
            // File upload path 
            $fileName = basename($_FILES['files']['name'][$key]); 
            $targetFilePath = $targetDir . $fileName; 
             
            // Check whether file type is valid 
            $fileType = pathinfo($targetFilePath, PATHINFO_EXTENSION); 
            if(in_array($fileType, $allowTypes)){ 
                // Upload file to server 
                if(move_uploaded_file($_FILES["files"]["tmp_name"][$key], $targetFilePath)){ 
                    $uploadedFiles[] = $fileName; // 存入数组
                }else{ 
                    $errorUpload .= $_FILES['files']['name'][$key].' | '; 
                } 
            }else{ 
                $errorUploadType .= $_FILES['files']['name'][$key].' | '; 
            } 
        } 
         
        // Error message 
        $errorUpload = !empty($errorUpload)?'Upload Error: '.trim($errorUpload, ' | '):''; 
        $errorUploadType = !empty($errorUploadType)?'File Type Error: '.trim($errorUploadType, ' | '):''; 
        $errorMsg = !empty($errorUpload)?'<br/>'.$errorUpload.'<br/>'.$errorUploadType:'<br/>'.$errorUploadType; 
         
        if(!empty($uploadedFiles)){ 
            // 处理文件名，确保img1和img2存在（不足的话设为空）
            $img1 = !empty($uploadedFiles[0]) ? $uploadedFiles[0] : '';
            $img2 = !empty($uploadedFiles[1]) ? $uploadedFiles[1] : '';
            
            // 转义变量防止SQL注入
            $img1 = $db->real_escape_string($img1);
            $img2 = $db->real_escape_string($img2);
            $order_id_escaped = $db->real_escape_string($order_id);
            
            // 插入同一行数据
            $insert = $db->query("INSERT INTO idcardprint (orderid, img1, img2, date) VALUES ('$order_id_escaped', '$img1', '$img2', NOW())"); 
            
            if($insert){ 
                $statusMsg = "Files are uploaded successfully.".$errorMsg; 
            }else{ 
                $statusMsg = "Sorry, there was an error uploading your file."; 
            } 
        }else{ 
            $statusMsg = "Upload failed! ".$errorMsg; 
        } 
    }else{ 
        $statusMsg = 'Please select a file to upload.'; 
    } 
} 
?>

3. 补充说明 #

• 用$uploadedFiles数组收集成功上传的文件名，取前两个分别对应img1和img2字段插入同一行
• 加入real_escape_string处理变量，修复原代码的SQL注入漏洞
• 若上传文件超过2个，仅保留前两个存入数据库；若不足2个，空缺字段设为空值

内容的提问来源于stack exchange，提问作者Shah Tech