拖拽交换<li>后更新数据库分组信息的技术实现求助

Got it, let's work through how to get those swapped list items' new group positions saved to your database. I'll break this down into frontend and backend changes that fit right into your existing code.

Step 1: Update the Frontend Drag-Drop Logic #

Your current code swaps the list elements visually, but it doesn't track which user belongs to which new group, or send that data to the backend. Let's fix that:

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js" type="text/javascript"></script>
<script src="http://code.jquery.com/ui/1.8.20/jquery-ui.min.js" type="text/javascript"></script>
<script src="http://jquery-ui.googlecode.com/svn/tags/latest/external/jquery.bgiframe-2.1.2.js" type="text/javascript"></script>
<script src="http://jquery-ui.googlecode.com/svn/tags/latest/ui/minified/i18n/jquery-ui-i18n.min.js" type="text/javascript"></script>
<script type="text/javascript">
$(function() {
    $(".grupper li").draggable({
        appendTo: "body",
        helper: "clone",
        cursor: "move",
        revert: "invalid"
    });

    initDroppable($(".grupper li"));

    function initDroppable($elements) {
        $elements.droppable({
            activeClass: "ui-state-default",
            hoverClass: "ui-drop-hover",
            accept: ":not(.ui-sortable-helper)",
            over: function(event, ui) {
                var $this = $(this);
            },
            drop: function(event, ui) {
                var $this = $(this);
                // Keep your swap logic, but retain the user ID from the name attribute
                var li1 = $('<li>' + ui.draggable.text() + '</li>').attr('name', ui.draggable.attr('name'));
                $(this).after(li1);
                var li2 = $('<li>' + $(this).text() + '</li>').attr('name', $(this).attr('name'));
                ui.draggable.after(li2);
                ui.draggable.remove();
                $(this).remove();

                // Re-initialize draggable/droppable for new elements
                initDroppable($(".grupper li"));
                $(".grupper li").draggable({
                    appendTo: "body",
                    helper: "clone",
                    cursor: "move",
                    revert: "invalid"
                });

                // Collect updated group data
                var groupUpdates = {};
                $('.grupper').each(function() {
                    // Extract group number from the ul's ID (e.g., "gruppe1" → 1)
                    var groupId = $(this).attr('id').replace('gruppe', '');
                    var userIds = [];
                    // Gather all user IDs in this group
                    $(this).find('li').each(function() {
                        userIds.push($(this).attr('name'));
                    });
                    groupUpdates[groupId] = userIds;
                });

                // Send data to backend via AJAX
                $.ajax({
                    url: 'update_groups.php', // We'll create this file next
                    method: 'POST',
                    data: { groups: groupUpdates },
                    success: function(response) {
                        console.log('Group sync successful:', response);
                        // Optional: Add a user-friendly success message here
                    },
                    error: function(xhr, status, err) {
                        console.error('Group sync failed:', err);
                        // Optional: Show an error message to the user
                    }
                });
            }
        });
    }
});
</script>

Key Frontend Changes: #

• When creating new <li> elements, we now copy the name attribute (which stores the user's ID) from the original items – this was missing before, so we lost track of which user was which after swapping.
• After the swap, we loop through every group to collect all user IDs in that group, organizing them into an object where keys are group IDs and values are arrays of user IDs.
• We send this data to a new PHP file (update_groups.php) using AJAX to handle the database update.

Step 2: Create the Backend Update Script #

Make a new file called update_groups.php (place it in the same directory as your frontend page, or adjust the AJAX url if needed) to save the new group assignments:

<?php
require_once '../connection.php';
require_once '../globalfunctions.php';
globalfunctions();

// Check if we received valid group data
if(isset($_POST['groups']) && is_array($_POST['groups'])) {
    $groups = $_POST['groups'];
    $success = true;

    // Start a database transaction to ensure data consistency
    mysqli_begin_transaction($connection);

    try {
        foreach($groups as $groupId => $userIds) {
            foreach($userIds as $userId) {
                // Sanitize inputs to prevent SQL injection
                $safeUserId = mysqli_real_escape_string($connection, $userId);
                $safeGroupId = mysqli_real_escape_string($connection, $groupId);

                // Update the user's group in the database
                $query = "UPDATE studerende SET gruppeid_redigeret = '$safeGroupId' WHERE id = '$safeUserId'";
                $result = mysqli_query($connection, $query);

                if(!$result) {
                    throw new Exception(mysqli_error($connection));
                }
            }
        }

        // Commit changes if all updates succeeded
        mysqli_commit($connection);
        echo json_encode(['status' => 'success', 'message' => 'All group assignments updated!']);
    } catch(Exception $e) {
        // Roll back all changes if any update fails
        mysqli_rollback($connection);
        echo json_encode(['status' => 'error', 'message' => $e->getMessage()]);
        $success = false;
    }
} else {
    echo json_encode(['status' => 'error', 'message' => 'No valid group data received.']);
}

mysqli_close($connection);
?>

Key Backend Details: #

• We use a transaction to ensure that if any update fails, all changes are rolled back – this prevents partial updates that would leave your database in an inconsistent state.
• Inputs are sanitized with mysqli_real_escape_string to block SQL injection (for even better security, consider switching to prepared statements).
• We return a JSON response so the frontend can easily handle success or error cases.

Step 3: Test It Out #

1. Drag and drop a user between groups.
2. Open your browser's developer tools (F12) and check the Console tab to see the AJAX response.
3. Verify your studerende table in the database – you should see the gruppeid_redigeret column updated for the swapped users.

Quick Notes: #

• Double-check the path to update_groups.php in the AJAX url if you run into errors.
• You can add user-friendly feedback (like a toast notification) in the AJAX success/error callbacks to let users know their changes saved.
• If you're using a newer version of PHP, PDO is a more modern alternative to mysqli for database interactions.

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