2012-04-30 07:20



If I have a list of database fields:

id    order    title
1     3        This is a post
2     1        This is another post
3     2        This is also a post
4     4        This is still a post

And I want change the order of these based on order.
If I set order on id #1 to 2 I want order on id #3 to be set to 3.
If I set order on id #4 to 1 I want order on id #1 to be 4, order on id #2 to be 2, order on id #3 to be 3 etc. etc.

How can I do this? I think it should be quite simple but I can't figure it out.
I have searched but I don't know what to search for...

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答


  • duan7007 duan7007 9年前

    You can do it in SQL with two UPDATE statements:

    SELECT order FROM t WHERE order BETWEEN $new_order AND $old_order FOR UPDATE;
    UPDATE t SET order = order + 1  WHERE order BETWEEN $new_order AND $old_order;
    UPDATE t SET order = $new_order WHERE id = $id;

    I have locked the table during these updates to prevent concurrency issues (however, note that transactions/locks are only applicable to InnoDB tables).

    点赞 评论 复制链接分享
  • duannuo7878 duannuo7878 9年前

    First you have to find the $other_row, the one that has the order value you want to reuse. Then change each row’s value.

    $id_to_set = 1;
    $order_to_set = 2;
    // Find the order that will need to be swapped
    $result = mysql_query("select order from xxx where id = $id_to_set");
    $row = mysql_fetch_row($result);
    // Find the other row that will be modified
    $result = mysql_query("select id from xxx where order = $order_to_set");
    $other_row = mysql_fetch_row($result);
    // Do the actual swapping
    mysql_query("update xxxx set order = $order_to_set where id = $id_to_set");
    mysql_query("update xxxx set order = " . $row['order'] . " where id = " . $other_row['id']);
    点赞 评论 复制链接分享
  • dtjbcda841554 dtjbcda841554 9年前

    there are two ways : read SQL doc and use the ORDER BY close in your query

    read PHP doc and use usort function

    点赞 评论 复制链接分享