MYSQL/PHP在保持数字顺序的同时上下移动对象的顺序

我有一个存放对象的表,它看起来像这样:

CREATE TABLE `post_objects` (
  `id` mediumint(7) NOT NULL AUTO_INCREMENT,
  `post_id` mediumint(7) NOT NULL,
  `object_data` text,
  `object_order` tinyint(1) DEFAULT '0',
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

这个 object_order post_id .

问题:

在我的PHP中,我希望能够将对象上下移动一次。例如,将顺序为3的对象移动到2位置,并使其余对象重新排序,以便仍保持1、2、3、4。。。

我尝试了什么:

当前函数没有给我预期的结果。目前,如果我将最后一个位置向下移动,则无法正确重新编号。以下是最新的尝试:

// Shift given object order by one in given direction.
function objectOrderShift ($object_id, $shift_type) {
    $post_id = getParentPostId($object_id);
    $current_object_order_value = getObjectOrderValue($object_id);
    switch ($shift_type) {
        case 'up':
            $new_object_order_value = $current_object_order_value - 1;
            break;
        case 'down':
            $new_object_order_value = $current_object_order_value + 1;
            break;
    }

    // Update new object id to new order value (*THERE WILL BE TWO ENTRIES WITH THE SAME ORDER NUMBER*)
    $sql = "UPDATE post_objects SET object_order = :new_object_order_value WHERE id = :object_id";
    $stmt = db::getInstance()->prepare($sql);
     $stmt->bindParam(':object_id', $object_id, PDO::PARAM_INT);
     $stmt->bindParam(':new_object_order_value', $new_object_order_value, PDO::PARAM_INT);
    $stmt->execute();

    //set starting order value
    $object_order_value = 1;
    // Run select for all objects edit as needed
    $sql = "SELECT id, object_order FROM post_objects WHERE post_id = :post_id ORDER BY object_order ASC";
    $stmt = db::getInstance()->prepare($sql);
     $stmt->bindParam(':post_id', $post_id, PDO::PARAM_INT);
    $stmt->execute();
    $results = $stmt->fetchAll();

    foreach ($results as $row) {
        $flux_object_id = $row['id'];  // holder for other object_id's from SELECT query

        if($flux_object_id == $object_id) {
            // This is the already altered object_id, skip entry and ++ value
            $object_order_value++;
        }
        $sql = "UPDATE post_objects SET object_order = :object_order_value, created = NOW() WHERE id = :flux_object_id";
        $stmt = db::getInstance()->prepare($sql);
         $stmt->bindParam(':flux_object_id', $flux_object_id, PDO::PARAM_INT);
         $stmt->bindParam(':object_order_value', $object_order_value, PDO::PARAM_INT);
        $stmt->execute();
        $object_order_value++;
    }
}