自学PHP遇阻：表单数组及动态部门值插入MySQL求助

Hey there! Let's work through this together—you’ve already got the foundational pieces in place, so we just need to fill in the logic to safely insert that dynamic department value and attendance array into MySQL.

First, let’s assume you’re using PDO for database interactions (it’s more secure against SQL injection and widely recommended over mysqli procedural style). Here’s how to complete your code:

Step 1: Complete the insertAttendance Function #

We’ll add database connection handling, prepared statements (to avoid injection), and loop through your attendance array to insert each record. We’ll also add transaction support to ensure all records are inserted successfully or none are (prevents partial data).

class AttendanceHandler {
    private $pdo;

    // Inject the PDO connection into the class (best practice for reusability)
    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }

    public function insertAttendance($dep, $attendance = array(), $date) {
        // Exit early if there's no attendance data to insert
        if (empty($attendance)) {
            return false;
        }

        try {
            // Start a transaction to ensure atomicity
            $this->pdo->beginTransaction();

            // Prepare a reusable SQL statement (avoids repeating query parsing)
            $sql = "INSERT INTO attendance (department, employee_id, status, attendance_date) 
                    VALUES (:department, :employee_id, :status, :attendance_date)";
            $stmt = $this->pdo->prepare($sql);

            // Bind fixed parameters (same for all inserts)
            $stmt->bindParam(':department', $dep);
            $stmt->bindParam(':attendance_date', $date);

            // Loop through each entry in the attendance array
            foreach ($attendance as $employeeId => $attendanceStatus) {
                // Bind dynamic parameters (changes per entry)
                $stmt->bindParam(':employee_id', $employeeId);
                $stmt->bindParam(':status', $attendanceStatus);
                
                // Execute the insert for this employee
                $stmt->execute();
            }

            // Commit all changes if no errors occurred
            $this->pdo->commit();
            return true;

        } catch (PDOException $e) {
            // Roll back changes if something went wrong
            $this->pdo->rollBack();
            // Log the error for debugging (don't show to end users!)
            error_log("Attendance insertion failed: " . $e->getMessage());
            return false;
        }
    }
}

Step 2: Tie It to Your Form Submission Code #

Now update your form handling block to initialize the database connection, collect form data, and call the insertion function:

if (isset($_POST['submit'])) {
    // Sanitize and collect form data (adjust field names to match your form!)
    $department = trim($_POST['department']);
    $attendanceDate = trim($_POST['attendance_date']);
    $attendanceRecords = $_POST['attendance']; // Should be an array like [employee_id => status]

    // Validate basic data (add more checks based on your needs)
    if (empty($department) || empty($attendanceDate) || !is_array($attendanceRecords)) {
        echo "Please fill in all required fields correctly.";
        exit;
    }

    // Initialize PDO database connection (replace with your DB credentials)
    try {
        $pdo = new PDO(
            'mysql:host=localhost;dbname=your_database_name;charset=utf8mb4',
            'your_username',
            'your_password'
        );
    } catch (PDOException $e) {
        die("Database connection failed: " . $e->getMessage());
    }

    // Create handler instance and run insertion
    $attendanceHandler = new AttendanceHandler($pdo);
    if ($attendanceHandler->insertAttendance($department, $attendanceRecords, $attendanceDate)) {
        echo "Attendance records saved successfully!";
    } else {
        echo "Oops, there was an error saving attendance. Please try again.";
    }
}

Key Notes to Remember #

• SQL Injection Protection: Using prepared statements with PDO ensures user input is never directly inserted into SQL queries—critical for security.
• Form Validation: Always validate and sanitize user input (e.g., check that $attendanceDate is a valid date, $department exists in your departments table, etc.).
• Error Handling: The try/catch block and transaction support prevent partial data from being inserted if one record fails.
• Form Structure: Make sure your HTML form uses array syntax for attendance fields, like <input type="text" name="attendance[123]" value="present"> where 123 is the employee ID.

内容的提问来源于stack exchange，提问作者Shahadat Hossen