mysql拖动排序设计

CREATE TABLE items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    order INT NOT NULL DEFAULT 0
);

<?php
// 假设$newOrder是从前端接收到的新顺序数组
$newOrder = [
    ['id' => 1, 'newOrder' => 2],
    ['id' => 2, 'newOrder' => 1],
    // ...
];

// 开启事务
$conn->beginTransaction();

try {
    // 更新每个项目的顺序
    foreach ($newOrder as $item) {
        $stmt = $conn->prepare("UPDATE items SET `order` = ? WHERE id = ?");
        $stmt->execute([$item['newOrder'], $item['id']]);
    }

    // 提交事务
    $conn->commit();
} catch (Exception $e) {
    // 回滚事务
    $conn->rollBack();
    throw $e;
}
?>

<ul id="sortable-list">
    <!-- 列表项 -->
</ul>

<script src="https://cdn.jsdelivr.net/npm/sortablejs@latest/Sortable.min.js"></script>
<script>
    new Sortable(document.getElementById('sortable-list'), {
        onUpdate: function (evt) {
            const newOrder = [];
            document.querySelectorAll('#sortable-list li').forEach((li, index) => {
                newOrder.push({ id: li.dataset.id, newOrder: index });
            });

            // 发送新的顺序到后端
            fetch('/update-order', {
                method: 'POST',
                headers: {
                    'Content-Type': 'application/json'
                },
                body: JSON.stringify(newOrder)
            });
        }
    });
</script>