使用php重新排序父/子行mysql

I need to re-order via form submission rows in a table with the following structure for a nested page layout

我需要通过表格中的表单提交行重新排序,具有以下嵌套页面布局结构

Columns: (table name is: sourcedocs3)

列:(表名是:sourcedocs3)

sort1 | sort2 | type

1 | 1 | parent

1 | 2 | child

2 | 1 | no nesting

3 | 1 | parent

3 | 2 | child

3 | 3 | child

4 | 1 | no nesting

I need to be able to re-order (via PHP/Mysql) 3 to 1 without losing basic structure -- 3 becomes 1 and all rows with value of 1 are incremented). Sounded simple but I'm having a hard time keeping the structure intact after shuffling (3|1 should be 1|1, 3|2 should be 1|2 etc)

我需要能够重新排序(通过PHP / Mysql)3到1而不会丢失基本结构 - 3变为1并且所有值为1的行都会递增)。听起来很简单,但我很难在洗牌后保持结构完好(3 | 1应该是1 | 1,3 | 2应该是1 | 2等)

2 个解决方案

#1

START TRANSACTION;

UPDATE `why_do_people_never_give_the_table_name` SET sort1 = 999 
WHERE sort1 = 3;

UPDATE `why_do_people_never_give_the_table_name` SET sort1 = sort1 + 1 
WHERE sort1 BETWEEN 1 AND 3
ORDER BY sort1 DESC;

UPDATE `why_do_people_never_give_the_table_name` SET sort1 = 1 
WHERE sort1 = 999;

COMMIT;

Note that if you were moving a menu to a later position, e.g. moving 2 to 4, you'd want to order the second update in ASCending order.

请注意,如果您要将菜单移动到以后的位置,例如移动2到4,您需要按ASCending顺序订购第二个更新。

#2

The answer from Bill was spot on - but didn't include the PHP code necessary to perform the operation so here is what I ended up with:

比尔的答案很明显 - 但是没有包含执行操作所需的PHP代码,所以这就是我最终的结果:

    <?php
//if new sort number is less than old sortnum
    if ($newsortnum < $oldsortnum){
         //RUN SEPARATE QUERIES
         $sql_rename="UPDATE  `sourcedocs` SET sort1 = '999' WHERE sort1 =".$oldsortnum." AND category =  '".$category."'";
         $sql_reorder="UPDATE  `sourcedocs` SET sort1 = sort1 +1 WHERE sort1 >= ".$newsortnum." AND sort1 <= ".$oldsortnum." AND category =  '".$category."'";
         $sql_insert="UPDATE  `sourcedocs` SET sort1 =".$newsortnum." WHERE sort1 = '999' AND category =  '".$category."'";
    }
    //if new sort number is greater than old sortnum decrement all sortnums greater than new sortnum and decrement sortnums between 
    if ($newsortnum > $oldsortnum){
    //RUN SEPARATE QUERIES
         $sql_rename="UPDATE  `sourcedocs` SET sort1 = '999' WHERE sort1 =".$oldsortnum." AND category =  '".$category."'";
         $sql_reorder="UPDATE  `sourcedocs` SET sort1 = sort1 -1 WHERE sort1 <= ".$newsortnum." AND sort1 >= ".$oldsortnum." AND category =  '".$category."'";
         $sql_insert="UPDATE  `sourcedocs` SET sort1 =".$newsortnum." WHERE sort1 = '999' AND category =  '".$category."'";
    }
    ?>

Long story short - I stopped short of creating a PDO instance for the beginTransaction(); ...a little more messy, but it's working. I may switch to the PDO method but need to move on. Here's a link to the PDO info if you're having similar difficulties:

长话短说 - 我没有为beginTransaction()创建一个PDO实例; ......有点混乱,但它正在发挥作用。我可能会切换到PDO方法,但需要继续前进。如果您遇到类似的困难,这里是PDO信息的链接:

PHP.net - PDO Construct
PHP.net - PDO beginTransaction();

PHP.net - PDO构造PHP.net - PDO beginTransaction();

#1

START TRANSACTION;

UPDATE `why_do_people_never_give_the_table_name` SET sort1 = 999 
WHERE sort1 = 3;

UPDATE `why_do_people_never_give_the_table_name` SET sort1 = sort1 + 1 
WHERE sort1 BETWEEN 1 AND 3
ORDER BY sort1 DESC;

UPDATE `why_do_people_never_give_the_table_name` SET sort1 = 1 
WHERE sort1 = 999;

COMMIT;