PHP文件上传与数据库记录：常见问题排查与最佳实践

$host = "localhost";
$dbUsername = "root";
$dbPassword = "";
$dbName = "abc_school";

//Create connection
$conn = mysqli_connect($host, $dbUsername, $dbPassword, $dbName);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$targetDir = "uploads/";
// ... 其他变量定义 ...
$fileName = $_FILES['lfile']['name'];
$targetFilePath = $targetDir . $fileName;
$fileType = pathinfo($targetFilePath,PATHINFO_EXTENSION);

if(isset($_POST["upload"]) && !empty($_FILES['lfile']['name'])){
    $allowTypes = array('jpg','png','jpeg','gif','pdf');
    if(in_array($fileType, $allowTypes)){
        if(move_uploaded_file($_FILES['lfile']['tmp_name'], $targetFilePath)){
            // ... 数据库插入逻辑 ...
        } else {
            $statusMsg = "Sorry, there was an error uploading your file.";
        }
    } else {
        $statusMsg = "Sorry, only JPG, JPEG, PNG, GIF, & PDF files are allowed to upload.";
    }
} else {
    $statusMsg = "Please select a file to upload.";
}

            $insert = "INSERT INTO lessons (lesson_no, name, description, date, file) VALUES ($lessonNo, '$lessonName', 
                '$description', '$date', '$fileName');";
            $result_insert = mysqli_query($conn,$insert);

            if($insert){ // 错误：这里应该检查 $result_insert
                $statusMsg = "The file ".basename($_FILES['lfile']['name']). " has been uploaded successfully.";
            }
            else{
                $statusMsg = "File upload failed, please try again.";
            } 

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

            // 修正后的数据库插入逻辑
            $insert = "INSERT INTO lessons (lesson_no, name, description, date, file) VALUES ($lessonNo, '$lessonName', 
                '$description', '$date', '$fileName');";
            $result_insert = mysqli_query($conn,$insert);

            if($result_insert){ // 正确：检查 $result_insert
                $statusMsg = "The file ".basename($_FILES['lfile']['name']). " has been uploaded successfully.";
            }
            else{
                // 输出数据库错误信息，帮助调试
                $statusMsg = "File upload failed, please try again. Error: " . mysqli_error($conn);
            } 

<?php

$host = "localhost";
$dbUsername = "root";
$dbPassword = "";
$dbName = "abc_school";

// 创建连接
$conn = mysqli_connect($host, $dbUsername, $dbPassword, $dbName);

// 检查连接
if (!$conn) {
    die("数据库连接失败: " . mysqli_connect_error());
}

$targetDir = "uploads/";

if(isset($_POST["upload"]) && !empty($_FILES['lfile']['name'])){
    $lessonNo = $_POST['lno'];
    $lessonName = $_POST['lname'];
    $description = $_POST['ldescription'];
    $date = $_POST['ldate'];
    $fileName = $_FILES['lfile']['name'];
    $targetFilePath = $targetDir . $fileName;
    $fileType = pathinfo($targetFilePath, PATHINFO_EXTENSION);

    $allowTypes = array('jpg','png','jpeg','gif','pdf');
    if(in_array(strtolower($fileType), $allowTypes)){ // 统一转换为小写进行比较
        // 确保上传目录存在
        if (!is_dir($targetDir)) {
            mkdir($targetDir, 0777, true);
        }

        // 生成唯一文件名，防止覆盖
        $newFileName = uniqid() . '.' . $fileType;
        $newTargetFilePath = $targetDir . $newFileName;

        if(move_uploaded_file($_FILES['lfile']['tmp_name'], $newTargetFilePath)){
            // 使用预处理语句插入数据
            $sql = "INSERT INTO lessons (lesson_no, name, description, date, file) VALUES (?, ?, ?, ?, ?)";

            // 准备语句
            if ($stmt = mysqli_prepare($conn, $sql)) {
                // 绑定参数
                // 'issss' 表示参数类型：i=integer, s=string
                // 确保 $lessonNo 的数据类型与数据库字段匹配，这里假设是整数
                mysqli_stmt_bind_param($stmt, "issss", $lessonNo, $lessonName, $description, $date, $newFileName);

                // 执行语句
                if (mysqli_stmt_execute($stmt)) {
                    $statusMsg = "文件 " . htmlspecialchars(basename($_FILES['lfile']['name'])) . " 已成功上传并记录。";
                } else {
                    $statusMsg = "文件上传成功，但数据库记录失败。错误: " . mysqli_stmt_error($stmt);
                    // 如果数据库记录失败，考虑删除已上传的文件
                    unlink($newTargetFilePath); 
                }
                // 关闭语句
                mysqli_stmt_close($stmt);
            } else {
                $statusMsg = "数据库语句准备失败: " . mysqli_error($conn);
                unlink($newTargetFilePath); // 如果语句准备失败，删除已上传的文件
            }
        } else {
            $statusMsg = "抱歉，上传文件时发生错误。";
        }
    } else {
        $statusMsg = "抱歉，只允许上传 JPG, JPEG, PNG, GIF, & PDF 文件。";
    }
} else {
    $statusMsg = "请选择一个文件上传。";
}

echo $statusMsg;

// 关闭数据库连接
mysqli_close($conn);

?>