如何用PHP+jQuery实现排班系统多维度选择并提交至MySQL

Hey there, it looks like your current code is creating every possible combination of selected shifts, sites, and dates (a Cartesian product) when inserting into MySQL—this is why you're getting messy, unintended records. What you actually need is to link each selected date to a single specific shift and site, right? Let's fix this step by step:

1. Frontend Adjustment: Link Dates to Their Own Shift/Site Selections #

The current setup lets users pick multiple shifts and sites globally, which can't be tied to individual dates. We'll modify the frontend to dynamically generate a shift and site selector for each date the user picks, so each date has its own paired values.

Here's the updated frontend code:

<html>
<head>
<title>Roster </title>
<meta charset="utf-8">
<script type="text/javascript" src="https://code.jquery.com/jquery-1.11.3.min.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<link rel="stylesheet" href="https://formden.com/static/cdn/bootstrap-iso.css" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-multiselect/0.9.13/js/bootstrap-multiselect.js"></script>
<link rel="stylesheet" href="bootstrap/css/multiselect.css" />
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.4.1/js/bootstrap-datepicker.min.js"></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.4.1/css/bootstrap-datepicker3.css"/>
<script type="text/javascript">
$(document).ready(function(){
    var date_input=$('input[name="start_time"]');
    var container=$('.bootstrap-iso form').length>0 ? $('.bootstrap-iso form').parent() : "body";
    var options={
        multidate:true,
        format: 'yyyy-mm-dd',
        container: container,
        todayHighlight: true,
        autoclose: true,
    };
    date_input.datepicker(options).on('changeDate', function(e) {
        // Generate shift/site selectors for each selected date
        var selectedDates = $(this).datepicker('getDates');
        var dateFieldsHtml = '';
        
        $.each(selectedDates, function(index, date) {
            var formattedDate = $.datepicker.formatDate('yyyy-mm-dd', date);
            dateFieldsHtml += `
                <div class="date-assignment" style="margin:15px 0; padding:10px; border:1px solid #ddd;">
                    <h5>日期: ${formattedDate}</h5>
                    <input type="hidden" name="dates[]" value="${formattedDate}">
                    
                    <div style="margin:10px 0;">
                        <label>班次:</label>
                        <select name="shifts[]" required>
                            <option value="">请选择班次</option>
                            <option value="Day">Day</option>
                            <option value="Night">Night</option>
                        </select>
                    </div>
                    
                    <div style="margin:10px 0;">
                        <label>站点:</label>
                        <select name="sites[]" required>
                            <option value="">请选择站点</option>
                            <option value="bp">BP</option>
                            <option value="shell">Shell</option>
                            <option value="caltex">Caltex</option>
                            <option value="KFC">KFC</option>
                            <option value="McDonald's">McDonald's</option>
                            <option value="New York City park">New York City park</option>
                            <option value="Airport">Airport</option>
                        </select>
                    </div>
                </div>
            `;
        });
        
        $('#date-assignments').html(dateFieldsHtml);
    });
});
</script>
</head>
<body>
<form method="post" action="md.php">
    <div id="peoplenames" style="padding:10px">
        <select name="personname" id="personname" required>
            <option value="">选择员工</option>
            <option value="Mike">Mike</option>
            <option value="Gupta">Gupta</option>
            <option value="Messi">Messi</option>
            <option value="Ronaldo">Ronaldo</option>
            <option value="James">James</option>
        </select>
    </div>

    <div class="bootstrap-iso">
        <div class="container-fluid">
            <div class="row">
                <div class="col-md-6 col-sm-6 col-xs-12">
                    <div class="form-group">
                        <label class="control-label" for="date">选择排班日期</label>
                        <input class="form-control" id="start_time" name="start_time" placeholder="YYYY-MM-DD" type="text" required />
                    </div>
                </div>
            </div>
        </div>
    </div>

    <!-- Dynamic date-shift-site assignment area -->
    <div id="date-assignments" style="padding:20px;"></div>

    <div class="form-group" style="padding:20px">
        <button class="btn btn-primary" name="btnroster" type="submit">提交排班</button>
    </div>
</form>
</body>
</html>

2. Backend PHP Fix: Insert Paired Date/Shift/Site Data #

Now the frontend sends three aligned arrays: dates[], shifts[], and sites[]—each index corresponds to one complete roster entry (e.g., dates[0] pairs with shifts[0] and sites[0]). We'll loop through these arrays as matched sets instead of creating all combinations.

Update your PHP code (either in the same file or md.php—make sure your database connection $con is initialized first):

<?php
// Initialize your database connection first (example below)
// $con = new PDO("mysql:host=localhost;dbname=your_database", "your_username", "your_password");

if(isset($_POST['btnroster'])){
    $empid = trim($_POST['personname']);
    $dates = $_POST['dates'];
    $shifts = $_POST['shifts'];
    $sites = $_POST['sites'];

    // Ensure all arrays have the same length to avoid mismatches
    if(count($dates) === count($shifts) && count($dates) === count($sites)){
        // Prepare the insert query once for efficiency
        $query = 'INSERT INTO tbljobassignment(empid, siteid, shifttype, start_time) VALUES (:empid, :siteid, :shifttype, :start_time)';
        $insert = $con->prepare($query);
        
        // Loop through each paired entry
        for($i=0; $i<count($dates); $i++){
            $date = trim($dates[$i]);
            $shift = trim($shifts[$i]);
            $site = trim($sites[$i]);
            
            // Skip empty entries (double-check even with frontend required validation)
            if(!empty($date) && !empty($shift) && !empty($site)){
                $insert->execute(array(
                    ':empid' => $empid,
                    ':siteid' => $site,
                    ':shifttype' => $shift,
                    ':start_time' => $date
                ));
            }
        }
        
        echo "排班提交成功！";
    } else {
        echo "数据不匹配，请确保每个日期都选择了对应的班次和站点！";
    }
}
?>

Key Improvements Explained #

• Frontend Data Linking: Each date now has its own shift and site selector, so users explicitly assign values per date—no more ambiguous global selections.
• Efficient Backend Insert: We use a single prepared statement for all inserts (better performance than preparing inside a loop) and validate array lengths to prevent index errors.
• Data Integrity: Frontend required attributes and backend empty-value checks ensure no incomplete records are inserted.

With these changes, your MySQL entries will match exactly the example you provided—each employee-date pair has one shift and one site, no messy duplicate combinations.

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