如何用PHP和jQuery实现多日期对应多站点班次提交至MySQL

Hey there! The core problem with your current code is that it’s generating a full Cartesian product of all selected dates, shifts, and sites. That means every date gets paired with every shift and every site in your database, which is definitely not what you want. Let’s restructure things so each selected date has its own dedicated shift and site selection, then submit those paired values correctly.

Step 1: Rewrite the HTML to Bind Dates to Their Own Shifts & Sites #

We’ll use jQuery to dynamically generate a shift and site selector for every date the user picks. This way, they can explicitly set the shift and site for each individual date:

<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(){
            const dateInput = $('input[name="start_time"]');
            const container = $('.bootstrap-iso form').length > 0 ? $('.bootstrap-iso form').parent() : "body";
            const dateOptions = {
                multidate: true,
                format: 'yyyy-mm-dd',
                container: container,
                todayHighlight: true,
                autoclose: false,
            };

            // Initialize datepicker
            dateInput.datepicker(dateOptions);

            // Generate shift/site selectors when dates are added/removed
            dateInput.on('changeDate', function(e) {
                const selectedDates = $(this).datepicker('getDates');
                const dateContainer = $('#date-shift-site-container');
                dateContainer.empty();

                // Loop through each selected date to create a matching shift/site group
                selectedDates.forEach((date, index) => {
                    const formattedDate = $.fn.datepicker.DPGlobal.formatDate(date, 'yyyy-mm-dd', dateOptions);
                    dateContainer.append(`
                        <div class="date-roster-group" style="margin: 15px 0; padding: 12px; border: 1px solid #eaeaea; border-radius: 4px;">
                            <input type="hidden" name="roster[${index}][date]" value="${formattedDate}">
                            <h6>Date: ${formattedDate}</h6>
                            <div style="margin: 8px 0;">
                                <label>Shift:</label>
                                <select name="roster[${index}][shifttype]" required>
                                    <option value="">Select Day/Night</option>
                                    <option value="Day">Day</option>
                                    <option value="Night">Night</option>
                                </select>
                            </div>
                            <div style="margin: 8px 0;">
                                <label>Site:</label>
                                <select name="roster[${index}][sitenames]" required>
                                    <option value="bp">BP</option>
                                    <option value="shell">Shell</option>
                                    <option value="caltex">Caltex</option>
                                </select>
                            </div>
                        </div>
                    `);
                });
            });
        });
    </script>
</head>
<body>
    <form method="post" action="md.php">
        <div id="peoplenames" style="padding: 10px;">
            <select name="personname" id="personname" required>
                <option value="Mike">Mike</option>
                <option value="Gupta">Gupta</option>
                <option value="Messi">Messi</option>
                <option value="Ronaldo">Ronaldo</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">Select Roster Dates</label>
                            <input class="form-control" id="start_time" name="start_time" placeholder="MM/DD/YYYY" type="text" required />
                        </div>
                    </div>
                </div>
            </div>
        </div>
        <!-- Container for dynamic date-shift-site groups -->
        <div id="date-shift-site-container" style="padding: 20px;"></div>
        <div class="form-group" style="padding: 20px;">
            <button class="btn btn-primary" name="btnroster" type="submit">Submit Roster</button>
        </div>
    </form>
</body>
</html>

Step 2: Update PHP to Insert Paired Data Correctly #

Now the form submits data as grouped entries (each with a date, shift, and site). We just need to loop through these groups and insert each one into the database—no more messy cross-combinations:

<?php
// Assume your database connection $con is already initialized here

$employeeName = isset($_POST['personname']) ? trim($_POST['personname']) : '';

if (isset($_POST['btnroster']) && !empty($_POST['roster'])) {
    foreach ($_POST['roster'] as $rosterEntry) {
        // Skip entries where shift or site wasn't selected
        if (empty($rosterEntry['shifttype']) || empty($rosterEntry['sitenames'])) {
            continue;
        }

        $date = $rosterEntry['date'];
        $shift = $rosterEntry['shifttype'];
        $site = $rosterEntry['sitenames'];

        // Prepare and execute the insert query
        $insertQuery = 'INSERT INTO tbljobassignment(empid, siteid, shifttype, start_time) 
                        VALUES (:empid, :siteid, :shifttype, :start_time)';
        $statement = $con->prepare($insertQuery);
        $statement->execute([
            ':empid' => $employeeName,
            ':siteid' => $site,
            ':shifttype' => $shift,
            ':start_time' => $date
        ]);
    }

    echo "Roster submitted successfully!";
}
?>

What Was Wrong With the Original Code? #

Your original PHP used three nested foreach loops (shifts → sites → dates), which creates every possible combination of the selected values. For example, if you picked 2 dates, 2 shifts, and 3 sites, you’d end up with 12 database entries instead of the 2 you actually need. By grouping each date with its own shift and site before submission, we eliminate this problem entirely.

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